TSV Data import

[SP]

Hi, I'm new to access but I have some of experience with Excel data queries.

The problem that I have is with importing data from tsv file (tab
delimited data from EUROSTAT). It looks like this:

[field1],[field2],[field3] [time1] [time2] [time3] ...
code1,code2,code3 data1 data2 data3 ...
....

As you can see it is tsv but only the data is separated with tabs, the
fields are separated with comma.
So I don't know how to tell Access to read first three columns like
codes and the next ones like data.

Next, sometimes the data is missing for some period and there is ":"
instead.

And another case is when for some data there are flags which say that
the data is provisional or something else important to this data. It
looks like: data1 [f] data2 [f] ... The separator between data1 and [f]
is space.

Your help and advice will be highly appreciated.

 

[Jerry Whittle]

1. Open up the text file and replace the commas with tabs.
or
2. Create a table with the proper data structure and fields. Import into
this table. The first row should be thrown away as an error.

The flags are a real problem. If you don't need them, delete them in the
text file before importing. If you do need them, import into a temporary
table with all the fields set as text. Then run some fancy queries to move
around the data as needed.

Better yet look into the EUROSTAT program. There might be ways to tailor the
output of the data so that you can use it readily in Access.