Pull Data from Multiple CSV's

[Eskimo]

I am able to import or link into Multiple CSV's in my database from a folder.
In total there are 24 csv's so there are 24 tables.

Each imported or linked table all have the same, non-normalized structure
where there is data from rows 1 through 20 that contain general information
about the product. There is nothing I need from those rows, with the
exception of a number from Row 8 Column 2.

From Row 21, the tables become more normalized as a tabled structure. The
21st row is header information and from 22 on down, lies the data that I am
looking for.

Is there a query string I can run that will pull out data from each table to
include all rows from 22 down and include the number in cell 8,2?

In other words what I am trying to do is combine all the tables, or CSVs as
one table or query, then place the corresponding number from cell 8,2 into a
column from where each line is derived from.

So really, there are a few steps here that I am doing.

1)import all the CSVs into a table or Tables.
2)filter out all the redundant data from certain cells in rows 1 through 20,
except for cell 8,2
3)grab all of the normalized data from Rows 21 on down, with 21 being header
info.

I can than apply further queries. As an example of one of the 24 CSVs... one
file name is 613844A Condensed.csv and the number in that particuliar csv is
always 83767. Each csv has its own unique number in cell 8,2.

If I can get some help here, that would be excellent.

Thanks,

Eskimo

 

[Jeff Boyce]

If this were mine, I'd probably use a procedure rather than trying to get a
query to ignore the first 21 rows (except for row 8, column 2).

I'd use the procedure extract the data from row 8, column 2, then skip down
to row 22 before INSERTing the rows' data into a permanent (well-normalized)
table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[pietlinden]

If this were mine, I'd probably use a procedure rather than trying to geta
query to ignore the first 21 rows (except for row 8, column 2).

I'd use the procedure extract the data from row 8, column 2, then skip down
to row 22 before INSERTing the rows' data into a permanent (well-normalized)
table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Can this be done with ADO or DAO? I tried it using Excel automation,
and it's *really slow*!!! not to mention the huge overhead of opening
Excel to read a single cell value...

 

[Jeff Boyce]

I don't know about ADO vs DAO...

I'd probably link to the list/table, then create a query that returns all
rows, then use the procedure against that query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

If this were mine, I'd probably use a procedure rather than trying to get
a
query to ignore the first 21 rows (except for row 8, column 2).

I'd use the procedure extract the data from row 8, column 2, then skip
down
to row 22 before INSERTing the rows' data into a permanent
(well-normalized)
table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Can this be done with ADO or DAO? I tried it using Excel automation,
and it's *really slow*!!! not to mention the huge overhead of opening
Excel to read a single cell value...

 

[pietlinden]

I don't know about ADO vs DAO...

I'd probably link to the list/table, then create a query that returns all
rows, then use the procedure against that query.

Regards

Jeff Boyce
Microsoft Office/Access MVP





Can this be done with ADO or DAO? I tried it using Excel automation,
and it's *really slow*!!! not to mention the huge overhead of opening
Excel to read a single cell value...

but then how do you get Cell(Row:=8, Column:=2), since record position
in a database is essentially meaningless?

 

[Jeff Boyce]

Good point! I'd need a way to 'order' the records/rows. I wonder if the
Access import process would import in exact order ... if so, letting Access
set a primary key would probably give a sequential number to use.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Eskimo]

Hi Jeff et al.

Perhaps there is another way to pull the number from cell 8,2 for each
database.

You see, each of the 24 CSVs have a constant filename, and each of the
filenames always carry the same, but unique values for cell 8,2.

If I were to create a table that lists filenames with corresponding values
from 8,2, would we be able to use that in a procedure?

So then, the only procedure we would need is to gather all data from row 22
on down from each of the CSV's then add a column that is filled in based on
which filename the line came from and assign the value from the manually made
table?

Just a thought, because the filenames are constant and that each of the
filenames have a corresponding unique ID in the cell 8,2

Eskimo