VBA Excel：从CSV文件中提取特定格式的数据 [英] VBA Excel : Extract data in specific format from CSV files

问题描述

我有不同的CSV文件，这些文件有一些原始数据
计算机名称，计算机串行，用户名，员工号，软件名称后面是以下数据。

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.

从链接的文件中添加：

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

我以前从未与Excel VBA一起工作，这是我第一次在此工作。我开始研究一些例子来创建&在Excel中运行VBA代码。

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.

有一个请帮我解决这个问题，我想创建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.

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

我查看了这个链接代码循环指定文件夹中的所有excel文件，并从特定单元格中拉取数据&它有一个关于excel循环通过文件在文件夹的信息，但这不是我正在寻找。

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.

我想，我需要做的在这里，删除文件&的特殊字符，如，; 然后格式化。我只是不知道如何继续这个。

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.

有没有任何工具来从.CSV文件中提取数据？我需要一些建议，想法或一些很好的例子来解决我的问题，这对我来说真的很有用。

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.

我在这里分享我的一个文件： http://uploadmb.com/freeuploadservice.php?uploadmbID=1323960039& ; srv = www& filename = 4408_NANTES_softwares.csv

I'm sharing one of my files here: http://uploadmb.com/freeuploadservice.php?uploadmbID=1323960039&srv=www&filename=4408_NANTES_softwares.csv

推荐答案

这适用于您的示例文件：

This works for your example file:

' Open the csv file as a text file
Workbooks.OpenText Filename:="C:\4408_NANTES softwares.csv"

Excel有时会自动解析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:

' 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))

FieldInfo 位可能看起来有点神秘，但它唯一的作用是指定您的字段被视为文本（主要是避免您的序列号 108081520001 以科学记数法形式）。

The FieldInfo bit 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 108081520001 being formated in scientific notation).

这篇关于VBA Excel：从CSV文件中提取特定格式的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！