CSV import strangeness

[MasterRacker]

I'm using Access 2007 with a 2002-2003 mdb.

I'm testing a few things to develop data cleanup tool. I have a CSV file that was exported from Excel. (I can't directly link to Excel: thread181-1489289) The first column is called "Incident" and contains data such as "200800010423", "200800010406", etc.

If I manually import into a new table, specifying text as the data type for that column, the data imports perfectly. I then did a DELETE *.... on that table to clear it and preserve the structure. I then tried to import the data again (same source file), this time into the existing table and every row fails on a type conversion error.

Especially as I'm sourcing from a CSV and importing to a text field, what could possibly cause this?

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[Remou]

Did you save and use the import specification when importing?

 

[jsteph]

Jeff
I've found that with Excel imports Access doesn't give you as much control over data type.

I always convert Excel to text before importing. You can do this programatically with OLE, using the .SaveAs method with the Workbook object.
--Jim

 

[MasterRacker]

Remou, I will eventually be doing this through VBA using differently named source files so I did not use an import spec. I did do exactly the same steps except using an existing table (when it failed it did it again more carefully to be sure).

jsteph, this is csv file - pure text.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[Remou]

You can have a number of different source files with the same import specification and this can be used with TransferText.

 

[jsteph]

Jeff,
You can use an import spec even if you use differently named sources, as long as they're the same structure.

I know that if you don't manually force Access to use text data type for fields that look like numbers, Access will use a number data type, even though it can't fit, which appears to be the case in your example--your sample data was larger than a long integer allows (if that data were a number and not what appears to be a year followed by an incident ID number).

Further, Access only looks at the first few rows, so if you had, say, rows 1 to 30 and they started out looking like numbers, Access would force long integer, even if row 31 was "ABCDEFG". (I'm not sure if it's 30 rows, but there is some sample that Access takes and it ain't very large).

--Jim