Text File Import

[RShrader]

Hi,

I have a text file that contains data that I want to import into my MS
Access database. The layout of this text file contains a number and 5
pieces of data for that number as in the example below.

12345;data1;data2;data3;data4;data5


I would like the data to be imported into my MS Acces table in the
following format.

12345 data1
12345 data2
12345 data3
12345 data4
12345 data5

In this example, the unique number has as many records as there is
data (that's seperated by the delimiter).

Any help or a point in the right direction to any web resources
dealing with this type of importing would be helpful.

Thanks in advance.

(e-mail address removed)

 

[Ken Snell [MVP]]

Import the data into a temporary table that has the six fields.

Then, use a union query to normalize the data as you wish it to be:

SELECT Field1, Field2 FROM TempTable
UNION ALL
SELECT Field1, Field3 FROM TempTable
UNION ALL
SELECT Field1, Field4 FROM TempTable
UNION ALL
SELECT Field1, Field5 FROM TempTable
UNION ALL
SELECT Field1, Field6 FROM TempTable;


Then create an append query that uses the above union query as its source
table, and append the data to your permanent table.