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