Why! Text field imports to Access at a double

[KAllen31]

I have no idea why this is happening. But I wish it would stop.

I have a csv file which I import into Access monthly. I have a field that is a SIC Code field. Essentially SIC codes are numbers but they mean nothing in terms of numbers so I have them in my CSV files as text.

When I import this to Access, Access automatically imports it as a double. This is really messing me up in my Crystal reports as my look up table to map the SIC codes are text.

My fix has been to switch the double back to text to get this to work, but I am training newbies on some of the Access fields and don't want to overwhelm them with too much right now. I want it as automated for them as possible.

Does anyone know why Access wants to do this. This has happened to me a few times in other fields and it drives me crazy.

Thanks

 

[RichMorrison]

I have had similar experience with imports from Excel worksheets. In some cases, Access does not recognize the format of external data. Instead, Access examines the values of a field and decides that it is numeric.

This sounds like the situation you are experiencing. As Pat suggested, importing into a table with fields defined as text will fix the problem.

RichM

 

[The_Doc_Man]

I wonder if this is a case where a pre-defined Import Spec would work. Manually define the import rules once, then store them. Next time, name them when you do the import. But I don't recall if the data types get stored. Pat?

 

[The_Doc_Man]

Probably because Excel's data types can't be trusted. As far as Excel is concerned, you can lie like a rug and still be OK. Oh, well, it was a nice thought.

I think I would have to go back to importing via Append rather than using a MakeTable, then.

 

[pcs]

KAllen31,

not sure what version of Access you are using.

in A97 and earlier, an import specification for a .csv will work.

later versions may be different. (import schema, whatever that is...)

an Excel file is a different problem. but, as you are importing a .csv, you should be able to force the SIC code to text in the import spec.

hth,
al

 

[KAllen31]

Thanks for the advise. I think I will try and run an append query monthly. I also noticed that Excel doens't do many things I would like. I live in New England and all New England zip codes start with 0. When you put any CSV file into Excel it will automatically delete the first zero in my zip codes!!! What that means is a bad zip code. This is a very BAD, BAD thing when you are trying to do some intelligent data analysis using demographics. I have to go in and format all of the cells as zip codes again.

Oh well, that is why I like to use Access more than I do Excel.

Thanks again.