CSV File import question

[JoeCruse]

Good Afternoon,

I'm working with comma-delimited CSV files, trying to import the contents into 2 related tabled (1-Many). I've done quite a bit of looking today, both here and in Help, but did not see the answer I needed. I've attached a copy of typical CSV file that I'm working on. The first field in the CSV file goes into the "One" table, which has an autonumber ID, date field, and labID field. It should go into the labID field. The second and third fields go into the "Many" table.

My question is; how do I tell Access in the code which field in the CSV file is which? In the attached file, the first field says "NBS 59a". This is a name which will go into the "One" table as the labID.
Here is some code I'm working with:

Dim dbs As DAO.Database          'Pull up database
Dim rst As DAO.Recordset         'Pull up individual recordset for table
Dim lecoID As Long               'The AutoNumber record ID
Dim labID As String              'The assigned LAB ID (labID) of the sample
Dim analysisdate As Date      'Date of analysis
Dim concentration As Double      'Result concentration, in %, from the analysis
Dim analyte As String            'Name of the analyte
Dim SQL As String                'The SQL statement used to pull data into the table "tblLecoAnalysisDetails"



Dim buffer As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblLECOAnalysis")   'Set database table
DoCmd.SetWarnings False                          'Turn off warning so that an Access message box does not appear
                                                 'for each record appended to the "tblLecoAnalysisDetails" table


Open "C:\Results\LECO.csv" For Input As #1       'Open the text file made by LECO to
                                                                    'import the data
Line Input #1, buffer
While Not EOF(1)
   
        labID = (Field1)                                  'Tells where on this line to grab for labID
        
        Set rst = dbs.OpenRecordset("tblLECOAnalysis")    'Opens table tblLECOAnalysis
        rst.AddNew                                        'new record set for data import
        lecoID = rst!lecoID                                 'Save the AutoNumber, we'll need it below
        rst!labID = labID                                 'Saves labID to labID field in table
        rst!analysisdate = date                           'Saves actual date of import to sdate field in table (note, don't use reserved word "date" for object

For labID, I did not know how to reference it from the CSV file, other than to say (field1). It does not work, so it must not be correct. Could anyone be kind enough to tell me how to refer to the first field in my CSV file in this code?

Thank you in advance.

 

[gemma-the-husky]

much easier to import the whole file into a single access table

then split it with queries and add the constituent bits to the relevant tables

easy to debug/validate etc

yuo can use import file spec to import the table with precision

 

[JoeCruse]

Well, I got what I needed after more searching. I found a text import example at Roger Carlson's site, and the example showed me how to take strings out of the text file, from in between the commas. I'll post my code here, with acknowledgment and thanks to Roger, in case anyone else runs across the need to import text like this (especially breaking it into 2 tables).

Option Compare Database
Option Explicit

Private Sub Form_Open(cancel As Integer)

Dim dbs As DAO.Database          'Pull up database
Dim rst As DAO.Recordset         'Pull up individual recordset for table
Dim lecoID As Long               'The AutoNumber record ID
Dim labID As String              'The assigned LAB ID (labID) of the sample
Dim analysisdate As Date         'Value of the date of analysis
Dim concentration1 As Double      'Result concentration, in %, from the analysis
Dim concentration2 As Double      'Result concentration, in %, from the analysis
Dim analyte1 As String            'Name of the analyte
Dim analyte2 As String            'Name of the analyte
Dim SQL1 As String                'The SQL statement used to pull data into the table "tblLecoAnalysisDetails"
Dim SQL2 As String                'The SQL statement used to pull data into the table "tblLecoAnalysisDetails"
Dim Mystring As String           'Variable to hold each data string as we come to it for import

Set dbs = CurrentDb
Open "C:\Results\LECO.txt" For Input As #1       'Open the text file made by LECO to import the data
Set rst = dbs.OpenRecordset("tblLECOAnalysis")   'Set database table
DoCmd.SetWarnings False                          'Turn off warning so that an Access message box does not appear
                                                 'for each record appended to the "tblLecoAnalysisDetails" table


 While Not EOF(1)
   Line Input #1, Mystring
        
        rst.AddNew                                        'new record set for data import
        
        lecoID = rst!lecoID                               'Save the AutoNumber, we'll need it below
        labID = Left(Mystring, InStr(Mystring, ",") - 1)  'Tells where and how much on this line to grab for labID
        rst!labID = labID                                 'Saves labID to labID field in table
        rst!analysisdate = date                           'Saves actual date of import to sdate field in table (note, don't use reserved word "date" for object
        
        rst.Update                                        'Update/save new record
        rst.Close                                         'Close table tblLECOAnalysis
                      
      
             Mystring = Mid(Mystring, InStr(Mystring, ",") + 1)
             concentration1 = Left(Mystring, InStr(Mystring, ",") - 1)  'String variable Concentration-here/how much on this line to grab for Concentration, and to trim off spaces on left side
             analyte1 = "C"                                            'Assign "C" as analyte
             SQL1 = "INSERT INTO [tblLecoAnalysisDetails] (lecoID, concentration, analyte)" & _
                          "Values(" & lecoID & ", " & _
                                      concentration1 & ", '" & _
                                      analyte1 & "');"                'Defined SQL statement used to append all analytes and their concentrations, along with corresponding ResultID, in table XRF Results Concentration
         
             DoCmd.RunSQL SQL1                                       'Runs the SQL statement
        
            'Run again to insert Sulfur
             
             Mystring = Mid(Mystring, InStr(Mystring, ",") + 1)
             concentration2 = Mystring                          'String variable Concentration-here/how much on this line to grab for Concentration, and to trim off spaces on left side
             analyte2 = "S"                                     'Assign "S" as analyte
             SQL2 = "INSERT INTO [tblLecoAnalysisDetails] (lecoID, concentration, analyte)" & _
                          "Values(" & lecoID & ", " & _
                                      concentration2 & ", '" & _
                                      analyte2 & "');"                'Defined SQL statement used to append all analytes and their concentrations, along with corresponding ResultID, in table XRF Results Concentration
         
             DoCmd.RunSQL SQL2                                       'Runs the SQL statement
        
   
   Wend
   
Close #1                                                  'Close the text file.
DoCmd.SetWarnings True                                    'Set Access warnings back on again.
Kill "C:\Results\LECO.txt"                                'Delete the test file being imported

DoCmd.Close acForm, "frmImportLECO"                       'Close the import form.

End Sub

 

[boblarson]

Just an FYI, but you can save yourself a lot of code if the text file is delimited by a constant character. You can use the SPLIT function (I believe that's in A2K and higher) to accomplish it and saves you having to have a lot of INSTR functions.

So, like this:

Dim varSplit As Variant ' will create a single dimension array

You would just then use it like this:

While Not EOF(1)
Line Input #1, Mystring

varSplit = SPLIT(Mystring,",")

And then to read it you just use the code to write to the table


rst!MyField = varSplit(0)
rst2!MyField = varSplit(1)

 

[JoeCruse]

Thank you, Bob. I'll work it in there and see what flies.

 

[boblarson]

Thank you, Bob. I'll work it in there and see what flies.

yeah, I just thought it might simplify things for you. I know that, when I learned about that one, it saved me a ton of coding for many things that have come up since.