how to import complex text file into ms access

[msaccess4me]

I only need "sample, additional info, reference, analyte, % and Scaling Ref."

see below for example of the text file,
-----------------------------------------------

Method: 1 Metal Mode Sample: A46731 8/1/13 6:57:04 PM
Additional info: C4027
Duration: 5
Good Match
Reference: Inco 718
Difference: 23.60
Screening Method:Ni Chem 1
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 0.89 18.68 0.07 18.37 0.33 48.06 0.00 >5.38 3.03 0.45
STD 0.17 0.32 0.14 0.35 0.13 0.37 0.093 0.07 0.079 0.12
Grades: Inconel 718
Scaling Ref. : Inconel 718
Scaling Method: Ni ID

Method: 1 Metal Mode Sample: A46731 8/1/13 6:57:16 PM
Additional info: C4027
Duration: 5
Good Match
Reference: Inco 718
Difference: 29.55
Screening Method:Ni Chem 1
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 1.18 18.43 <0.04 18.90 0.18 47.89 0.00 >5.48 3.13 0.31
STD 0.15 0.28 0.12 0.31 0.11 0.32 0.074 0.06 0.069 0.093
Grades: Inconel 718
Scaling Ref. : Inconel 718
Scaling Method: Ni ID

Method: 1 Metal Mode Sample: A46731 8/1/13 6:57:28 PM
Additional info: C4027
Duration: 5
Good Match
Reference: Inco 718
Difference: 33.03
Screening Method:Ni Chem 1
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 1.13 19.02 0.00 18.44 0.29 48.44 0.00 >5.34 2.89 0.42
STD 0.15 0.28 0.11 0.3 0.11 0.32 0.07 0.059 0.066 0.096
Grades: Inconel 718
Scaling Ref. : Inconel 718
Scaling Method: Ni ID

Method: 1 Metal Mode Sample: A46731 8/1/13 6:57:41 PM
Additional info: C4027
Duration: 5
Good Match
Reference: Inco 718
Difference: 27.10
Screening Method:Ni Chem 1
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 1.08 18.26 0.08 18.54 0.32 48.71 0.00 >5.37 2.88 0.34
STD 0.14 0.26 0.11 0.28 0.1 0.3 0.072 0.056 0.061 0.086
Grades: Inconel 718
Scaling Ref. : Inconel 718
Scaling Method: Ni ID

-----------------------------------------------------
thanks for your help

[zmbd]

Is the format set?
Are these all in one file or are these examples in seperate files?

We are not supposed to write the code for you; however, some ideas to get you started:

I'd be looking at the standard IO commands
Some would use the File Scripting Object
ReadLN()
SPLIT()
Test result in element(0) for Method, Additional, etc and select case for action based on that return to process the correct element for the table
if not eof then loop/

[ADezii]

Extracting this Data could be as simple as a series of If...ElseIf...End If Statements as indicated below:

Dim strLine As String
Dim intLineNum As Integer
 
Open "C:\Security\Test.txt" For Input As #1
 
Do While Not EOF(1)
  Line Input #1, strLine    'Read line into variable.
    If InStr(strLine, "Sample:") > 0 Then
      Debug.Print Mid$(strLine, InStr(strLine, "Sample:") + 8)
    ElseIf InStr(strLine, "Additional Info:") > 0 Then
      Debug.Print Mid$(strLine, InStr(strLine, "Additional Info:") + 17)
    ElseIf InStr(strLine, "Reference:") > 0 Then
      Debug.Print Mid$(strLine, InStr(strLine, "Reference:") + 11)
    ElseIf InStr(strLine, "Analyte") > 0 Then
      Debug.Print Mid$(strLine, InStr(strLine, "Analyte") + 8)
    ElseIf Left$(strLine, 1) = "%" Then
      Debug.Print Mid$(strLine, 3)
    ElseIf InStr(strLine, "Scaling Ref. :") > 0 Then
      Debug.Print Mid$(strLine, InStr(strLine, "Scaling Ref. :") + 15)
    End If
Loop
 
Close #1

OUTCOME:

A46731 8/1/13 6:57:04 PM
C4027
Inco 718
Ti Cr Mn Fe Co Ni Cu Nb Mo W
0.89 18.68 0.07 18.37 0.33 48.06 0.00 >5.38 3.03 0.45
Inconel 718
A46731 8/1/13 6:57:16 PM
C4027
Inco 718
Ti Cr Mn Fe Co Ni Cu Nb Mo W
1.18 18.43 <0.04 18.90 0.18 47.89 0.00 >5.48 3.13 0.31
Inconel 718
A46731 8/1/13 6:57:28 PM
C4027
Inco 718
Ti Cr Mn Fe Co Ni Cu Nb Mo W
1.13 19.02 0.00 18.44 0.29 48.44 0.00 >5.34 2.89 0.42
Inconel 718
A46731 8/1/13 6:57:41 PM
C4027
Inco 718
Ti Cr Mn Fe Co Ni Cu Nb Mo W
1.08 18.26 0.08 18.54 0.32 48.71 0.00 >5.37 2.88 0.34
Inconel 718

Now, what do you wish to do with this extracted Data.

[msaccess4me]

thank you ADezii, I will like to import these extracted data in to a ms access table with field names as below:
-----------------------------------------
Sample
Date
AdditionalInfo
Reference
Ti
Cr
Mn
Fe
Co
Ni
Cu
Nb
Mo
W
ScalingRef
-------------------------------------------
Is anyway to do it?

[zmbd]

Read the values in to the variables as either ADezii or myself have indicated.

If the table exsists then a simple insert sql will work.

So I've used your field names for the table
and prefaced each with a "z" for the variables

Thus if the sample is A46731
then set the varible zSample = A46731

zsql = "INSERT INTO tbl_schedule" & _
       "(Sample,AdditionalInfo,Reference,Ti,Cr," & _
       "Mn,Fe,Co,Ni,Cu,Nb,Mo,W,ScalingRef)" & _
       " VALUES" & _
       " (" & zSample & "," & zAdditionalInfo & _
       "," & zReference & "," & zTi & "," & zCr & _
       "," & zMn & "," & zFe & "," & zCo & _
       "," & zNi & "," & zCu & "," & zNb & _
       "," & zMo & "," & zW & "," & zScalingRef & ")"
zdb.Execute zsql, dbFailOnError

So open the file, parse the text using either ADezii or my method, then insert the records. If the table exsists then the simple insert sql will work.
NOTE: I build the string first this way, I can do a debug.print to see the resolved string as spacing and all of that is importaint.

Also, This may not really be the most normalized method to import your sample data.

[ADezii]

I like dealing with Recordsets so I took a different approach from zmbd. Here is what I cam up with along withe the Results, but I am a little confused on the [Analyte] Field. Any questions, feel free to ask.

Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
 
Open "C:\Test\Test.txt" For Input As #1
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
 
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError     'Clear tblResults
 
With rst
  .AddNew       'for 1st Block of Data only
    Do While Not EOF(1)
      Line Input #1, strLine            'Read line into variable.
      intLineNum = intLineNum + 1       'Line Counter
        If InStr(strLine, "Sample:") > 0 Then
          ![sample] = Mid$(strLine, InStr(strLine, "Sample:") + 8)
        ElseIf InStr(strLine, "Additional Info:") > 0 Then
          ![AdditionalInfo] = Mid$(strLine, InStr(strLine, "Additional Info:") + 17)
        ElseIf InStr(strLine, "Reference:") > 0 Then
          ![Reference] = Mid$(strLine, InStr(strLine, "Reference:") + 11)
        ElseIf InStr(strLine, "Analyte") > 0 Then
          'Debug.Print Mid$(strLine, InStr(strLine, "Analyte") + 8)
        ElseIf Left$(strLine, 1) = "%" Then
          ![Percent] = Mid$(strLine, 3)
        ElseIf InStr(strLine, "Scaling Ref. :") > 0 Then
          ![ScalingRef] = Mid$(strLine, InStr(strLine, "Scaling Ref. :") + 15)
        ElseIf intLineNum Mod 14 = 0 Then
          intLineNum = 0        'RESET Line Counter for next Block of Data
           ![Date] = Date
            .Update             'Record Separator, time to Save Record
            .AddNew             'Return to ADD Mode for next Block
    End If
Loop
End With
 
Close #1
rst.Close
Set rst = Nothing
 

tblResults:

Sample    Date    AdditionalInfo    Reference    Percent    ScalingRef
A46731 8/1/13 6:57:04 PM    12/17/2013    C4027    Inco 718    0.89 18.68 0.07 18.37 0.33 48.06 0.00 >5.38 3.03 0.45    Inconel 718
A46731 8/1/13 6:57:16 PM    12/17/2013    C4027    Inco 718    1.18 18.43 <0.04 18.90 0.18 47.89 0.00 >5.48 3.13 0.31    Inconel 718
A46731 8/1/13 6:57:28 PM    12/17/2013    C4027    Inco 718    1.13 19.02 0.00 18.44 0.29 48.44 0.00 >5.34 2.89 0.42    Inconel 718
A46731 8/1/13 6:57:41 PM    12/17/2013    C4027    Inco 718    1.08 18.26 0.08 18.54 0.32 48.71 0.00 >5.37 2.88 0.34    Inconel 718

P.S. - Kindly forgive my lack of proper Formatting on tblResults, heading out the door!

[zmbd]

ADezii,
I'll go out on the limb here being the Chemist, I recognize this type of output:

I am assuming that the results are mass-mass percentage:
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 1.18 18.43 <0.04 18.90 0.18 47.89 0.00 >5.48 3.13 0.31

Ti = Titanium = 1.18%(m/m)
Cr = Chromium = 18.43%(m/m)
(...)
W = Tungsten = 0.31%(m/m)

The way I would parse this:
(Table
[fields]

example based on entry one post 1)
->

tbl_samplelog
[samplelog_pk]autonumber
[samplelog_name]text(40)
[samplelog_day] date/time
[samplelog_AdditionalInfo]text(40)
[samplelog_Reference]text(40) << This might be another table
[samplelog_ScalingRef]text(40) << This might be another table

[1][A46731][8/1/13 6:57:04 PM][C4027][Inco 718][Inconel 718]

tbl_sampleconsituents
[sampleconsituents_pk]autonumber
[sampleconsituents_name]text(40)
[sampleconsituents_LDL]text(10)
[sampleconsituents_UDL]text(10)

[1][Ti][<1.0][>100]
[2][Cr][<1.0][>100]
[3][Mn][<0.04][>100]
(...)
[8][Nb][<1.0][>5.38]
(...)
[10][W][<1.0][>100]

tbl_analyteresults
[analyteresults_pk]autonumber
[analyteresults_fk_samplelog]numeric(long)
[analyteresults_fk_sampleconsituents]numeric(long)
[analyteresults_result]numeric(double)

[1][1][1][0.89]
[1][1][2][18.68]
[1][1][3][-1]
(...)
[1][1][8][101]
(...)
[1][1][10][0.45]

Reports would be set so that -1 and 101 which are outside of the percent range would the reference the LDL (lower detectio limit) or the UDL (upper detection limit) for the return value; thus, keeping [analyteresults_result]numeric(double) - However, there appears to be an inconsistancy with UDL on say Nb... so this is either part of the scaling or reference material and hence my thought above that there may be a new table or two needed.

Therefore I'd more than likely open a recordset on each of the tables.
Open the file for read, do while not eof
Read my line in (lineinput()), SPLIT([on spaces]) pull the
so line 1 yields the following array:
[Method:][1][Metal][Mode][Sample:][A46731][8/1/13][6:57:04][PM]

Select case on element[0]="Method:"
zsample=element[5]
zdate= "#" & element[5] & " " & element[6] & " " & element[6] & "#"

DAO add this to tbl_samplelog return bookmark to last modified and then return [samplelog_pk] for use later

Parse each string,
for example "Analyte" the select case might start a second loop to handle the consituents. Perhaps, Pulling Find first on the text to return [sampleconsituents_pk], then use [samplelog_pk]; however, if the results ALWAYS include ALL of the analyte results then, in this case, I think I'd use the insert sql, it's cleaner, drop the "%" concatenate the elements, then read the next line, would have to eval each array element for "</>" and alter as needed...

might need either "Scaling Method" or "Method:" to flag for new sample... would have to N/S this to make sure the logic is stable.

[msaccess4me]

zmbd is right, the "analyte" and "%" are the percentage of each element. thanks to ADezii, I got everything else working except the analyte results. I will like to ignore the "<" and ">" in the results, so I need create another table just for analyte results, and with the autonumbers I can link the the results back with the log information right?
so I can use split? select case? array? I am new at vba coding, could you show me how it get done to import the results in a table like below
table II
-------------------------------------------------------
AutoNumber 1 2 3
Ti 0.89 1.18 1.13
Cr 18.68 18.43 19.02
Mn 0.07 0.04 0.00
Fe 18.37 18.90 18.44
Co 0.33 0.18 0.29
Ni 48.06 47.89 48.44
Cu 0.00 0.00 0.00
Nb 5.38 5.48 5.34
Mo 3.03 3.13 2.89
W 0.45 0.31 0.42
--------------------------------------------------
thank you

[zmbd]

Would you please post the code you have so far so that we can make suggestions that integrate with what you already have in place.

Also, the way you have the table:
analyte, result 1, result 2, reasult 3

is not normalized and will be very difficult to relate back to samples A46731 8/1/13 6:57:04 PM; A46731 8/1/13 6:57:16 PM; etc...

[ADezii]

@zmbd:
Thanks for the Chemistry Lesson.
@msaccess4me:
Everything can be neatly contained within a single Table as I feel it should be. The Code below will do just that, assuming Test.txt is in the same Folder as the Database within which the Code is being executed. I am a firm believer in 'Seeing is believing' so in addition to Posting the Code, I am attaching the Demo Database that I used for this Thread. Copy both Files in the *.Zip to any Folder, they must reside in the same Folder. Open the Database and the rest will be quite obvious. If this is not what you are looking for, no harm done. Good luck and let us know how you make out.

Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim varSplit As Variant
 
Open CurrentProject.Path & "\Test.txt" For Input As #1
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
 
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError     'Clear tblResults
 
With rst
  .AddNew       'for 1st Block of Data only
    Do While Not EOF(1)
      Line Input #1, strLine            'Read line into variable.
      intLineNum = intLineNum + 1       'Line Counter
        If InStr(strLine, "Sample:") > 0 Then
          ![sample] = Mid$(strLine, InStr(strLine, "Sample:") + 8)
        ElseIf InStr(strLine, "Additional Info:") > 0 Then
          ![AdditionalInfo] = Mid$(strLine, InStr(strLine, "Additional Info:") + 17)
        ElseIf InStr(strLine, "Reference:") > 0 Then
          ![Reference] = Mid$(strLine, InStr(strLine, "Reference:") + 11)
        ElseIf InStr(strLine, "Analyte") > 0 Then
          'Debug.Print Mid$(strLine, InStr(strLine, "Analyte") + 8)
        ElseIf Left$(strLine, 1) = "%" Then
          varSplit = Split(strLine)
            ![Ti] = varSplit(1)
            ![Cr] = varSplit(2)
            ![Mn] = varSplit(3)
            ![Fe] = varSplit(4)
            ![Co] = varSplit(5)
            ![Ni] = varSplit(6)
            ![Cu] = varSplit(7)
            ![Nb] = varSplit(8)
            ![Mo] = varSplit(9)
            ![W] = varSplit(10)
          '![Percent] = Mid$(strLine, 3)
        ElseIf InStr(strLine, "Scaling Ref. :") > 0 Then
          ![ScalingRef] = Mid$(strLine, InStr(strLine, "Scaling Ref. :") + 15)
        ElseIf intLineNum Mod 14 = 0 Then
          intLineNum = 0        'RESET Line Counter for next Block of Data
           ![Date] = Date
            .Update             'Record Separator, time to Save Record
            .AddNew             'Return to ADD Mode for next Block
    End If
Loop
End With
 
Close #1
rst.Close
Set rst = Nothing
 
With DoCmd
  .OpenTable "tblResults", acViewNormal, acReadOnly
  .Maximize
End With
 
Exit_cmdTest_Click:
  Exit Sub
 
Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    Resume Exit_cmdTest_Click
 

[msaccess4me]

thank you ADezii, you code works very well. I made a few small adjustments, because the Analyte elements maybe different base on what kind material we are using, but I was having hard time to carry over the value (varSplitA) I want to link with next line. Here is what I have so far

Function InputRawDate1()
Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim varSplitA As Variant
Dim varSplit As Variant
Dim strReplacedPercentage As String
Dim strReplacedAnalyte As String
 
 
 
Open CurrentProject.Path & "\input.txt" For Input As #1
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblRawMaterialData", dbOpenDynaset)
 
CurrentDb.Execute "DELETE * FROM tblRawMaterialData", dbFailOnError     'Clear tblResults
 
With rst
  .AddNew       'for 1st Block of Data only
    Do While Not EOF(1)
      Line Input #1, strLine            'Read line into variable.
      intLineNum = intLineNum + 1       'Line Counter
        If InStr(strLine, "Sample:") > 0 Then
          ![sample] = Mid$(strLine, InStr(strLine, "Sample:") + 9, 6)
          ![fldDate] = Mid$(strLine, InStr(strLine, "Sample:") + 16)
        ElseIf InStr(strLine, "Additional Info:") > 0 Then
          ![AdditionalInfo] = Mid$(strLine, InStr(strLine, "Additional Info:") + 17)
        ElseIf InStr(strLine, "Reference:") > 0 Then
          ![Reference] = Mid$(strLine, InStr(strLine, "Reference:") + 11)
        ElseIf InStr(strLine, "Analyte") > 0 Then
        strReplacedAnalyte = Trim(Replace(strLine, vbTab, " "))
        varSplitA = Split(Mid$(strReplacedAnalyte, InStr(strReplacedAnalyte, "Analyte") + 8), " ")
          'Debug.Print Mid$(strLine, InStr(strLine, "Analyte") + 8)
        ElseIf Left$(strLine, 1) = "%" Then
         strReplacedPercentage = Trim(Replace(strLine, vbTab, " "))
          'varSplit = Split(strLine)
          varSplit = Split(Mid$(strReplacedPercentage, InStr(strReplacedPercentage, "%") + 8), " ")
            ![varSplitA(1)] = varSplit(1)
            ![varSplitA(2)] = varSplit(2)
            ![varSplitA(3)] = varSplit(3)
            ![varSplitA(4)] = varSplit(4)
            ![varSplitA(5)] = varSplit(5)
            ![varSplitA(6)] = varSplit(6)
            ![varSplitA(7)] = varSplit(7)
            ![varSplitA(8)] = varSplit(8)
            ![varSplitA(9)] = varSplit(9)
            ![varSplitA(10)] = varSplit(10)
          '![Percent] = Mid$(strLine, 3)
        ElseIf InStr(strLine, "Scaling Ref. :") > 0 Then
          ![ScalingRef] = Mid$(strLine, InStr(strLine, "Scaling Ref. :") + 15)
        ElseIf intLineNum Mod 14 = 0 Then
          intLineNum = 0        'RESET Line Counter for next Block of Data
           '![Date] = Date
            .Update             'Record Separator, time to Save Record
            .AddNew             'Return to ADD Mode for next Block
    End If
Loop
End With
 
Close #1
rst.Close
Set rst = Nothing
 
With DoCmd
  .OpenTable "tblRawMaterialData", acViewNormal, acReadOnly
  .Maximize
End With
 
'Exit_cmdTest_Click:
  'Exit Sub
 
'Err_cmdTest_Click:
  'MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    'Resume Exit_cmdTest_Click
 
End Function

I attached a sample txt file, so you can see better. thanks

[zmbd]

I made a few small adjustments, because the Analyte elements maybe different base on what kind material we

Hence the method I suggested with the multiple tables... it is the normalized approach.
If you are interested, I may have time to bash something togeither based upon what I have in the lab.

[ADezii]

You can populate the Analyte Elements and their corresponding Percentages into Parallel Arrays where each Element in the Analyte Array can represent a Field in tblResults. You can then set the Value of this Field in a Recordset to its corresponding Percentage, as the Debug Statement will show. These Code Segments should illustrate these points.

Dim varAnalyte As Variant
Dim varValue As Variant
Dim intCtr
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
 
varAnalyte = Array("Ti", "Cr", "Mn", "Fe", "Co", "Ni", "Cu", "Nb", "Mo", "W")
varValue = Array(0.89, 18.68, 0.07, 18.37, 0.33, 48.06, 0#, 5.38, 3.03, 0.45)
 
rst.AddNew
For intCtr = LBound(varAnalyte) To UBound(varAnalyte)
  Debug.Print varAnalyte(intCtr) & " <==> " & varValue(intCtr)
  'Update the corresponding Field in tblResults("Ti","Cr","Mn","Fe","Co","Ni","Cu","Nb","Mo","W")
  rst.Fields(varAnalyte(intCtr)) = varValue(intCtr)
Next
rst.Update
 
rst.Close
Set rst = Nothing
 

Debug.Print results:

Ti <==> 0.89
Cr <==> 18.68
Mn <==> 0.07
Fe <==> 18.37
Co <==> 0.33
Ni <==> 48.06
Cu <==> 0
Nb <==> 5.38
Mo <==> 3.03
W <==> 0.45
 

[ADezii]

@msaccess4me:
I do believe that I have arrived at a viable solution to this interesting Thread. Again, simply extract the Revised Database and Input.txt into the 'same' Folder then Open the Database. Before I put any more time into this, I want to make absolutely sure that this is what you are looking for, or at least close to it.

P.S. - Should you wish to use the Parent ==> Child Table approach, it will be a simple matter to do so since I incorporated an AutoNumber/Primary Key into the Results Table (tblResults). Let us know how you make out.

[msaccess4me]

@ADezii: Thank you so much for helping me out. your code works very well.
also, @zmbd:thanks for your help too.
I works with MS Access a lot, but not so much VBA coding. I really like to learn more of it. Do you guys know any good way or good website to help me to understand it better.
p.s. Holidays are coming soon, Happy Holidays to you all :)