Why! Text field imports to Access at a double (1 Viewer)

KAllen31

Registered User.
Local time
Today, 00:52
Joined
Feb 21, 2001
Messages
22
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:52
Joined
Feb 19, 2002
Messages
42,981
The .csv file may be the culpret. Text fields need to be surrounded by double quotes. If the SIC code is not, Access will treat it as numeric.

If you append to a table rather than creating a new one, this problem should go away.
 

RichMorrison

Registered User.
Local time
Yesterday, 19:52
Joined
Apr 24, 2002
Messages
588
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

Immoderate Moderator
Staff member
Local time
Yesterday, 19:52
Joined
Feb 28, 2001
Messages
27,001
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:52
Joined
Feb 19, 2002
Messages
42,981
An import spec should also work. Unfortunately, they don't work for Excel imports. Access INSISTS on determining data types by itself.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:52
Joined
Feb 28, 2001
Messages
27,001
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

Registered User.
Local time
Yesterday, 19:52
Joined
May 19, 2001
Messages
398
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

Registered User.
Local time
Today, 00:52
Joined
Feb 21, 2001
Messages
22
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. :mad:

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

Thanks again.
 

Users who are viewing this thread

Top Bottom