Field Type Error

[KAllen31]

Hi, I am using Access 97

I have a CSV file (that is coming from Excel) that I import into Access, I get this identical file every month (with the new month end information). There is a field called "Officer ID" which is a number, but of no numeric value, so it has been created as text.

Well, this month I tried to import it into Access and it will not allow me to import this one field into Access (I get a seperate table the one that will show you all of the errors associated with your import), telling me that I have the wrong data type. I look at what data type Access thinks it is (during the import wizard), and I see that it is recognizing it as an integer.

I go back into Excel (to the CSV file) and make sure that it is set as text before I re-do the import, and it is. Access just can't recognize it.

Does anyone have a solution??

I'm baffled

 

[Howlsta]

When you import the data, why don't you just change the data type in the wizard. Move to the field that is causing the problem and change the combo box accordingly (in the field options page).
HTH

Rich

 

[cos001]

Hi Kimberly,

I have had this trouble in the past, and I think the answer is to set this field type within Access to Text as well, since access recognises Integer's as numerical digits.

I apologise if you already know all this, but if you can get into the design of the data table in Access and set the 'offending' field's "data type" to text, then try re importing it should work??!??!

I hope I have helped (a bit at least!)

Craig

 

[KAllen31]

Thanks to both of you for the suggestion, the problem is that the field (officer ID) is no longer in my original table (the one I am using) because of the import error. The data error table (because of the data type error is all by itself, and that is where Access puts my officer ID field.

It is crazy!!

 

[simongallop]

As you are bringing the data in each month, then so long as the csv file name is always the name, why not bring it in as a linktable?

 

[KAllen31]

Because sometimes I need to do work within the Excel data, and I don't want the result to show up in Access. My company looooves Excel, and they like all kinds of options for spreadsheets. Therefore I keep the same info. in two different systems (Access & Excel).

Is this the right way to go?

Thanks

 

[simongallop]

In which case use the link table then have either a make table query or an append query, depending on whether you want to keep the data, to move the data to a perm table

 

[KAllen31]

Good Idea,

I will try that.

So what you are saying is if I link the table, I will no longer get the import error because I am no longer importing.

That makes sense, doesn't it

 

[The_Doc_Man]

You could always just import it to a temporary table, then have a query standing by to do the REAL import. One which would ignore the columns you weren't interested in keeping anyway.

After that, a quick delete of the temp table and you are done. Though if the format of the sheet doesn't change from month to month, it might be more convenient to just delete the contents of the table and leave it around for the next import. The wizard will pay attention to the format of the destination table if you choose the "append to existing table" option.

I believe once you get it right and define the import specs, you can save them, then write a macro to do the whole thing from a single action.

The only other part of this that would require attention is that you would need a regular regimen of table compression. But other than that, you should have no problem.