Import errors importing Text into existing table, header vs no hea

[gus]

I import large csv or vert-bar delimited text files into MS Access 2007
(12.0.6211.1000) SP3 MSO (12.0.6320.5000). The text files are created on
UNIX servers. I import into an existing table, and the column layout of my
text file is the same as the layout of my existing table.

When I have no header record in the text file, MS Access has no problems
importing the data and appending it to my existing table.

When I have a header record in the text file, MS Access appears to attempt
to interpret the types of data in the columns even though the import is
appending to an existing table that has types defined for all columns.
Columns that have both (1) mixed numeric and TEXT data, and (2) numeric on
the first few rows (maybe 25 rows?), appear to be interpreted as numeric and
the rows with TEXT get import errors.

Also, it appears to behave differently if there is just 1 field (works OK)
versus more than 1 field (gets errors as described above).

I tested this by:
(1) create a new MS Access table TEST1 with no index and with 2 fields
named TextField1 and TextField2, both defined as TEXT.
(2) create a text csv file with 26 rows, where the first 25 rows were 0,0
and the last row was BBBB,AAAA.
(3) create a text csv file with 27 rows, where the first row is
TextField1,TextField2 and the next 25 rows were 0,0 and the last row was
BBBB,AAAA.
(4) use the import wizard to append the text file created in (2) to the
table created in (1). No errors at all reported my MS Access.
(5) use the import wizard to append the text file created in (3) to the
table created in (1), being sure to tell the import wizard that the "First
row contains field names". MS Access reports an import error on data row 26
(27 if you count the header record), which is the row that has BBBB,AAAA.

In my testing, the order in which I did steps (4) and (5) above did not
matter. The error always happened when importing the text file that had the
header.

My work around will be to eliminate the header record and make sure my text
file format exactly matches the column layout of the existing MS Access
table. I wanted to report this, as IMHO the presence/absence of a header
record should have no effect on how MS Access handles the import when
appending to an existing table.

Thanks much
Gus

 

[Klatuu]

I suggest you use an import specification. It can eliminate all the problems
you are having.

 

[gus]

Thanks for the reply, I hope that I understood your reply correctly.
Referring back to the test steps in my post, saving the import steps as an
Import Specification allows me to either repeatedly perform the "good" import
from my step (4), or repeatedly perform the "bad" import from my step (5).

I am not looking for a work-around.

What I would like to know is: why does having a header record in the Text
file change the behavior of the import in MS Access 2007?

Thank you very much

 

[Klatuu]

As to why it acts differently in 2007 I can't say. I have not done any text
imports in 2007. As I recall, in 2003, it would just see the header row as
data and create a record that would have the field names in each field.

I really don't think it is a work around. I always use import
specifications when importing text data. That way, you get the field names
you want and the data types you want. To do it in VBA, you just about have
to.

Some of the data typing issues are because of how you have your test laid
out. Access looks at the first few rows (I can't remember how many) and
makes an assumption on data type. In your example # 5, you start with 0,0
and the last record is text. That is why you get the error there.

Now to cure the problem, do the import spec. But, you don't use the wizard.
Create a TransferText macro or use VBA.
--
Dave Hargis, Microsoft Access MVP

Thanks for the reply, I hope that I understood your reply correctly.
Referring back to the test steps in my post, saving the import steps as an
Import Specification allows me to either repeatedly perform the "good" import
from my step (4), or repeatedly perform the "bad" import from my step (5).

I am not looking for a work-around.

What I would like to know is: why does having a header record in the Text
file change the behavior of the import in MS Access 2007?

Thank you very much