Sensible way to import CSV into table.

[chromachem]

We need to import data that has been exported from our Mass Spectrometer into Access.
Nothing is ever straightforward.

The attached zip file contains 3 files.

File 1) "072823 as exported from Mass Spec software.csv" This is the datafile generated by exporting the results of a run of several samples from our Mass Spec. Lots of stuff we dont need in our table.

File 2) "072823 formatted for import into Access tbl 537.csv" This file was created from File 1 above. We use excel VBA to format the csv to use for import into Access tbl537.

File 3) "tbl537.accdb" The database contains one table showing the results from importing the file formatted for import (File 2) into access.

So, we take the file generated from the mass spec, use excel to format it for import, then fire up Access and import the formatted file.

I am just getting started with access. I know Excel VBA pretty well. It just seems like I have one too many steps in the process.



I tried to import the file from step one directly into Access. That was a no go. On the other hand, we're trying to wean ourselves off Excel as much as possible.

Is there a relatively straitghtforward way to format File 1 within Access so that it can be imported into tbl537 without the intermidiary use of excel to create the formatted file for import (File 2)?


Note: File 2 deletes 3 rows from File 1 and also gets the file headers all set.
I aplogize for the cumbersome file names.

As always, thanks for taking a look.

[ranman256]

'*******************
' TEXT FILE IMPORT
'*******************

---------------
PREP
---------------
1st, manually import the text file,
in the 'import screen' ,bottom left corner is ADVANCED
define your fields in the text as an IMPORT SPEC.
SAVE this spec. (i.e: "SpecName")

manually link this CSV file using the spec name so it becomes an External Table: txtFile2Import

make an append query to append the txtFile2Import fields to the target table: qaImportCsvFile


---------------
EXECUTION
---------------
1. each new CSV file you get, save it to the same place every time,like: c:\temp\txtFile2Import.csv
2. run the import query: docmd.openquery "qaImportCsvFile"
3. Done

[CJ_London]

you can do this with sql, no need for the excel step

INSERT INTO tbl537Acc ( LabID, [Level], [Date], PFBS, [Symmetry PFBS], [13C6-PFHxA], PFHxA, [Symmetry PFHxS], F26, F32, F38, F44, F50, F56, F62, F68, F74, F80, F86, F92, F98, F104, F110, F116, F122, F128, F134 )
SELECT *
FROM (SELECT Replace(f4,'.d','') AS LabID, F6 AS [Level], DateValue([f7]) AS [Date], F8 AS PFBS, F11 AS [Symmetry PFBS], F14 AS [13C6-PFHxA], F20 AS PFHxA, F23 AS [Symmetry PFHxS], F26, F32, F38, F44, F50, F56, F62, F68, F74, F80, F86, F92, F98, F104, F110, F116, F122, F128, F134 
            FROM [TEXT;DATABASE=D:\Dev\1_temp;HDR=No].072823.csv
              WHERE F4 Not Like 'Blank*' And F4<>'Data File')  AS txt

Notes:
1. I renamed your '072823 as exported from Mass Spec software.csv' file to just 072823.csv - rename as you require
2. The directory D:\Dev\1_temp is where I placed the file - rename as you require
3. Because of the irregular nature of the headings you need to set headers to No which automatically sets column headings to F1, F2 etc
4. The accdb you provided has a linked table, so I created my own (tbl537Acc) as what I believe would be the equivalent. I named the first few fields but don't really have the time to do the whole thing
5. Within the select part of the above code I've aliased the first few columns but that is not really necessary, just did it to illustrate you can..
6. The Where clause excludes the second header row and the blank records
7. LabID - due to the mixed datatypes the column defaults to numeric rather than text, not quite sure why, probably because there is not header, but can still be determined from the datafile column.
8. Edit:, just noticed the time element is required for the import, the above limits to the date only - just change 'DateValue([f7]) AS [Date]' to 'f7 AS [Date]'

I've attached your file with the query, just modify path and file name, destination table name and whatever field names you are using

If required, the query can be modified to left join to the destination table to either only append new results or to update and append

[chromachem]

That is really slick. I knew there had to be a way to go direct from the MS export into the table.

I can handle the aliases - just to be sure we have the correct data associated with the correct header.
We'll do this over and over. I'd like to make a form with a text box to enter file names and stick the file name in a variable.
Can I stick the variable in the sql code?

Something like:

strA = txtFileName  'txtFilename from text box on form


FROM [TEXT;DATABASE=D:\Dev\1_temp;HDR=No].strA 'or

FROM [TEXT;DATABASE=D:\Dev\1_temp;HDR=No].txtFileName  ' use txtFileName from the text box and skip the variable

Two other things: tbl537Acc is empty. Just headers.

The datasheet view of the query shows the contents of the csv.

I am a total goob with SQL. On the other hand, once I see the syntax I can usually use the code in the future. I hate to be a dummy, but I'll ask anyhow. How would you add code so that results would be update or append depending on whether or not the data already existed.

Thanks!

[Welshgasman]

No, you would need to concatenate the filename

Along the lines of

strSQL = strSQL & " FROM [TEXT;DATABASE=D:\Dev\1_temp;HDR=No]." & txtFileName

If the path was different, then you would need to do the same there.

strSQL = strSQL & " FROM [TEXT;DATABASE=" & txtPath & ";HDR=No]." & txtFileName

[CJ_London]

Two other things: tbl537Acc is empty. Just headers.

the idea was for you to modify the path and file name then execute the query

Edit: responding to other points

The datasheet view of the query shows the contents of the csv.

it is an append query, viewing in datasheet view will show records to be appended. Just double clicking to open it will execute it, or if open in design view click the ! button on the ribbon

How would you add code so that results would be update or append depending on whether or not the data already existed.

google/bing 'access upsert query', plenty of examples out there - here is one https://stackoverflow.com/questions/...crosoft-access

You do need a unique ID to match on in your source and destination tables (that is unique for all time, not just this particular 'load'). Insufficient info provided but might be LabID perhaps combined with one or more other fields

[chromachem]

Re: tbl537Acc is empty - I copied the database on the the forum to my desktop. Once I realized Macros were blocked (and subsequently unblocked), the append worked as it should. No problem modifying the code to point where it needs to go.


Re: How would you add code so that results would be update or append depending on whether or not the data already existed.

I was thinking this morning (OK - in the shower) that the upsert problem is one that is in no way unique. Just look it up! What he said (CJ).

A sincere thank you to all of you that took the time to look and help. Honestly very much appreciated.