Text File Import

[JoeCruse]

Greetings.

I'm working on importing data from a spectrometer into my Access application. The vendor has completely changed their file system with a recent software upgrade, forcing me to look for a new way to import analysis data. One possibility is to import it in individual text files. It sounded promising, until I opened one of the files and then tried importing it into Access. The way the vendor formats the file, it is going to be a bear to do this.

I'm attaching a sample file here, to see if some of you would be kind enough to look at the text file and see if it is possible to import.

In the first row, I need the "16032" as a sample ID, and the "0503170128" as the date/time. In the 2nd row, I need the "75FESI16" as a file name. In the 3rd row, I need the "75HP" as a program name. The 4th row starts the data. I need the numbers in the 3rd column, and the element signs in the 5th column. I don't need any of the other stuff, but I see no good way to separate it all out in the Import.

Anyone have any ideas as to whether this text file can be used?

Thank you in advance,

 

[ghudson]

Welcome to the forum.

Search the forum for there was a thread discussing reading files [text] within the past two/three weeks. That should point you in the right direction.

To get the data in to access you will have to import the data with a custom file spec which will ignore the first five rows.

Search Access help of this forum for "file spec" if you need more info.

 

[JoeCruse]

Hi, GHUDSON, thanks for replying.

I've searched the forums pretty hard for import info, but only saw one post that related with my own problem. It was 2 years ago, and the poster never replied back if they had resolved the issue (unfortunate for me, as they were dealing with a lab instrument file issue like me).

I think I could handle the import and subsequently get the data I wanted, IF the text file was in a better format. I've got to have a few items in those first few rows, just not all of it.

I've done this a few years ago with text files off another instrument, but the text files that this instrument shot out were in a MUCH better format.

Thanks again.

 

[WayneRyan]

Joe,

Untested, but something like this should work:

tblSamples
==========
SampleID - AutoNumber
SampleNumber (16032)
SampleDate   (0503170128)
FileName     (75FESI16)
ProgramName  (75HP)

tblSampleData
=============
SampleDataID - AutoNumber
SampleID     - FK to tblSamples
TheNumber      (75.53435)
Element        (Si)

Command Button Code:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim SampleID As Long ' <-- The AutoNumber
Dim SampleNumber As String
Dim SampleDate As String
Dim FileName As String
Dim ProgramName As String

Dim buffer As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSamples")

Open "C:\YourFile.txt" For Input As #1

Line Input #1, buffer
While Not EOF(1)
   Select Case Mid(buffer, 1, 1)
      Case "S"
        SampleNumber = Rtrim(Mid(buffer, 3, 10))
        SampleDate = Rtrim(Mid(buffer, 64, 15)
      Case "A"
        FileName = RTrim(Mid(buffer, 3, 15))
      Case "D"
        ProgramName = RTrim(Mid(buffer, 6, 10))
        Set rst = dbs.OpenRecordset("tblSamples")
        rst.AddNew
        rst!SampleNumber = SampleNumber
        rst!SampleDate = CDate(SampleDate, "yymmddhhmm")
        rst!FileName = FileName
        rst!ProgramName = ProgramName
        SampleID = rst!SampleID ' <-- Save the AutoNumber, we'll need it
        rst.Update
        rst.Close
      Case "C"
        DoCmd.RunSQL "Insert Into tblSampleData (SampleID, TheNumber, Element) " & _
                     "Values(" & SampleID & ", " & _
                                 Mid(Buffer, 7, 9) & ", '" & _
                                 Mid(buffer, 22, 2) & "');"
      End Select
   Line Input #1, buffer
   Wend

Wayne

 

[JoeCruse]

Wayne,

many thanks, sir. I'll test it Monday.

Joe

 

[JoeCruse]

Wayne,

the only 2 problems I ran into were simple syntax ones, which were easily fixed, except for one line:

rst!SampleDate = CDate(SampleDate, "yymmddhhmm")

I keep getting the "Expected )" error at the comma. It does not want the date format thrown in there like that, but I've not been able to tell it how to format it. I tried setting an intermediary variable to equal the parsed test string for the date, and then running the CDate function on it to return the SampleDate as a date value, but get a "Type Mismatch" error. Maybe it's balking at going from string to date, even if you "suggest" a format?

Any ideas on correct syntax there?

Thanks again,
Joe

 

[fuzzygeek]

CDATE doesn't take but one variable CDATE(variable).
Try format is it is a text field:

dadate = Format("29-OCT-2003", "yymmddhhmm")

yields -> 0310290000

 

[WayneRyan]

Joe,

Don't have my notes!

rst!SampleDate = CDate(SampleDate, Mid(buffer, 66, 2) & "/" & _
                                   Mid(buffer, 68, 2) & "/" & _
                                   Mid(buffer, 64, 2) & " " & _
                                   Mid(buffer, 70, 2) & ":" & 
                                   Mid(buffer, 72, 2))

Closer, when you run it, just put "Stop" in the first line of code.
That will stop the code.

F8 will single-step thru the code.
Hover over a variable to see its value.
F5 will run to completion.

You can also select: View --> Immediate Window

Then in the lower window, you can type:

? Mid(buffer, 66, 15)

And it will display its value.

Not too bad for a first shot ... let me know how it works.

Wayne

 

[JoeCruse]

Thank You!!!

Wayne,

your suggestions were great! I made modifications and got it working. I'm a novice, or worse, at VBA. I learned a lot today by pouring over your 2 posts and then doing an "F1" to learn exactly what you were pointing to. I still have a ways to go to get this implemented, but this part, actually being able to do the import from the file, was the biggest and hardest part, for me.

Thank you very much!

I'll post the code I ended up using, in case anyone else could use an example. If anyone sees an easier implementation, I'd appreciate feedback, if possible. Thanks again.

Private Sub cmdimport_Click()

'This code made with MUCH help from Wayne Ryan at the Access World Forums.
'It opens a text file made by SuperQ software, containing individual analysis
'results and parses out the desired sample information to the table "XRF Results"
'and the desired sample data to the table "XRF Results Concentration". The 2 tables
'are in a One-to-Many relationship, and the ResultID, an autonumber ID in "XRF Results",
'ties the related records together.

Dim dbs As DAO.Database          'Pull up database
Dim rst As DAO.Recordset         'Pull up individual recordset for table
Dim ResultID As Long             'The AutoNumber record ID
Dim SampleName As String         'The name of the actual sample analyzed
Dim SampleDate As String         'String value of the date of analysis, from the text file
Dim ResultDateTime As Date       'Date/Time value of the converted string for date of analysis
Dim sdate As Date                'Date value in Date format, used to link other tables to the sample
Dim ArchiveName As String        'Archive name from SuperQ that the sample was stored in
Dim MeasureOriginName As String  'Analytical program used to analyze sample in SuperQ
Dim Concentration As Double      'Result concentration, in %, from the analysis
Dim CompoundName As String       'Name of the analyte
Dim SQL As String                'The SQL statement used to pull data into the table "XRF Results Concentration"



Dim buffer As String

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


Open "C:\Program Files\PANalytical\Results\KC170128.txt" For Input As #1    'Open the text file made by SuperQ to
                                                                            'import the data
Line Input #1, buffer
While Not EOF(1)
   Select Case Mid(buffer, 1, 1)
      Case "S"
        SampleName = RTrim(Mid(buffer, 3, 25))
        SampleDate = RTrim(Mid(buffer, 64, 10))
        ResultDateTime = Mid(buffer, 66, 2) & "/" & _
                                   Mid(buffer, 68, 2) & "/" & _
                                   Mid(buffer, 64, 2) & " " & _
                                   Mid(buffer, 70, 2) & ":" & _
                                   Mid(buffer, 72, 2)
        
      Case "A"
        ArchiveName = RTrim(Mid(buffer, 3, 15))
      Case "D"
        MeasureOriginName = RTrim(Mid(buffer, 6, 10))
        Set rst = dbs.OpenRecordset("XRF Results")
        rst.AddNew
        ResultID = rst!ResultID ' <-- Save the AutoNumber, we'll need it
        rst!SampleName = SampleName
        rst!ResultDateTime = CDate(ResultDateTime)
        rst!sdate = Date
        rst!ArchiveName = ArchiveName
        rst!MeasureOriginName = MeasureOriginName
        rst.Update
        rst.Close
      Case "C"
        Concentration = LTrim(Mid(buffer, 7, 9))
        CompoundName = RTrim(Mid(buffer, 22, 6))
        
        SQL = "INSERT INTO [XRF Results Concentration] (ResultID, Concentration, CompoundName)" & _
                     "Values(" & ResultID & ", " & _
                                 Concentration & ", '" & _
                                 CompoundName & "');"
         
        DoCmd.RunSQL SQL
        
      End Select
   Line Input #1, buffer
   
   Wend
Close #1                                        'Close the text file.
DoCmd.SetWarnings True                  'Set Access warnings back on again.

End Sub

 

[JoeCruse]

Thank You!!!

Oops, double post.

One note; I ended up changing the way you had in your suggestion for getting the actual elements and concentration, Wayne. Access did not like the SQL statement, worded as it was. Looking through the Help Files got me a solution though.

Thanks again, Wayne.

Joe

 

[WayneRyan]

Joe,

Glad to help out. Hope to hear from you again as you progress.

btw,

If you put:

(code)
Some code ...
(/code)

Your code will preserve the indentations and be easier to read. You have
to use the square brackets instead of the parentheses.

Wayne

 

[spectrolab]

I know this is a pretty old post, but I am hoping someoene can help! I am using a modified version of this ( Thanks Joe, Wayne and others!) but I was wondering if it can be simplified a bit to fit my current db.What i am hoping to do is combine the 2 tables in the code "XRF Results" and "XRF Results Concentration" into one table where all the data is displayed in one record set. The 'CompundName' in the code are actually field name in a table, and the order of these doesn't change. The "Concentration" mentioned in the code is the data that needs to populate the table in the appropriate field. I sthere any easy way to do this? I have been trying for months and still can't get anywhere! The code works fine, it is just that the tables made don't really gel with the rest of the db, which has taken me a year to make and has over 100,000 records. Adding 10 or so to the 'XRF Results Concentration' every time data is added is making it absolutely huge.

Thanks in advance for any help!

here is the code

Private Sub Command9_Click()

'This code made with MUCH help from Wayne Ryan at the Access World Forums.
'It opens a text file made by SuperQ software, containing individual analysis
'results and parses out the desired sample information to the table "XRF Results"
'and the desired sample data to the table "XRF Results Concentration". The 2 tables
'are in a One-to-Many relationship, and the ResultID, an autonumber ID in "XRF Results",
'ties the related records together.

Dim dbs As DAO.Database          'Pull up database
Dim rst As DAO.Recordset         'Pull up individual recordset for table
Dim ResultID As Long             'The AutoNumber record ID
Dim SampleName As String         'The name of the actual sample analyzed
Dim SampleDate As String         'String value of the date of analysis, from the text file
Dim ResultDateTime As Date       'Date/Time value of the converted string for date of analysis
Dim sDate As Date                'Date value in Date format, used to link other tables to the sample
Dim ArchiveName As String        'Archive name from SuperQ that the sample was stored in
Dim MeasureOriginName As String  'Analytical program used to analyze sample in SuperQ
Dim Concentration As Double      'Result concentration, in %, from the analysis
Dim CompoundName As String       'Name of the analyte
Dim SQL As String                'The SQL statement used to pull data into the table "XRF Results Concentration"



Dim buffer As String

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


Open "C:\Results\results.txt" For Input As #1    'Open the text file made by SuperQ to
                                                                            'import the data
Line Input #1, buffer
While Not EOF(1)
   Select Case Mid(buffer, 1, 1)
      Case "S"
        SampleName = RTrim(Mid(buffer, 3, 25))
        SampleDate = RTrim(Mid(buffer, 64, 10))
        ResultDateTime = Mid(buffer, 66, 2) & "/" & _
                                   Mid(buffer, 68, 2) & "/" & _
                                   Mid(buffer, 64, 2) & " " & _
                                   Mid(buffer, 70, 2) & ":" & _
                                   Mid(buffer, 72, 2)
        
      Case "A"
        ArchiveName = RTrim(Mid(buffer, 3, 15))
      Case "D"
        MeasureOriginName = RTrim(Mid(buffer, 6, 10))
        Set rst = dbs.OpenRecordset("XRF Results")
        rst.AddNew
        ResultID = rst!ResultID ' <-- Save the AutoNumber, we'll need it
        rst!SampleName = SampleName
        rst!ResultDateTime = CDate(ResultDateTime)
        rst!sDate = Date
        rst!ArchiveName = ArchiveName
        rst!MeasureOriginName = MeasureOriginName
        rst.Update
        rst.Close
      Case "C"
        Concentration = LTrim(Mid(buffer, 7, 9))
        CompoundName = RTrim(Mid(buffer, 22, 6))
        
        SQL = "INSERT INTO [XRF Results Concentration] (ResultID, Concentration, CompoundName)" & _
                     "Values(" & ResultID & ", " & _
                                 Concentration & ", '" & _
                                 CompoundName & "');"
         
        DoCmd.RunSQL SQL
        
      End Select
   Line Input #1, buffer
   
   Wend
Close #1                                        'Close the text file.
DoCmd.SetWarnings True                  'Set Access warnings back on again.

End Sub

 

[spectrolab]

Tried a different approach, using a different format for the txt file, here's the code

Private Sub Command9_Click()



Dim dbs As DAO.Database          'Pull up database
Dim rst As DAO.Recordset         'Pull up individual recordset for table
Dim ResultID As Long             'The AutoNumber record ID
Dim SampleName As String         'The name of the actual sample analyzed
Dim SampleDate As Date           'String value of the date of analysis, from the text file
Dim Fe As Double
Dim SiO2 As Double
Dim Al2O3 As Double
Dim P As Double
Dim S As Double
Dim CaO As Double
Dim MnO As Double
Dim K2O As Double
Dim TiO2 As Double
Dim MgO As Double
             
Dim ArchiveName As String



Dim buffer As String

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


Open "C:\Documents and Settings\Nigel\My Documents\Results\Transfer\SuperQ.txt" For Input As #1    'Open the text file made by SuperQ to
                                                                            'import the data
Line Input #1, buffer
While Not EOF(1)
   Select Case Mid(buffer, 1, 1)
      Case "Sample"
        SampleName = RTrim(Mid(buffer, 23, 35))
      Case "Measurement"
        SampleDate = RTrim(Mid(buffer, 23, 21))
        
        
      Case "Application"
        ArchiveName = RTrim(Mid(buffer, 23, 15))
        
      Case "Fe"
        Fe = RTrim(Mid(buffer, 23, 5))
      Case "SiO2"
        SiO2 = RTrim(Mid(buffer, 23, 5))
      Case "AL2O3"
        Al2O3 = RTrim(Mid(buffer, 23, 5))
      Case "P "
        P = RTrim(Mid(buffer, 23, 5))
      Case "S "
        S = RTrim(Mid(buffer, 23, 5))
      Case "CaO"
        CaO = RTrim(Mid(buffer, 23, 5))
      Case "MnO"
        MnO = RTrim(Mid(buffer, 23, 5))
      Case "K2O"
        K2O = RTrim(Mid(buffer, 23, 5))
      Case "TiO2"
        TiO2 = RTrim(Mid(buffer, 23, 5))
      Case "MgO"
        MgO = RTrim(Mid(buffer, 23, 5))
        
      
        Set rst = dbs.OpenRecordset("XRF Results")
        rst.AddNew
        ResultID = rst!ResultID ' <-- Save the AutoNumber, we'll need it
        rst!SampleName = SampleName
        rst!SampleDate = SampleDate
        rst!ArchiveName = ArchiveName
        rst!Fe = Fe
        rst!SiO2 = SiO2
        rst!Al2O3 = Al2O3
        rst!P = P
        rst!S = S
        rst!CaO = CaO
        rst!MnO = MnO
        rst!K2O = K2O
        rst!TiO2 = TiO2
        rst!MgO = MgO
        
        rst.Update
        rst.Close
      
      End Select
   Line Input #1, buffer
   
   Wend
Close #1                                        'Close the text file.
DoCmd.SetWarnings True                  'Set Access warnings back on again.

End Sub

The source file is attached. It doesn't work, anyone know why? Doesn't give any errors when run, just doesn't copy the data. Tried it with the error turned on, no help. Any help you can provide would be greatly appreciated!

 

[WayneRyan]

Spectro,

Just a few quick thoughts.

Your line of code --> Select Case Mid(buffer, 1, 1)

Then you have Case "Sample", "Measurement", etc.
Those are not ONE-character fields.

Secondly, you are trying to write a table entry for EACH line that
you read. Your Insert is probably failing because you have some
zero length fields.

You should only try to do the Insert when you have finished reading
an entire sample ... Is it always the "MgO..." line?

Wayne

 

[spectrolab]

Thanks for the reply Wayne, yep, I wasn't to sure about the select case argument, I am still learning VB, from the file, what should I set it as?

The file will always have the same format, with MgO being the last compond measured. I think our file varies from Joe's in that it doesn't change at all, apart from the values for each element/compound, sample name, date and maybe sample archive.

I think it is quite obvious that I don't know what I am doing in this stuff, but I keep pushing ahead anyhow!

 

[JoeCruse]

Spectrolab,

one thing I ran into with this is that this code saw the VERY LAST ELEMENT REPORTED in the text file like the end of the file, so it was never included with the rest of the data. I didn't know what was going on with the code to make it act that way, so I changed the SuperQ text file. On ALL of my SuperQ analytical programs, I added a "YY" channel at the end that simply reported a zero numerical value. It is now the last line in the text file, and gets treated as the end-of-file, so is not imported into our d'base.

One other thing; We also used to keep our spectrometer data, both sample ID info AND analytical results in one table. This really does violate the rules on table normalization, so I split the table into 2 tables. I, too, had over 100,000 records, but I ended up seeing no letup in performance by splitting the tables into 2 related tables.

 

[WayneRyan]

Joe,

If you can post a larger sample of your data, AND a description of your tables,
I'll have a look and give you some ideas.

Wayne

 

[JoeCruse]

Joe,

If you can post a larger sample of your data, AND a description of your tables,
I'll have a look and give you some ideas.

Wayne

Wayne, thanks for the offer, but no worries for us.

Spectrolab, how are you making out with this issue?

 

[SIMinerals]

I know this is very old but I am interested in seeing how this was completed. I find myself in need of learning VB for a project that actually is for LIMS.
Any info will be helpful.