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

查看：666
本文介绍了VBA Excel：从CSV文件中提取特定格式的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有不同的CSV文件，这些文件有一些原始数据
计算机名称，计算机序列号，用户名，员工号


$ b

Comp; uter;Name; Computer; Seria; lUser;nameEmployee;NumberSoftware;name
DK4408XP0016,108081520001 ，GAILLARD Alain，11014，Adobe SVG Viewer 3.0;;;;;LiveUpdate3.3（Symantec Corporation）;;;;;;;;;;;;;;;;;;;; ;;;
DK4408XP0016,108081520001，GAILLARD Alain，11014，Adobe Download Manager 2.0（Supprimer uniquement）;;;;;;;
DK4408XP0016,108081520001，GAILLARD Alain，11014 ，ATI-Utilitaire dedé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代码。



有一些请帮助我继续这个，我想创建VBA代码来提取原始数据& 

  CompName ComputerSerial用户名EmpNo软件名
 
 DK4408XP0016 1108081520001 GAILLARD Alain 11014 LiveUpdate 3.3 （赛门铁克公司）
 DK4408XP0016 1108081520001 GAILLARD Alain 11014 Adob​​e SVG Viewer 3.0

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



我想，我需要做的，从文件& c中的，; 然后格式化。我只是不知道如何继续这个。



有没有任何工具从.CSV文件提取数据？我需要一些建议，想法或一些好的例子，对我来说，这将是非常有帮助的。



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

解决方案

这适用于您的示例文件：

 '以文本文件打开csv文件
 Workbooks.OpenText文件名：=C：\4408_NANTES softwares.csv

Excel有时会自动解析CSV文件，我不知道这个模式。因此，您可以添加以下内容以确保正确解析：

 '使用逗号和分号解析分隔符
范围（范围（A1），范围（A1）End（xlDown））TextToColumns _ 
 DataType：= xlDelimited，_ 
 TextQualifier：= xlDoubleQuote，ConsecutiveDelimiter：= False ：= False，_ 
 Semicolon：= True，Comma：= True，Space：= False，Other：= False，_ 
 FieldInfo：= _ 
 Array（1，2） ，Array（2,2），Array（3,2），Array（4,1），Array（5,2））

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


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.

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.

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

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.

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.

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

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屋！

查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆