VBA将文本文件导入Excel并解析

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.

我为自己是个新手而感到惭愧。我有以以下样式生成的TXT文件。我需要能够将它们转换为可以导入数据库的电子表格。

": 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

这只是TXT的一个小样本，可能有更多的S#，将会有更多的Var。大量的数据可以被删除，我只需要一些东西，并格式化它。> - >分析- >导入格式

        : 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

1 个解决方案

#1

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

我认为您的文本文件中的数据太不结构化，不能使用QueryTables()方法。

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.

您可以将调用代码放在一行中，但是通过从中创建单独的子例程，您可以分别编写和测试每个子例程，同时忽略文件的其余部分。一旦你有了一个工作，你可以注释掉这个调用，这样你就不必在调试下一个的时候再去检查它了，这样可以节省时间。另外，在单独的子代码中读取大量代码要容易得多。

#1