Import Text data using import specification

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
876
I have a text file that has about 50 fields and I am importing it into a table that has just 10 fields. The first time that I import it, I import it into a New Table. I skip over 40 of the fields. This works fine. I end up with exactly the fields that I want. And I save the import specification so I can use it again.

The next time I try to import the file (e.g., the next day), I do /File/Get External Data/Import and then in the Import Text Wizard I press the Advanced button then the Specs button and select the Import spec that I want to use. It shows all the field names and has the tick boxes showing that most of them will be skipped. I say OK and proceed. Everything seems to go fine until I say I want to store my data in an existing table and name it. Access then gives an error message saying that the first field on the text file does not exists in the table and then says that the data was not imported. Now that field was specified to be skipped. So why is this causing a problem?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
The reason that I've been having trouble importing a subset of columns from a delimited text file into an existing MS Access table is that you cannot do it.

This is made clear in this Microsoft help article:
http://office.microsoft.com/en-us/access-help/import-or-link-to-data-in-a-text-file-HA001232227.aspx

And here is the relevant passage:

Skipping records and values

If you are interested in only a portion of the text file, edit the source file before you start the import operation. You cannot skip records during the import operation.
<b>If you are adding the data to a new table, you can skip certain fields, but this option is not available if you are appending the contents to an existing table.</b> When you append data to an existing table, the structure of the source data must match the structure of the destination table. In other words, the source data must have the same number of columns as the destination table, and the data types of the source data must match the data types of the destination table.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top