how to import complex text file into ms access

**zmbd** · Dec 16 '13, 11:22 PM

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** · Dec 17 '13, 02:27 AM

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

Code:

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:

Code:

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** · Dec 17 '13, 05:19 PM

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** · Dec 17 '13, 05:38 PM

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

Code:

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** · Dec 17 '13, 07:10 PM

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.

Code:

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:

Code:

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** · Dec 17 '13, 09:40 PM

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_Addit ionalInfo]text(40)
[samplelog_Refer ence]text(40) << This might be another table
[samplelog_Scali ngRef]text(40) << This might be another table

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

tbl_sampleconsi tuents
[sampleconsituen ts_pk]autonumber
[sampleconsituen ts_name]text(40)
[sampleconsituen ts_LDL]text(10)
[sampleconsituen ts_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_analyteresu lts
[analyteresults_ pk]autonumber
[analyteresults_ fk_samplelog]numeric(long)
[analyteresults_ fk_sampleconsit uents]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 [sampleconsituen ts_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** · Dec 18 '13, 03:58 PM

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** · Dec 18 '13, 04:28 PM

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** · Dec 18 '13, 05:08 PM

@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.

Code:

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

Attached Files

Complex Text File.zip (27.7 KB, 243 views)

**msaccess4me** · Dec 18 '13, 11:35 PM

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

Code:

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

Attached Files

Input.txt (4.1 KB, 516 views)

**zmbd** · Dec 18 '13, 11:46 PM

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** · Dec 19 '13, 02:29 AM

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.

Code:

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:

Code:

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** · Dec 19 '13, 03:01 PM

@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.

Attached Files

Complex Text File 2.zip (44.4 KB, 271 views)

**msaccess4me** · Dec 19 '13, 09:42 PM

@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 :)

how to import complex text file into ms access

how to import complex text file into ms access

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment