Problem importing large file

[Brian Ferguson]

Hi,
I have a 1.4M row file (7 columns total, 6 text, 1 datetime)
col1 text 10
col2 date/time
col3 text 4
col4 text 50
col5 text 40
col6 text 50
col7 text 40

The import file is a pipe (|) delimited file, with a leading pipe, when I
attempt to exclude a column (i.e. click on the Advanced button in the import
dialog) I get "Subscript out of range error".

I have tried this using Access 2000-2003 format and 2007 format. and get the
same error.

Any ideas?

Thanks
Brian

 

[John Nurick]

Hi Brian,

You say "with a leading pipe". If this means that each line of the text file
begins with a | and then contains the 7 fields, treat it as containing 8
fields of which the first is a text field that just happens to contain only
empty strings.

Also make sure that the linebreaks consist of the two characters CR and LF -
Chr(13) & Chr(10).

 

[Brian Ferguson]

John,
Thanks for you reply. Yes my file has a leading "|", so the file has 8
column. I want to skip the first column and import cols 2-8 into an existing
table. When I click the Advanced button (or finish) I get the Subscript out
of range error. Importing into a new table (skipping or not skipping the
first column) it works fine.


Thanks
Brian

 

[Klatuu]

There is an approach you may consider that would help this problem. First,
create an Import specification for the file. You can do that by starting an
import manually. When you get to the import wizard dialog, click on
Advanced. It should see that first column as being empty or Null. You can
tell it to skip that field. You can also set up the other columns like you
want them. Once you have the Import Spec set up, click on Save As. Give the
spec a name. Then include that name in your TransferText method. See VBA
Help for TransferText for specifics.

If you are not able to omit the first column, another way would be to link
to the file rather than import it and use an append query to pull the data
into your table.

 

[Brian Ferguson]

Thanks for you answer, I'll try that, however, my original concern is that is
is a bug (does not happen in 2003). I should be able to exclude columns in a
file when importing into an existing table. For me, this is a once/year
excerise,not really worth the effort to script it.

Thanks
Brian