Serious Problem with Data Import (1 Viewer)

[spectrolab]

Hi Guys,

I have some Vba (Made with the help of Wayne Ryan and Joe Cruse, thanks guys) that extracts data from a text file. I had to make a few changes to the text file and I thought I did the same witht the vba, but no luck.

Private Sub Form_Timer() 'set timer to x milliseconds accordingly


 Dim fs As New Scripting.FileSystemObject
 Dim f As File
 Dim fldr As Folder
    
 Set fldr = fs.GetFolder("Y:\Results Axios")
If fldr.Files.Count > 0 Then
  For Each f In fldr.Files
          Name f As "Y:\Results Axios\SuperQ.txt"
      Next f
        
 End If
   Set f = Nothing
    


'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 "new 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.
 
 
    
 If fldr.Files.Count > 0 Then

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 ResultDateTime As Date       'Date/Time value of the converted string for date of analysis
Dim MeasureOriginName As String  'Analytical program used to analyze sample in SuperQ
Dim Fe As Double                 'Result concentration, in %, from the analysis
Dim Fe2O3 As Double
Dim SiO2 As Double                 'Result concentration, in %, from the analysis
Dim CaO As Double                 'Result concentration, in %, from the analysis
Dim MnO As Double                 'Result concentration, in %, from the analysis
Dim Al2O3 As Double                 'Result concentration, in %, from the analysis
Dim TiO2 As Double                 'Result concentration, in %, from the analysis
Dim MgO As Double                 'Result concentration, in %, from the analysis
Dim P2O5 As Double                 'Result concentration, in %, from the analysis
Dim SO3 As Double                 'Result concentration, in %, from the analysis
Dim K2O As Double                 'Result concentration, in %, from the analysis
Dim V2O5 As Double                 'Result concentration, in %, from the analysis
Dim Cr2O3 As Double                 'Result concentration, in %, from the analysis
Dim CoO As Double                 'Result concentration, in %, from the analysis
Dim NiO As Double                 'Result concentration, in %, from the analysis
Dim CuO As Double                 'Result concentration, in %, from the analysis
Dim ZnO As Double                 'Result concentration, in %, from the analysis
Dim As2O3 As Double                 'Result concentration, in %, from the analysis
Dim PbO As Double                 'Result concentration, in %, from the analysis
Dim BaO As Double                'Result concentration, in %, from the analysis
Dim Na2O As Double
Dim Cl As Double
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("tblXRFResults")   'Set database table
DoCmd.SetWarnings False              'Turn off warning so that an Access message box does not appear
                                             'for each record appended to the "tblXRFResultsConcentration" table


Open "Y:\Results Axios\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, 3)
      Case "Sam"
        SampleName = RTrim(Mid(buffer, 24, 25))
      
        
      Case "App"
        MeasureOriginName = RTrim(Mid(buffer, 24, 20))
        Set rst = dbs.OpenRecordset("tblXRFResults")
        rst.AddNew
        rst!SampleName = SampleName
        rst!ResultDate = Date
        rst!Time = Time()
        rst!MeasureOriginName = MeasureOriginName
        rst.Update
        rst.Close
        Set rst = dbs.OpenRecordset("tblXRFResults")
        rst.MoveLast
        ResultID = rst!ResultID
        rst.Close
        
      Case "Fe "
        Fe = RTrim(Mid(buffer, 17, 9))
      Case "Fe2"
        Fe2O3 = RTrim(Mid(buffer, 17, 9))
      Case "SiO"
        SiO2 = RTrim(Mid(buffer, 17, 9))
      Case "CaO"
        CaO = RTrim(Mid(buffer, 17, 9))
      Case "Al2"
        Al2O3 = RTrim(Mid(buffer, 17, 9))
      Case "MnO"
        MnO = RTrim(Mid(buffer, 17, 9))
      Case "TiO"
        TiO2 = RTrim(Mid(buffer, 17, 9))
      Case "MgO"
        MgO = RTrim(Mid(buffer, 17, 9))
      Case "P2O"
        P2O5 = RTrim(Mid(buffer, 17, 9))
      Case "SO3"
        SO3 = RTrim(Mid(buffer, 17, 9))
      Case "K2O"
        K2O = RTrim(Mid(buffer, 17, 9))
      Case "V2"
        V2O5 = RTrim(Mid(buffer, 17, 9))
      Case "Cr"
        Cr2O3 = RTrim(Mid(buffer, 17, 9))
      Case "Co"
        CoO = RTrim(Mid(buffer, 17, 9))
      Case "Ni"
        NiO = RTrim(Mid(buffer, 17, 9))
      Case "CuO"
        CuO = RTrim(Mid(buffer, 17, 9))
      Case "ZnO"
        ZnO = RTrim(Mid(buffer, 17, 9))
      Case "As2"
        As2O3 = RTrim(Mid(buffer, 17, 9))
      Case "Pb"
        PbO = RTrim(Mid(buffer, 17, 9))
      Case "Ba"
        BaO = RTrim(Mid(buffer, 17, 9))
        Case "Na2"
        Na2O = RTrim(Mid(buffer, 17, 9))
        Case "Cl "
        Cl = RTrim(Mid(buffer, 17, 9))
        
        
        sql = "INSERT INTO [tblXRFResultsConc] (ResultID, Fe2O3, SiO2, CaO, MnO, Al2O3, TiO2, MgO, P2O5, SO3, K2O, V2O5, Cr2O3, CoO, NiO, CuO, ZnO, As2O3, PbO, BaO, Na2O, Cl)" & _
                     "Values(" & ResultID & ", " & _
                                 Fe2O3 & ", " & _
                                 SiO2 & ", " & _
                                 CaO & ", " & _
                                 MnO & ", " & _
                                 Al2O3 & ", " & _
                                 TiO2 & ", " & _
                                 MgO & ", " & _
                                 P2O5 & ", " & _
                                 SO3 & ", " & _
                                 K2O & ", " & _
                                 V2O5 & ", " & _
                                 Cr2O3 & ", " & _
                                 CoO & ", " & _
                                 NiO & ", " & _
                                 CuO & ", " & _
                                 ZnO & ", " & _
                                 As2O3 & ", " & _
                                 PbO & ", " & _
                                 BaO & ", " & _
                                 Na2O & ", " & _
                                 Cl & ");"
                                 
         
        DoCmd.RunSQL sql
        
      End Select
   Line Input #1, buffer
   
   Wend
Close #1                                        'Close the text file.

Dim fso, f1, S

  Set fso = CreateObject("Scripting.FileSystemObject")

  Set f1 = fso.GetFile("Y:\Results Axios\SuperQ.txt")
   
   f1.Delete
   
  End If

   Set fldr = Nothing
    Set fs = Nothing
    Set rst = Nothing

The first part of the code still works fine, it gets the sample name and measure origin and appends it to the table just fine, but when it tries to grab the concentrations (case "Fe " etc) there is no error, but the records are not added to the table. I thought it might have something to do with the rtrim size, but I'm not sure. It worked fine before, but since I changed the file slightly, it doesn't any longer

The file it gets the data from is attached and the format does not change, but I am not seeing why it is happening.

Many thnaks for any help you can give.

 

[nIGHTmAYOR]

honey neither i read your post nore i am by any chance interested in debuging above code not even for money for the fact that i'm sure it must contain heaps of unoptimized syntax to reach above sum.

but i came across the line you mentioned that your problem lays in case "fe " so i brawsed up and noticed something , you use all Mid locations to be the same (character location 17 till 9 charackters later!) now that could work .. but where is the part of the code that the line curser moves?

Your entire script assumes that all happens line 1 so you either move the curser or calculate the exact character location which is not exactly character 17 for every molicule or whatever.

 

[spectrolab]

Thanks for that, not really much help though.

The select case, if you had bothered to have a look at the file, selects the particular data line required, i.e. the "Fe " case has the data at 17th position and is less than 9 digits long.

As I metioned previously, the code worked perfectly fine before, but now, just changing the layout of the data file causes it to fail.

If anyone else could help it would be much appreciated.

 

[WayneRyan]

Spectro,

Hello again.

The code writes a row to the many side table whenever it encounters a "cl "
in columns 1-3.

If it's not writing, it must not find that. All I could figure is that:

You have --> cl<Tab>

You have only one many-side entry and the line with the "cl " has no line
terminator. You're reaching EOF too early!

That's my only ideas. Have you tried the debugger. This situation is made
for it. Post back if you need tips with it.

Wayne

 

[spectrolab]

Thanks a lot wayne,

I thought it might be something to do with that.

The debugger works ok, but when it gets to the Case where say SiO2 needs to be selected, the buffer still says

"Measurement time 14/10/2008 10:45:18 PM"

It is like it gets hung up on this line and cant go any further, any ideas what might be causing this? I tried using a Select Case width of 2 and modified the file (removed the Fe line so there wasn't a duplicate for Fe and Fe2O3) but still no luck.

It is like it isnt reading further in the file than that line and all the elements are zero.

 

[WayneRyan]

Spectro,

I'd love to spend five minutes on this with the debugger.

Can you post a DB with EMPTY tables?

Also, is that the REAL file used for input?

Wayne

 

[spectrolab]

Thanks Wayne,

I managed to solve it through the debugger and modifying the file to remove the Fe line, doesn't really need it. The code stopped when it met a line that wasn't in the select case, in the case "measurement time" I added this to the table as well as selecting every line in order and trimming the buffer width to 2. I also had the same issue as joe as it was seeing the Cl line as the final line, but not grabbibg the data, I added another line to the output fil and it works.

I can post the code if you would like to have a look.

 

[WayneRyan]

Spectro,

No, as long as you're OK.

I think that the last line of your input file was returning the EOF indicator.
As long as you can compensate for it ... OK.
Isn't it wonderful to work with external text files.

Wayne