VBA Excel：从CSV文件中以特定格式提取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8521342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel : Extract data in specific format from CSV files
提问by linguini
I have different CSV files and these files have some raw data
Computer Name,"Computer Serial","User name","Employee Number","Software name"followed by the below data.
我有不同的 CSV 文件，这些文件有一些原始数据，
Computer Name,"Computer Serial","User name","Employee Number","Software name"后跟以下数据。
added this from the linked file:
从链接文件中添加了这个：
Comp;uter;"Name ";Computer;Seria;l"" User";"name"" Employee";"Number"" Software";"name"""
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"LiveUpdate 3.3 (Symantec Corporation)";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Adobe SVG Viewer 3.0";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Adobe Download Manager 2.0 (Supprimer uniquement)";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"ATI - Utilitaire de d??sinstallation du logiciel";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"ATI Display Driver";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"IBM iSeries Access for Windows";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,DomusDraw;;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"NeXspan SoftPhone i2052 R3.1 D03";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"D??sinstallation du logiciel d''imprimante IBM";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"D??sinstallation du logiciel IBM";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"CA 01 - le Offline MALL de Siemens Automation and Drives";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Java Web Start";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB873339";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB885250";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB885835";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB885836";;;;;;;;
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB886185";;;;;;;;
I never worked with Excel VBA before, this is the first time I gonna work on this. I started working on some examples to create & run VBA code in Excel.
我以前从未使用过 Excel VBA，这是我第一次进行此工作。我开始研究一些在 Excel 中创建和运行 VBA 代码的示例。
Some one please help me out to proceed with this, I want to create VBA code to extract the raw data & put into the following format.
有人请帮我继续这个，我想创建VBA代码来提取原始数据并放入以下格式。
CompName ComputerSerial UserName EmpNo SoftwareName
DK4408XP0016 1108081520001 GAILLARD Alain 11014 LiveUpdate 3.3 (Symantec Corporation)
DK4408XP0016 1108081520001 GAILLARD Alain 11014 Adobe SVG Viewer 3.0
I checked this link Code for looping through all excel files in a specified folder, and pulling data from specific cells& it has an information about "excel loop through files in folder" but this is not what I'm looking for.
我检查了这个链接代码，用于循环遍历指定文件夹中的所有 excel 文件，并从特定单元格中提取数据，它有关于“excel 循环遍历文件夹中的文件”的信息，但这不是我要找的。
I guess, what I need to do here, remove the special characters like , "" ;from the file & then format. I simply don't know how to proceed with this.
我想，我需要在这里做的是，, "" ;从文件中删除特殊字符，然后格式化。我只是不知道如何处理这个问题。
Is there any tool to extract data from .CSV files??? I need some suggestion, idea or some good examples for my problem and it will be really helpful for me.
是否有任何工具可以从 .CSV 文件中提取数据？？？我需要一些建议、想法或一些很好的例子来解决我的问题，这对我真的很有帮助。
I'm sharing one of my files here: http://uploadmb.com/freeuploadservice.php?uploadmbID=1323960039&srv=www&filename=4408_NANTES_softwares.csv
我在这里分享我的一个文件：http: //uploadmb.com/freeuploadservice.php?uploadmbID=1323960039&srv=www&filename=4408_NANTES_softwares.csv
回答by Slim Soltani
you have a lot of choise to realize this.
你有很多选择来实现这一点。
If your operation are an exception (just for today), you can use the function of import and convert CSV file of Excel.
如果您的操作是例外（仅限今天），您可以使用 Excel 的导入和转换 CSV 文件的功能。
Open your Excel, in the toolbar, just click on Data/Convert.
打开你的 Excel，在工具栏中，点击数据/转换。
If you want to put a task like macro, you can take example by this script :
如果你想放置一个像宏这样的任务，你可以以此脚本为例：
Public Sub IsValid()
Dim i As Long
Dim valueTemp As String 'Chaine de caractere
Dim wsTemp As Worksheet 'Feuille
Dim rTemp As Range 'Range
'Variable initialise a 1
i = 1
Set wsTemp = ThisWorkbook.Worksheets(NameFileResult)
While (Ws_Result.Cells(i, 1) <> "")
valueTemp = Ws_Result.Cells(i, 1)
With ThisWorkbook 'ton fichier dans lequel tu fais ta recherche
Set rTemp = wsTemp.Range("A:D").Find(valueTemp, , xlValues, xlWhole, , , False)
If Not rTemp Is Nothing Then
wsTemp.Rows(rTemp.Row).Interior.ColorIndex = 4 'Vert si la donnees est disponible
wsTemp.Rows(rTemp.Row).Copy (Ws_Index.Rows(15))
wsTemp.Rows(1).Copy (Ws_Index.Rows(14))
Else
Ws_Index.Rows(15).Clear
Ws_Index.Rows(14).Clear
Ws_Index.Cells(15, 5).Value = NameMsgBoxNoFind
Ws_Index.Rows(15).Interior.ColorIndex = 3
End If
End With
i = i + 1
Wend
End Sub
结束子
回答by Jean-Fran?ois Corbett
This works for your example file:
这适用于您的示例文件：
' Open the csv file as a text file
Workbooks.OpenText Filename:="C:08_NANTES softwares.csv"
Excel sometimes parses CSV files automatically, but sometimes not; I can't figure out the pattern. So you can add the following to ensure that it gets parsed properly:
Excel 有时会自动解析 CSV 文件，但有时不会；我无法弄清楚模式。因此，您可以添加以下内容以确保它被正确解析：
' Parse it using comma and semicolon as delimiters
Range(Range("A1"), Range("A1").End(xlDown)).TextToColumns _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=True, Space:=False, Other:=False, _
FieldInfo:= _
Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 2))
The FieldInfobit may look a bit cryptic, but the only thing it does is specify that your fields are to be treated as text (mostly to avoid your serial number 108081520001being formated in scientific notation).
该FieldInfo位可能看起来有点神秘，但它唯一能做的就是指定您的字段将被视为文本（主要是为了避免您的序列号108081520001以科学记数法格式化）。
回答by SandPiper
I know this is an old thread, but I had to do this also and I thought my solution might help future users. If there is a zero length element (e.g. elem1,elem2,,elem4) then the array value will = "". It has worked very well for me.
我知道这是一个旧线程，但我也必须这样做，我认为我的解决方案可能会帮助未来的用户。如果存在零长度元素（例如 elem1,elem2,,elem4），则数组值将 = ""。它对我来说非常有效。
Function ParseLineEntry(LineEntry As String) As Variant
'This function takes a .CSV line entry as argument and parses it into an array of each element.
Dim NumFields As Integer, LastFieldStart As Integer
Dim LineFieldArray() As Long
Dim i As Long, j As Long
'Determine how many delimitations there are. There will always be at least one field
NumFields = 1
For I = 1 To Len(LineEntry)
If Mid(LineEntry, i, 1) = "," Then NumFields = NumFields + 1
Next I
ReDim LineFieldArray(1 To NumFields)
'Parse out each element from the string and assign it into the appropriate array value
LastFieldStart = 1
For i = 1 To NumFields
For j = LastFieldStart To Len(LineEntry)
If Mid(LineEntry, j, 1) = "," Then
LineFieldArray(i) = Mid(LineEntry, LastFieldStart, j - LastFieldStart)
LastFieldStart = j + 1
Exit For
End If
Next j
Next i
ParseLineEntry = LineFieldArray
End Function
