473,320 Members | 1,825 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

non standard text import

I found the code which this was based on here on this site. It was created a few years back.
Expand|Select|Wrap|Line Numbers
  1. Dim dbs As DAO.Database          'Pull up database
  2. Dim rst As DAO.Recordset         'Pull up individual recordset for table
  3. Dim intLineNum As Integer
  4. Dim strLine As String
  5.  
  6.  
  7. 'i = FreeFile
  8. 'DoCmd.SetWarnings False              'Turn off warning so that an Access message box does not appear
  9.                                              'for each record appended to the "XRF Results Concentration" table
  10. Open "C:\Panalytical\SuperQ Format.txt" For Input As #1    'Open the text file made by SuperQ                                                                            'import the data
  11. Set dbs = CurrentDb
  12. Set rst = dbs.OpenRecordset("XRF Results")  'Set database table
  13.  
  14. With rst
  15.     .AddNew
  16.      Do While Not EOF(1)
  17.       Line Input #1, strLine
  18.         If InStr(strLine, "Sample Name") > 0 Then
  19.           ![SampleName] = LTrim(Mid(strLine, 23, 35))
  20.         ElseIf InStr(strLine, "Measurement time") > 0 Then
  21.           ![SampleDate] = LTrim(Mid(strLine, 23, 35))
  22.         ElseIf InStr(strLine, "Initial weight") > 0 Then
  23.           ![Initial_Weight] = LTrim(Mid(strLine, 23, 5))
  24.         ElseIf InStr(strLine, "Final weight") > 0 Then
  25.           ![Final_Weight] = LTrim(Mid(strLine, 23, 5))
  26.         ElseIf InStr(strLine, "LOI (balance)") > 0 Then
  27.           ![LOI] = LTrim(Mid(strLine, 23, 5))
  28.         ElseIf InStr(strLine, "TiO2") > 0 Then
  29.           ![TiO2] = LTrim(Mid(strLine, 18, 7))
  30.         ElseIf InStr(strLine, "Al2O3") > 0 Then
  31.           ![AI2O3] = LTrim(Mid(strLine, 18, 7))
  32.         ElseIf InStr(strLine, "Fe2O3") > 0 Then
  33.           ![Fe2O3] = LTrim(Mid(strLine, 18, 7))
  34.         ElseIf InStr(strLine, "ZrO2") > 0 Then
  35.           ![ZrO2] = LTrim(Mid(strLine, 18, 7))
  36.         ElseIf InStr(strLine, "Zn_ppm") > 0 Then
  37.           ![Zn_ppm] = LTrim(Mid(strLine, 18, 7))
  38.         ElseIf InStr(strLine, "U (ppm)") > 0 Then
  39.           ![U_ppm] = LTrim(Mid(strLine, 18, 7))
  40.         ElseIf InStr(strLine, "Th (ppm)") > 0 Then
  41.           ![Th_ppm] = LTrim(Mid(strLine, 18, 7))
  42.         ElseIf InStr(strLine, "SiO2") > 0 Then
  43.           ![SiO2] = LTrim(Mid(strLine, 18, 7))
  44.         ElseIf InStr(strLine, "MgO") > 0 Then
  45.           ![MgO] = LTrim(Mid(strLine, 18, 7))
  46.         ElseIf InStr(strLine, "CaO") > 0 Then
  47.           ![CaO] = LTrim(Mid(strLine, 18, 7))
  48.         ElseIf InStr(strLine, "P2O5") > 0 Then
  49.           ![P2O5] = LTrim(Mid(strLine, 18, 7))
  50.         ElseIf InStr(strLine, "V2O5") > 0 Then
  51.           ![V2O5] = LTrim(Mid(strLine, 18, 7))
  52.         ElseIf InStr(strLine, "Cr2O3") > 0 Then
  53.           ![Cr2O3] = LTrim(Mid(strLine, 18, 7))
  54.         ElseIf InStr(strLine, "HfO2") > 0 Then
  55.           ![HfO2] = LTrim(Mid(strLine, 18, 7))
  56.         ElseIf InStr(strLine, "MnO%") > 0 Then
  57.           ![MnO] = LTrim(Mid(strLine, 18, 7))
  58.         ElseIf InStr(strLine, "Nb2O5") > 0 Then
  59.          ![Nb2O5] = LTrim(Mid(strLine, 18, 7))
  60.         ElseIf InStr(strLine, "Y2O3") > 0 Then
  61.          ![Y2O3] = LTrim(Mid(strLine, 18, 7))
  62.         ElseIf InStr(strLine, "Na2O") > 0 Then
  63.          ![Na2O] = LTrim(Mid(strLine, 18, 7))
  64.         ElseIf InStr(strLine, "ThO2") > 0 Then
  65.          ![ThO2_ppm] = LTrim(Mid(strLine, 18, 7))
  66.         ElseIf InStr(strLine, "U3O8") > 0 Then
  67.          ![U3O8_ppm] = LTrim(Mid(strLine, 18, 7))
  68.         ElseIf InStr(strLine, "Pb_ppm") > 0 Then
  69.          ![Pb_ppm] = LTrim(Mid(strLine, 18, 7))
  70.         ElseIf InStr(strLine, "Mn3O4") > 0 Then
  71.          ![Mn3O4] = LTrim(Mid(strLine, 18, 7))
  72.         'ElseIf InStr(strLine, "TiO2 Alt") > 0 Then
  73.          '![TiO2_Alt] = LTrim(Mid(strLine, 18, 7))
  74.         'ElseIf InStr(strLine, "ZrO2 Alt") > 0 Then
  75.          '![ZrO2_Alt] = LTrim(Mid(strLine, 18, 7))
  76.         'ElseIf InStr(strLine, "TiO2 Alt HMC") > 0 Then
  77.          '![TiO2_Alt_HMC] = LTrim(Mid(strLine, 18, 7))
  78.         'ElseIf InStr(strLine, "ZrO2 Alt HMC") > 0 Then
  79.          '![ZrO2_Alt_HMC] = LTrim(Mid(strLine, 18, 7))
  80.          'intLine = 0
  81.           .Update
  82.           .AddNew
  83.       End If
  84.     Loop
  85.   End With
  86. Close #1
  87. rst.Close  'Close the text file.
  88. 'DoCmd.SetWarnings True                  'Set Access warnings back on again.
  89. Set rst = Nothing
  90.  
  91. End Sub
  92.  
The code works but I am getting some incorrect results into my table. One example is:
Expand|Select|Wrap|Line Numbers
  1. ElseIf InStr(strLine, "TiO2") > 0 Then
  2.           ![TiO2] = LTrim(Mid(strLine, 18, 7))
  3. ElseIf InStr(strLine, "TiO2 Alt") > 0 Then
  4.          ![TiO2_Alt] = LTrim(Mid(strLine, 18, 7))
  5.  
if i do not edit out the second set the program does not run.
Second example:
Expand|Select|Wrap|Line Numbers
  1.   ElseIf InStr(strLine, "ZrO2") > 0 Then
  2.           ![ZrO2] = LTrim(Mid(strLine, 18, 7))
  3.  
pulls the incorrect value for the "ZrO2) 62.01
this is the value for "ZrO2+HfO2 " listed in the text table.
any help or thoughts will be greatly appreciated.
I have been working in Access for a while but do to this project I am trying to learn VBA.
Thanks,
Attached Files
File Type: txt SuperQ Format.txt (1.4 KB, 388 views)
Jul 26 '16 #1

✓ answered by ADezii

  1. I was able to provide an alternative approach similar to what you want using only 30 Lines of Code (counting White Space). It relies on the Fixed Width Nature of the Text File, the Fields Collection of the Recordset Object, and the complete elimination of the If...ElseIf...Else...End If Construct. This is possible since the Field Names in XRF Results are synchronized with Details in the Header Section as well as the Chemical Names.
  2. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim strLine As String
    2. Dim intLineNum As Integer
    3. Dim MyDB As DAO.Database
    4. Dim rst As DAO.Recordset
    5.  
    6. Open CurrentProject.path & "\SuperQ Format.txt" For Input As #1
    7.  
    8. Set MyDB = CurrentDb
    9. Set rst = MyDB.OpenRecordset("XRF Results", dbOpenDynaset)
    10.  
    11. rst.AddNew
    12.   Do While Not EOF(1)
    13.     intLineNum = intLineNum + 1
    14.     Line Input #1, strLine: Debug.Print strLine
    15.      Select Case intLineNum
    16.        Case 1 To 3
    17.          rst.Fields(Trim(Left$(strLine, 22))) = Trim(Mid$(strLine, 23, 35))
    18.        Case 4 To 6
    19.          rst.Fields(Trim(Left$(strLine, 22))) = Trim(Mid$(strLine, 23, 5))
    20.        Case Else
    21.          rst.Fields(Trim(Left$(strLine, 17))) = Trim(Mid$(strLine, 18, 7))
    22.      End Select
    23.   Loop
    24.  
    25. rst.Update
    26.  
    27. Close #1
    28.  
    29. rst.Close
    30. Set rst = Nothing
    31.  
    32. 'Optional Code
    33. With DoCmd
    34.   .OpenTable "XRF Results", acViewNormal, acReadOnly
    35.   .Maximize
    36. End With
    37.  
  3. I tested the Code against the Header Information as well as the first 15 Chemicals in the Text File and it works very well.
  4. Download the Attachment and extract both Files to the 'same' Folder since both the DB and Text File need to reside in the same Folder as the CurrentProject.Path is utilized.
  5. This may/may not be a viable solution for you, only you can decide.
  6. Good Luck on your Project.

17 1738
zmbd
5,501 Expert Mod 4TB
FloridaBlueJay: if you would post the link to the original thread it might be helpful for context.
:)
Jul 26 '16 #2
Any suggestions? I am now at a point to change directions on this project. It must be something simple I am just overlooking.
Aug 12 '16 #4
PhilOfWalton
1,430 Expert 1GB
I can see the problem and obviously Instr(strLine, "TiO2") will produce the same value as InStr(strLine, "TiO2 Alt"). Both will detect the first occurrence.

Try
Expand|Select|Wrap|Line Numbers
  1. Instr(strLine, "TiO2     ") 
  2. and Instr(strLine, "TiO2 Alt    ") 
  3.  
Both have 4 spaces after them.
Much depends on the exact format of your text file, whether the spaces are really there between the chemical and it's weight??

Phil
Aug 12 '16 #5
ADezii
8,834 Expert 8TB
  1. The problem arises when certain Strings are contained within other Strings, in your case: TiO* and ZrO2*. I was able to circumvent this problem by first removing all the spaces between elements and analyzing each while keeping the original Line in tact for Output. The following Code, based on your Text File returns the proper Values for each Compound. It should be able to be simplified by removing the Replace() Function and replacing it with Left$() and possible LTrim().
    Expand|Select|Wrap|Line Numbers
    1. Dim strLine As String
    2.  
    3. Open "C:\Test\SuperQ Format.txt" For Input As #1
    4.  
    5. Do While Not EOF(1)
    6.   Line Input #1, strLine
    7.     If Left$(Replace(strLine, " ", ""), 4) = "TiO2" Then
    8.       If Left$(Replace(strLine, " ", ""), 7) = "TiO2Alt" Then
    9.         Debug.Print "TiO2 Alt" & " ==> " & LTrim(Mid(strLine, 18, 7))
    10.       Else
    11.         Debug.Print "TiO2" & " ==> " & LTrim(Mid(strLine, 18, 7))
    12.       End If
    13.     ElseIf Left$(Replace(strLine, " ", ""), 7) = "TiO2Alt" Then
    14.       Debug.Print "TiO2 Alt" & " ==> " & LTrim(Mid(strLine, 18, 7))
    15.     ElseIf Left$(Replace(strLine, " ", ""), 4) = "ZrO2" Then
    16.       If Left$(Replace(strLine, " ", ""), 9) = "ZrO2+HfO2" Then
    17.         Debug.Print "ZrO2+HfO2" & " ==> " & LTrim(Mid(strLine, 18, 7))
    18.       ElseIf Left$(Replace(strLine, " ", ""), 7) = "ZrO2Alt" Then
    19.         Debug.Print "ZrO2 Alt" & " ==> " & LTrim(Mid(strLine, 18, 7))
    20.       Else
    21.         Debug.Print "ZrO2" & " ==> " & LTrim(Mid(strLine, 18, 7))
    22.       End If
    23.     End If
    24. Loop
    25.  
    26. Close #1
  2. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. TiO2 ==> 0.35   
    2. ZrO2 ==> 60.82  
    3. ZrO2+HfO2 ==> 62.01  
    4. TiO2 Alt ==> 5.396  
    5. ZrO2 Alt ==> 65.862
Aug 13 '16 #6
PhilOfWalton
1,430 Expert 1GB
This may give you very much better results with very little hard coding.

First the table

Expand|Select|Wrap|Line Numbers
  1. XRF Results
  2.     ChemicalID       AutoNumber      PK
  3.     ChemicalID       Text
  4.     ChemicalValue    Double
  5.     Unit             Text
  6.     HeaderID         Long
  7.  
Then the code
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub AddResults()
  5.  
  6.     Dim dbs As DAO.Database          'Pull up database
  7.     Dim rst As DAO.Recordset         'Pull up individual recordset for table
  8.     Dim strLine As String
  9.     Dim Chemical As String
  10.     Dim ChemicalValue As String
  11.     Dim Unit As String
  12.     Dim i As Integer
  13.     Dim HeaderID As Long
  14.     Const HeaderLength = 23
  15.     Const ChemicalLength = 17
  16.  
  17.     'for each record appended to the "XRF Results Concentration" table
  18.     Open "E:\Downloads\SuperQ Format.txt" For Input As #1    'Open the text file made by SuperQ                                                                            'import the data
  19.     Set dbs = CurrentDb
  20.     Set rst = dbs.OpenRecordset("XRF Results")  'Set database table
  21.  
  22.     ' ###### Temporary Bodge ######
  23.     HeaderID = Nz(DMax("HeaderID", "XRF Results")) + 1
  24.     ' #############################
  25.  
  26.     Do While Not EOF(1)
  27.         Line Input #1, strLine
  28.         'strLine = Trim(strLine)
  29.         ' Ignore Headers
  30.         If Left(strLine, HeaderLength) = "Sample name            " Then
  31.             GoTo AddHeader
  32.         End If
  33.         If Left(strLine, HeaderLength) = "Application            " Then
  34.             GoTo AddHeader
  35.         End If
  36.         If Left(strLine, HeaderLength) = "Measurement time       " Then
  37.             GoTo AddHeader
  38.         End If
  39.         If Left(strLine, HeaderLength) = "Initial weight         " Then
  40.             GoTo AddHeader
  41.         End If
  42.         If Left(strLine, HeaderLength) = "Final weight           " Then
  43.             GoTo AddHeader
  44.         End If
  45.         If Left(strLine, HeaderLength) = "LOI (Balance)          " Then
  46.             GoTo AddHeader
  47.         End If
  48.  
  49.         Chemical = Trim(Left(strLine, ChemicalLength))
  50.         i = InStr(ChemicalLength + 1, strLine, " ")             ' First space after weight
  51.         ChemicalValue = CDec(Mid(strLine, ChemicalLength + 1, i - ChemicalLength))
  52.         i = InStrRev(strLine, " ")                              ' First space befor unit
  53.         Unit = Mid(strLine, i + 1)
  54.         Debug.Print "Chemical: " & Chemical & "   Value: " & ChemicalValue & "   Unit: " & Unit
  55.  
  56.         With rst
  57.             .AddNew
  58.             !Chemical = Nz(Chemical)
  59.             !ChemicalValue = Nz(ChemicalValue)
  60.             !Unit = Nz(Unit)
  61.             !HeaderID = HeaderID
  62.             .Update
  63.         End With
  64.  
  65. AddHeader:
  66.         ' ######### Might need to add a header table here #####
  67.  
  68.         Loop
  69.     Close #1
  70.     rst.Close                       'Close the text file.
  71.     Set rst = Nothing
  72.  
  73. End Sub
  74.  
Basically PROVIDING the format of your text file is constant then we start by ignoring the file headers (though I expect they will need to go in a header file. The HeaderID is there to provide that link, but you will need to create the header file first, and get the HeaderID from the TblHeaders rather than adding 1 to HeaderID in the XRF Results Table).

The first 17 letters are the chemical names, all have spaces after the actual letters.

Then we look for the next space after the 18th letter, the beginning of the quantity and that space is the end of the quantity That is then converted to Double.

The right hand bit of the string is the unit

Phil
Aug 13 '16 #7
ADezii
8,834 Expert 8TB
  1. I was able to provide an alternative approach similar to what you want using only 30 Lines of Code (counting White Space). It relies on the Fixed Width Nature of the Text File, the Fields Collection of the Recordset Object, and the complete elimination of the If...ElseIf...Else...End If Construct. This is possible since the Field Names in XRF Results are synchronized with Details in the Header Section as well as the Chemical Names.
  2. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim strLine As String
    2. Dim intLineNum As Integer
    3. Dim MyDB As DAO.Database
    4. Dim rst As DAO.Recordset
    5.  
    6. Open CurrentProject.path & "\SuperQ Format.txt" For Input As #1
    7.  
    8. Set MyDB = CurrentDb
    9. Set rst = MyDB.OpenRecordset("XRF Results", dbOpenDynaset)
    10.  
    11. rst.AddNew
    12.   Do While Not EOF(1)
    13.     intLineNum = intLineNum + 1
    14.     Line Input #1, strLine: Debug.Print strLine
    15.      Select Case intLineNum
    16.        Case 1 To 3
    17.          rst.Fields(Trim(Left$(strLine, 22))) = Trim(Mid$(strLine, 23, 35))
    18.        Case 4 To 6
    19.          rst.Fields(Trim(Left$(strLine, 22))) = Trim(Mid$(strLine, 23, 5))
    20.        Case Else
    21.          rst.Fields(Trim(Left$(strLine, 17))) = Trim(Mid$(strLine, 18, 7))
    22.      End Select
    23.   Loop
    24.  
    25. rst.Update
    26.  
    27. Close #1
    28.  
    29. rst.Close
    30. Set rst = Nothing
    31.  
    32. 'Optional Code
    33. With DoCmd
    34.   .OpenTable "XRF Results", acViewNormal, acReadOnly
    35.   .Maximize
    36. End With
    37.  
  3. I tested the Code against the Header Information as well as the first 15 Chemicals in the Text File and it works very well.
  4. Download the Attachment and extract both Files to the 'same' Folder since both the DB and Text File need to reside in the same Folder as the CurrentProject.Path is utilized.
  5. This may/may not be a viable solution for you, only you can decide.
  6. Good Luck on your Project.
Attached Files
File Type: zip Process Text File.zip (83.8 KB, 54 views)
Aug 14 '16 #8
PhilOfWalton
1,430 Expert 1GB
Interesting difference of opinion.

You have very elegantly displayed the results in columns while I have done then in rows.

You obviously have the same opinion as me - look at the spaces, not the text. Usually the best way of handling text files.

Be interesting to see which is preferred.

Phil
Aug 14 '16 #9
ADezii
8,834 Expert 8TB
Interesting difference of opinion.
That's what you got to love about Access, multiple methods to achieve the same result (LOL). I made a slight revision in that I appended the Unit to the actual Value(s) and eliminated the Trailing Period (297. - 297ppm, 121. - 121ppm, 350. - 350ppm, etc.) whenever appropriate. I totally agree with you, it's all up to the OP now. BTW, nice job on your end!
Attached Files
File Type: zip Process Text File_2.zip (74.9 KB, 43 views)
Aug 14 '16 #10
I want to thank you all for the suggestions. I will be testing each to see which will work best. This is one step in 5 - the others are working well. It always amazes me at the talent that exists in this world. As a lone do everything IT guy - it is nice to know there are people that can help me expand my skills. I will keep you posted. Thanks again.
Aug 15 '16 #11
zmbd
5,501 Expert Mod 4TB
FloridaBlueJay I don't think that your database is properly normalized and that may come back to haunt you.
Normalization you ask? Some more information here:
Database Normalization and Table Structures

I'll attach a database here that shows my take on one way to handle your data.


Now ADezii and Phil have focused on various aspects of either the constituent length or the relative spacing between the constituent and the reported concentration and although good approaches, my thought here is to remove the repeated spaces and replace with a comma
Expand|Select|Wrap|Line Numbers
  1. "TiO2            0.35            %" 
becomes
"TiO2,0.35,%"
Expand|Select|Wrap|Line Numbers
  1. "Th (ppm)         121.            ppm"
becomes
"Th (ppm),121.,ppm"
()etc...

This way we can use the split() function (syntax) at the commas and use the information directly (of course, with proper typecasting).

Replacing the doubled(etc...)-spaces groupings using the standard VBA string functions is convoluted; however, since Access2010, regular expressions have been made available via a reference library to "Microsoft VBScript Regular Expressions #.#" (where #.# is the version) and a simple pattern match of " {2,}" will do the trick

So Looking back at the function I wrote here Thread 966911 -post#9 and switching from the execute to the replace method I have this function:
Expand|Select|Wrap|Line Numbers
  1. Function MultiSpaceToCSV(zInStr As String) As String
  2.     'Function to strip all the spaces out of the string and replace with commas.
  3.     '
  4.     Dim zRegExObj As Object
  5.     Dim zPattern As String
  6.     Dim zNewText As String
  7.     Dim ztemp As Variant
  8.     'enable error trapping
  9.     On Error GoTo zerrtrap
  10.     If (zInStr & "") = "" Then Err.Raise Number:=(vbObjectError + 1001), Source:="fncMultiSpaceToCSV", Description:="Null or empty string passed to function"
  11.     'our replacement value
  12.     zNewText = ","
  13.     'create a late bound object to the vbscript library
  14.     'Microsoft VBScript Recular Expressions 5.5
  15.     Set zRegExObj = CreateObject("vbscript.regexp")
  16.     'with our new object lets modify the passed string
  17.     With zRegExObj
  18.         'look for all occurancs of the pattern in the string
  19.         .Global = True
  20.         'first, let's replace all groups or 2 or more spaces with a comma
  21.         zPattern = " {2,}"
  22.         .Pattern = zPattern
  23.         ztemp = .Replace(zInStr, zNewText)
  24.     End With
  25.     'return the new comma seperated string
  26.     MultiSpaceToCSV = ztemp
  27.     'release the object
  28. zcleanup:
  29.     Set zRegExObj = Nothing
  30. Exit Function
  31. zerrtrap:
  32.     'right now, very basic error trap
  33.     On Error Resume Next
  34.     MsgBox prompt:=Err.Number & vbCrLf & Err.Description, _
  35.         title:=Err.Source
  36.     Resume zcleanup
  37. End Function
Taking that same function in post#9 I wanted to take
"Initial weight 0.81g"
and return only the "0.81" for the mass value so a slight modification of the pattern to "[\d.]+" and remove the test we have:
Expand|Select|Wrap|Line Numbers
  1. Function ReturnNumeric(zInStr As String) As Double
  2.      Dim zRegExObj As Object
  3.      Dim zNewValue As Double
  4.      Set zRegExObj = CreateObject("vbscript.regexp")
  5.      With zRegExObj
  6.          .Pattern = "[\d.]+"
  7.          If .test(zInStr) Then zNewValue = .Execute(zInStr)(0)
  8.          ReturnNumeric = zNewValue
  9.  'Why Execute()(0)? In vba test (0) not required
  10.  'in SQL test (0) require or too few augments error
  11.      End With
  12.      Set zRegExObj = Nothing
  13.  End Function
Finally I have made a demo database that uses both of these functions and parses the example data provided in Post#1

The demo has 5 tables, yes, I know, may be over kill; however, this is a normalized database.
+ t_AnalyticalMethods has the "Application" names/method
+ t_AnalyticalRuns has the sample information (the first 6 lines of the sample data)
+ t_constituents has the names of the constituents (TiO2, Th (ppm),...)
+ t_results has the constituent concentrations and units as related back to the Analytical Run/Sample
+ t_sample has the sample name

There is a report, "r_TableRelationships" that shows how the tables are related

Three queries:
+ two cross tab, one that has just the concentrations as values in the table and he second has the concatenation of the value with the unit.
+ third query is just the human readable data used to feed both the cross tab queries and could be used for reports.

Two forms
"F_1000_AnalyticalRuns" opens at start
This is the main data entry form and has a command button to import the text file
"F_1001_results" is a child to "F_1000_AnalyticalRuns" that pulls the data from the t_results as related to t_analyticalruns

The two modules are self named as are the subs/functions
"parseSuperQTextFile()" is the main parsing code.
It opens the "SuperQ Format.txt" in the same directory as the database so when you unzip the files check to make sure that the "SuperQ Format.txt" is in the same directory.

Using the FileDialog Object (read more) one could alter this code to pass the selected file(s) to the procedure.

"parseSuperQTextFile()" performs three passes on the opened text file (the file is opened only once!)
Pass 1: Looks for the "Sample Name" and "Application" information and either finds that information in the tables or appends that information to the table

Pass 2: if there is a sample name and application then it searches for the "Measurement Time" and if found returns that record information if not it appends it to the tables.
As the date/time is down to the second, I doubt that one would ever overwrite the Masses and LOI that follow...

Pass 3: Pulls the Masses and LOI and Constituents.
The Masses and LOI are added to or edited in the currently selected t_ApplicationRuns (I really should store the units separate from the value... maybe a task for OP?)
Constituents are looked up in the t_Constituents and if not found appended
The concentrations are then appended to the t_results if not already in the table (to prevent overwrite)

Finally things are cleaned up and released.

I have ran this using Access 2013 (32 Bit)
Win7(64bit) Enterprise and Win8.1(64Bit) Home against the supplied sample data

The attached database is "empty" to start with as once the file is parsed it shouldn't double enter the data.

Of course, one can take this same concept and use it to enter data in the flat table where the Constituents are the record fields. I would use the same Select..Case method in such a code as I've done in the attached...

Clear as mud? Take a look at the attached, add a few more data files and see what it looks like,
:)
Attached Files
File Type: zip 966921_ImportingInstrumemntTextFiles.zip (82.9 KB, 55 views)
Aug 16 '16 #12
I will review this. Your thought process is interesting. I guess I should have explained a little more. I have built a system to replace something that was expensive and very limited. The text table is not actually text - I convert it - import it into my database then pull the needed data and insert into the correct tables. (a lot of the results in the output are not currently used. I also have 7 different sets of data in the same basic format.) I have built forms - currently manual input - and a working system that my users are very happy with. I am trying to improve by automating what I can to make their life easier. My goal is a button click and the most recent results will populate the correct tables. A second click and reports are emailed to the selected managers.
Aug 18 '16 #13
zmbd
5,501 Expert Mod 4TB
OK,
The fields
[t_analyticalruns]![AnalyticalRun_InitialWeight]
[t_analyticalruns]![AnalyticalRun_FinalWeight]
[t_analyticalruns]![AnalyticalRun_LOI]

could be converted such that InitialWeight, FinalWeight, and LOI become records in t_constituents much as "Th (ppm)" has with the same concept to relate the respective data back tot he analysis for the sample.

The form would have to be changed a tad to reflect this by removing those fields from the parent form as that data would then be presented in the subform. I would most likely change the entire form as follows:

Parent_Form: UnBound
Child_Form_1: Bound to t_samples
Child_form_2: Bound to t_analyticalruns
Child_form_3: Bound to t_results

On the parent form two hidden controls
txt_pk_sample - Control source linked to the child_form_1 so as to duplicate the pk_samples for the selected record in child_form_1

txt_pk_analyticalruns - Control source linked to the child_form_2 so as to duplicate the pk_analyticaruns for the selected record in child_form_2

The subform container controls would be set so that
Child_form_1 : no links in parent/child
Child_form_2 : parent bound to txt_pk_sample, child bound to the fk_samples
Child_form_3 : parent bound to txt_pk_analyticalruns , child bound to the fk_analyticalruns

I have a database where I use this exact arrangement.
The parent opens, I select the sample in the first subform which filters the records in the second child form to show the analysis ran on that that sample, selecting the analysis in the second child form filters the records in the third child form to show the constituents and their concentrations for that analysis. Wouldn't take me much to alter the attachment in Post#12 to reflect this arrangement.


BTW: I'm an analytical chemist :)
Aug 18 '16 #14
zmbd
5,501 Expert Mod 4TB
The text table is not actually text - I convert it - import it into my database
That may be topic for another thread... if the raw data is still an ASCII type file, depending on what this data looks like, there may be a way to handle the entire process under VBA.
Aug 19 '16 #15
The output has a ".qan". It is a simple to change to ".txt" and import.
Aug 23 '16 #16
zmbd
5,501 Expert Mod 4TB
I have used the file dialog box with the multi-select set to true to import multiple text files.

You can then use the With...End construct with the file-picker dialog, with multi-select set to true, to iterate thru the selected files. You could use the Name() Statement (Syntax) to rename the file to the *.txt extension then, within the With...End loop, use my sub as a function, passing to it the new file name, returning at least true/false for if the file imports without error.

:)
Aug 23 '16 #17
I'll take a look at that. I have a task that runs every time an output is detected and changes to .txt. A second check is run to see if it has been processed and if so, it is renamed to allow for the next output. I am working on using date/time stamp to allow for several to be in a "que" to be processed. We have a small lab and it is not an issue now but I am trying to plan for the future. I would like to thank you for all your ideas /help.
Aug 24 '16 #18

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Ed Whittaker | last post by:
Hi, I`m writing a wizard that very much resembles the Excel Text Import Wizard. So far so good but I have been unable to find a control to reproduce the step where the user picks the boundries...
1
by: DCM Fan | last post by:
Access 2K, SP3 on Windows 2K, SP4 All, I have an import spec set up with quoted Identifiers and comma-separated values. The text file is produced by a 3rd-party program of which I have no...
0
by: King Ron | last post by:
Access2002/WinXP Has anyone come up with a resolution for the text import wizard GUI breaking down after installing patches? When building a new table using the gui, the cursor moves to the...
1
by: usenethelp | last post by:
Has anyone had this problem where using the Text Import Wizard in Access 2002 Service Pack 3 does not allow you to change the field names for any field except the first one? If so, is there a...
1
by: Jenny | last post by:
Since I got a new PC with XP and the latest version of Access on it, I have been having problems with the Text Import Wizard. In the section where you specify the field options, when I click on...
8
by: Scott | last post by:
I wish to create a text document (to the users desktop on any machine) which outputs to a standard text file with information obtained from there use of my program... Some of the text will be fixed...
0
by: vektor | last post by:
Hi Can someone tell me how to hyperlink email addresses in the rich text box or standard text box. I can hyperlink the website addresses but i dont know how to hyperlink email addresses. ...
2
by: samithajai | last post by:
1)what is a non standard text file for input or output in c? 2)I want to read a simple xml file (without any namespaces and dtd) in c and get its contents How should i do it? thanks
1
by: user033088 | last post by:
I have a requirement where I have to import a lot of text files into excel. Up till now I can import the text files using visual studio C# but after importing the format is not exactly the way I want...
3
zmbd
by: zmbd | last post by:
It seems that in VBA some the most fundamentally basic commands for file control are the hardest to find any information for, for example, the basic File operation commands, such as DIR(), CHDRIVE(),...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.