Load table with .tsv file

[pstraszynski]

What is the best way to load an Access table from a .tsv file? ("tsv" officially stands for "tab seperated values", but in my file the seperator/delimiter is a vertical bar ("|"). )

The .tsv file has over 200 fields but the table only has about 25. - P.S.

 

[pstraszynski]

What's wrong with this insert line?

These lines give me an error "Compile error: Expected: end of statement" and highlights "Customer1". I do have a table of that name and I've tried every combination of spaces & parentheses I can think of. -PS

Insert Into Customer1(City,Postal Code) _
Select ClientCity, ClientPCode From "U:\Power Smart\ecoEnergy\Pauls Test Area\Pauls_File_Conv_TSV_to_XL.xlsm";

 

[gemma-the-husky]

import to a new table - you can set pipe symbol or any other as field separator.

then look at automating this, and saving a file specigication.

 

[pstraszynski]

Load Access table with many .tsv files

I have several hundred .tsv files that I need to load to a table in Access 2007. The best way would be to load it into an Excel file first. I want the column name to appear once in the Excel file, followed by rows of data.

I have an Excel 2007 macro that will do it for one .tsv file but I don't know how to modify it for more .tsv files. I have an INSERT INTO query in Access VBA that works, but also for only a one-row table (this must be modified for the multi-row table). All suggestions appreciated. - Paul S.