Access overriding my choice of field type when importing CSV.

[RodG]

I'm trying to import a large CSV file (700,000+ rows) directly into a table. Some columns in the CSV file are nearly all numeric except for an occasional character. Knowing this I set the field in the table I am importing into as Short Text. Access seems to ignore this and treats the field as if it is Long, consequently the character strings which will easily fit into the field are ignored.
I have a similar problem with another field, this is all numeric, however the number is too long to fit into Long, so I set the field to Short Text again. Again Access thinks it knows better and imports as Long, then doesn't save it as the number won't fit into Long.
I also have similar problems with dates, The CSV file occasional has something like -/-/1986. I ask for it to go into a text field, but again it ignores the field type.

Altering the CSV file is not really an option. Any suggestions much appreciated.

R

[ItsMe]

How are you importing your CSV? A CSV file is text. Just import it as a text file.

[June7]

ItsMe, isn't that what poster is attempting?

[ItsMe]

ItsMe, isn't that what poster is attempting?

It does not seem so. Perhaps they are using Excel. Maybe linking to it.

[June7]

The poster says 'import a large CSV' but you think maybe they are not actually directly importing the CSV? That does make sense with the described issue.

[ItsMe]

Yeah, there seems to be a contradiction and it would help if some things were clarified.

[ssanfu]

Is the CSV file being imported using an import specification? That should force the data type.

[CJ_London]

Knowing this I set the field in the table I am importing into as Short Text. Access seems to ignore this and treats the field as if it is Long, consequently the character strings which will easily fit into the field are ignored.

A text field will accept any data up to it's defined size, so what you are suggesting does not make sense. The problem you describe relates to importing to a new table or an incorrectly defined specification. I would check that it is set up correctly and not treating a text field as long.

If left undefined Access (and excel and many other systems for that matter) will inspect up to the first 16 rows or so to determine field type - so if those rows only contain numeric data it will treat the field as numeric - possibly solvable by sorting the file to ensure the 'correct' field types are in the first 16 rows, but with 700k records....

It may be your initial 'test' import has set an incorrect specification and your subsequent attempts are using this wrong specification

[RodG]

It may be your initial 'test' import has set an incorrect specification and your subsequent attempts are using this wrong specification

Just to clarify, I am importing using {External Data}{Text File}.
As I have several difficult fields sorting is not an option, and anyhow, I think some of the files exceed Excel's row limit.
I was aware that if I let Access decide its own field types for the table it would get it wrong so the destination fields are set to Text without doubt.
Will Access retain some memory behind the scenes of what it did before and reuse this?

[ItsMe]

What I do is use the Wizard and let the wizard create a table for me. Then, I use the wizard again and select the table previously created as the destination. On the second Wizard, I save the procedure and give it a name. When I look at the table, all of the fields will be of data type Text. I then use the name I gave the saved procedure when I call the saved procedure via VBA code.

I treat the table created by the first Wizard as a temp table. I do not ever delete the table. However, I delete the data. Before and or after I run an import process, I delete all of the data in my temp table.

After I run the stored import procedure, I need to validate and append the imported data to a production table. I use VBA and specific data types for my variables as I run Gated Checks. One way is to nest DAO Recordsets and loop.

[CJ_London]

I was aware that if I let Access decide its own field types for the table it would get it wrong so the destination fields are set to Text without doubt.

By destination tables, I presume you mean the table. What I am referring to is the specification you can complete when using {External Data}{Text File}. When in this process, click on the advanced tab

Will Access retain some memory behind the scenes of what it did before and reuse this?

sort of - it creates a specification automatically. It cannot import without one