VBA Import Text file into Excel and Parse

Question

I appologize for being a noob. I have TXT files that are generated in the following style. I need to be able to convert them into a SpreadSheet that can be imported into Database.

": var1:"
"NOM (LSL, USL) = 0.044 (0.035, 0.051)"
"Subgroup"  1   2   3       
"Status"    ""  ""  ""  
"X" 0.045   0.047   0.048       
"R" 0.009   0.008   0.003       
"s" 0.003   0.003   0.001       
"S1"    0.050   0.051   0.049       
"S2"    0.046   0.047   0.047       
"S3"    0.048   0.049   0.048       
"S4"    0.047   0.048   0.046       
"S5"    0.042   0.044   0.047       
"S6"    0.042   0.044   0.048       
"S7"    0.041   0.043   0.048       
"S8"    0.044   0.048   0.049       
"comments"  " " " " " " 
"Inspector" "tb"    "tb"    "tb"    
"Machine"   "shot1" "shot2" "shot3"     
"ID #"  " " " " " " 
"Work Order"    " " " " " "     
"Samples"   " " " " " " "   
"Sample"    " " " " " " "   
"Sample"    " " " " " " "   
"Sample"    " " " " " " "   
"Time"  "23:05:04"  "23:30:33"  "02:15:40"
"Date"  "03/26/2015"    "03/26/2015"    "03/27/2015"

": var2:"
"NOM (LSL, USL) = 0.023 (0.020, 0.026)"
"Subgroup"  1   2   3   
"Status"    ""  ""  ""  
"X" 0.022   0.021   0.024       
"R" 0.003   0.002   0.003       
"s" 0.001   0.001   0.001       
"S1"    0.022   0.022   0.023   
"S2"    0.023   0.021   0.022   
"S3"    0.022   0.021   0.023   
"S4"    0.021   0.021   0.025   
"S5"    0.022   0.022   0.024   
"S6"    0.023   0.020   0.022   
"S7"    0.020   0.022   0.024   
"S8"    0.020   0.020   0.025   
"comments"  " " " " " " 
"Inspector" "tb"    "tb"    "tb"
"Machine"   "shot1" "shot2" "shot3"
"ID #"  " " " " " "     
"Work Order"    " " " " " " 
"Samples"   " " " " " " 
"Sample"    " " " " " " 
"Sample"    " " " " " " 
"Sample"    " " " " " " 
"Time"  "23:05:04"  "23:30:33"  "02:15:40"
"Date"  "03/26/2015"    "03/26/2015"    "03/27/2015"

": var3:"
"NOM (LSL, USL) = 0.023 (0.020, 0.026)"
"Subgroup"  1   2   3
"Status"    ""  ""  ""
"X" 0.045   0.044   0.046
"R" 0.004   0.005   0.007
"s" 0.001   0.002   0.003
"S1"    0.046   0.046   0.045
"S2"    0.045   0.041   0.046
"S3"    0.046   0.046   0.048
"S4"    0.047   0.046   0.042
"S5"    0.044   0.045   0.048
"S6"    0.046   0.045   0.047
"S7"    0.043   0.042   0.047
"S8"    0.045   0.042   0.041
"comments"  " " " " " " 
"Inspector" "tb"    "tb"    "tb"
"Machine"   "shot1" "shot2" "shot3"
"ID #"  " " " " " "
"Work Order"    " " " " " "
"Samples"   " " " " " "
"Sample"    " " " " " "
"Sample"    " " " " " "
"Sample"    " " " " " "
"Time"  "23:05:04"  "23:30:33"  "02:15:40"
"Date"  "03/26/2015"    "03/26/2015"    "03/27/2015"

This is only a Small sample of the TXT, there may be more S#'s and will be more Var's. A good chunk of the data can be disgarded, I only need a few things from it and to format it. > IMPORT->PARSE->FORMAT

        : var1:     : var2:     : var3:
NOM     0.044       0.023       0.023
LSL     0.035       0.02        0.02
USL     0.044       0.026       0.026
S1      0.05        0.022       0.046
S2      0.046       0.023       0.045
S3      0.048       0.022       0.046
S4      0.047       0.021       0.047
S5      0.042       0.022       0.044
S6      0.042       0.023       0.046
S7      0.041       0.02        0.043
S8      0.044       0.02        0.045
S1      0.051       0.022       0.046
S2      0.047       0.021       0.041
S3      0.049       0.021       0.046
S4      0.048       0.021       0.046
S5      0.044       0.022       0.045
S6      0.044       0.02        0.045
S7      0.043       0.022       0.042
S8      0.048       0.02        0.042
S1      0.049       0.023       0.045
S2      0.047       0.022       0.046
S3      0.048       0.023       0.048
S4      0.046       0.025       0.042
S5      0.047       0.024       0.048
S6      0.048       0.022       0.047
S7      0.048       0.024       0.047
S8      0.049       0.025       0.041

I know how to accomplish a few of these things, but I do not know how to combine them into a single Sub if possible. To my under standing I should go about this with something like this.

Sub Import()

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Temp\HospiraWalls2.TXT" _
        , Destination:=Range("$A$1"))
        .Name = "HospiraWalls2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=9
End Sub

The PARSING : This I do not know

Sub OneColumn()

Dim vIn     As Variant
Dim vOut    As Variant
Dim i       As Long
Dim j       As Long
Dim ub1     As Long
Dim ub2     As Long

vIn = Application.Transpose(Range("B1").CurrentRegion)
ub1 = UBound(vIn, 1)
ub2 = UBound(vIn, 2)
ReDim vOut(1 To ub1 * ub2, 1 To 1)
For i = 1 To ub1
    For j = 1 To ub2
        vOut(j + (ub2 * (i - 1)), 1) = vIn(i, j)
    Next j
Next i
Range("A1:A" & (ub1 * ub2)) = vOut

End Sub

What do you get when you run your Import() Sub? My thought would be to open it as a text file & parse it yourself, since the formatting is so context sensitive. — FreeMan
– FreeMan, Commented Mar 30, 2015 at 16:48
I know the scripting as I have it now will not work. I am looking for way to do it in one script, this is something that needs to be done 6-8 times a week and takes alot of time to do on a individual basis. — def_not_tom
– def_not_tom, Commented Mar 30, 2015 at 17:05
So, Imort() is totally untested? Makes sense, .QueryTables.Add() is designed to get data from a database, not a text file as randomly structured as this.... — FreeMan
– FreeMan, Commented Mar 30, 2015 at 17:09

FreeMan · Accepted Answer · 2015-03-30 17:23:06Z

I think your data in your text file is too unstructured to use the QueryTables() method.

Someone may have a better algorithm for you, but this would be my approach. (note: pseudo-code only)

Open the file
MyLine = Read a line from the file
While not EOF
  'get your key for the line (the bit between " ")
  Key = Mid(Myline, 2, InStr(2, MyLine, """")) 
  if left(key, 5) = ": var" then
    Call NewVarSetupNewColumn
  elseif left(key, 3) = "Nom" then
    Call ParseNomLine
  elseif left(key,1) = "S" and left(key,2) <> "Sa" then 'Get "Sx", but not "Sample"
    Call ParseSxLine
  elseif ...
  'handle remaining lines you need, if you don't need the line, don't handle it
  Read next line
End While

You could put the Call code in line, but by making separate subroutines out of it, you can write and test each one individually while ignoring the rest of your file. Once you have one working, you can comment out the call so you don't have to go through it while you're debugging the next one, just to save time. Plus, it'll be a lot easier to read with the bulk of the code in separate subs.

Collectives™ on Stack Overflow

VBA Import Text file into Excel and Parse

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related