Import Multiple with filename

[Eskimo]

Hi all,

I have 23 CSV's in a folder, each with a unique filename. one file
represents a collar from a wild animal that tracks movements.

Anyway, the structure of each csv is this. Rows 1 through 21 is redundant
non-normalized data that I dont need.

What I need is anything below line 22. Line 22 is header info, with 23 down
being the data I need.

What I have done already is linked each of the csv's in the folder to the
database with its own link. Now I am trying to combine each table into one,
but make sure that the records I combine all have a new column indicating
which table it came from.

Any ideas if this is possible?

Thanks,

Eskimo

 

[bhicks11 via AccessMonster.com]

Hi Eskimo,

I'm not sure I understand what you are trying to do. Is the problem only
importing from row 23 forward? Or combining them? Is there anything in row
1-22 that can be used to filter?

Bonnie
http://www.dataplus-svc.com

 

[Ken Snell \(MVP\)]

You can use a union query to combine the separate tables' (files') data into
one recordset, and then you can use that query as a source of data to write
the data into a new table, or just use the union query to display the data:

SELECT *, 'LinkedFile1Name' AS FileSouce
FROM LinkedFile1
UNION ALL
SELECT *, 'LinkedFile2Name' AS FileSouce
FROM LinkedFile2
UNION ALL
SELECT *, 'LinkedFile3Name' AS FileSouce
FROM LinkedFile3

 

[Eskimo]

Hi B,

I can take a step back here and think this properly.

Each CSV all have the same structure. Rows 1-21 contain non-normalized
general information about the collar that I don't need. Row 22 contains
header information, but I really don't need that. The important and
normalized data starts in row 23. There are 15 columns when the normalized
data start.

With Ken snells suggestion, I can run a Union query to comine each of the
CSV's into one, then I am able to query out those lines that I do need only.

But for each line that I am querying, I need to be able to add a column that
tells me which table the information comes from.

In shorter form, what I am essentially doing is this....

1) Importing all 24 CSV's from a folder. I don't know if it is wiser to run
a routine that will import and append the information each time and archive
the source file or to continually build on the source file and just link each
table. As of now, I am just linking. (for some reason, I think I would rather
import then append rather thank link)

2)Once I have imported/linked all of the CSV's I need to combine them all.
It does not matter so much that I also include the redundant data because I
can run a query to pull the info I need, but I need to include a column to
show me where the rows of information are coming from.

Thanks for your help here.

Eskimo

 

[Eskimo]

Hi Ken,

The union query and the filname as filesource trick worked great.

Thank you very much.