importing header and body information from text (csv) file

[JamesL]

I'm a fairly inexperienced Access user with some understanding of VBA code. I need a pointer in the right direction on how to approach this problem:

I get sent csv files created from Excel with data in them on a twice-weekly basis (more or less), which currently gets put into an access database by manual cut-and-paste out of Excel, which is tedious to say the least. The files (example attached) contain 11 header rows and also some comment footer rows. in the body data, the first column contains a sample ID. Not every import table contains the same number (or headings) of columns as the example.

I want to be able to import this data into 2 Access tables: one with the header information and the other with the 'body' data. Tables would look like this:

"header" table:
field Job_number (text)
field Client_ref (text)
field Date_Received (date)
field Date_completed (date)

"Body" data table:
field Sample_ID (text)
field Element (text)
field Unit (text)


field Method (text)
field Digestion (text)
field Value_text (text)
field job_number (from header, text)

so for every row with y columns in the original input file, there would be y rows with the same sample ID in the database table. If this seems inefficient, that may be the case, but I need to be able to accommodate any future changes in the combinations of Element - unit - method - digestion that will occur is different techniques are used (this is chemical assay data).

First of all, is this possible in Access? Second, a few pointers as to what commands to chase up. I'm assuming the first step is to import the whole thing into a access table, but how do I go about grabbing data from individual 'cells' to put into another table?

help appreciated!

jamesL

[ssanfu]

Using VBA, it is easy to open the CAV file and parse the data. Do you have a table structure created yet? I've looked at your example, but I don't understand how you want the data stored in the table. Could you provide an example of the Access table you envision?

[JamesL]

Using VBA, it is easy to open the CAV file and parse the data. Do you have a table structure created yet? I've looked at your example, but I don't understand how you want the data stored in the table. Could you provide an example of the Access table you envision?

OK Steve,

Access DB attached with examples of the 2 tables required, with the file header info and data for the first two sample numbers included. As I said before, the Assay_data table might look a bit clunky, but the design is intended to be able to import any possible future combination of element, method, digest and units if required (something which is common for this type of data), and I don't see how else to achieve it. I just realized that an extra table is required in this scenario to link sample_ID with the batch and lab job numbers, so there are now 3 tables in the example DB.

cheers

James
Example_DB.zip

[ssanfu]

I have A2K and can't open the example. It will be 4 -5 days before I can get to A2K7 to convert it.

[JamesL]

I have A2K and can't open the example. It will be 4 -5 days before I can get to A2K7 to convert it.

no worries, 2000 version attachedExample_DB_A2K.zip

james

[ssanfu]

OK, this is very (semi) quick and dirty.... Given the CSV you provided, the data gets imported. I threw together a form to check the data.
Note: THERE IS NO ERROR CHECKING -you have been warned!! And just a tiny bit of validation....

There is no data in the tables ..... and that is another thing. I changed the tables slightly. Well, probably more than a little.

Click on the IMPORT button to import the data.

The DELETE button is a cascading delete so I could delete the data while writing the code. The main form, subform and subsubform are all linked.

[JamesL]

Apologies for the delay in replying Steve, what with other work and the holidays I just got around to looking at this today.
Thanks very much for the VBA routine - it's close enough to what I need to be able to make any changes myself, and in any case it's good for me to learn some new skills!

appreciate the help,
James