问题描述
我在写一个程序，将.csv文件转换为VBScript中的Excel文件。
I'm writing a program to convert a .csv file into an Excel file in VBScript.
这是我的一部分.csv文件（也有标题）
This is the portion of my .csv file (It has a header as well)
，ID，名称，产品说明，发行日期，产品类型，重量
（kg），重量（lb），价格，产品数量，利润，总价值（000）
，315616102，Lux Honey，身体
Wash，8/1 /2012,C,0.06,0.06,93,793920,0,7455703038，579780206，清除
AntiDandruf，洗发水，8/1/2012，C，0.06,0.06,60.5,1325980,2.05,8022179000
，761713106，Loreal Divine，Face
Crub，8/1/2012，F，0.12,0.12,46.52,3314780,5.07,15420356560
,ID,Name,Product Description,Issue Date,Product Type,Weight (kg),Weight (lb),Price,Product volume, Profit,Total Value (000) ,315616102,Lux Honey,Body Wash,8/1/2012,C,0.06,0.06,93,793920,0,7455703038 ,579780206,Clear AntiDandruf,Shampoo,8/1/2012,C,0.06,0.06,60.5,1325980,2.05,8022179000 ,761713106,Loreal Divine,Face Crub,8/1/2012,F,0.12,0.12,46.52,3314780,5.07,15420356560
这是我为转换写的更新的 VBScript代码。
This is the updated VBScript code I wrote for the conversion.
Dim xlApp, workBook1, workBook2,aSheets, fileName, aInfo2,aInfo1,oExcel
Const XlPlatform = "xlWindows"
Const xlDelimited = 1
Const xlTextQualifierDoubleQuote = 1
Const xlTextFormat = 2
Const xlGeneralFormat = 1
Set oExcel = Sys.OleObject("Excel.Application")
Set xlApp = CreateObject("Excel.Application")
Set workBook1 = xlApp.ActiveWorkBook
Set workBook1 = xlApp.WorkBooks.OpenText("Y:\Personal Folders\XXXX\TestFile1.csv",XlPlatform, 1, xlDelimited, xlTextQualifierDoubleQuote, true, false, false, true, false, true, "CRLF", Array(Array (1,2),Array (2,2),Array (3,2),Array (4,1),Array (5,2),Array (6,1),Array (7,1),Array (8,1),Array (9,1),Array (10,1),Array (11,1)), true, false)
Set workBook1 = xlApp.ActiveWorkBook
xlApp.Visible = true
workBook1.Save "Y:\Personal Folders\XXXX\x.xlsx", xlNormal
workBook1.Close
但数据不是表格形式在Excel文件中。我想以表格形式显示数据。
任何人都可以帮助我基于delimeters提取数据。我需要excel文件中的头部分
But the data is not in a tabular form in the Excel file.I want to display the data in a tabular form.
Could anyone please help me to extract data based on the delimeters.I need the header part in the excel file as well.
提前感谢
推荐答案
代码：
-
Const XlPlatform =xlWindows
XlPlatform 必须是数字值，而不是字符串。 xlWindows 成员的值为 2 。但是，由于这是默认值，您可以在调用 OpenText 时忽略此值。
XlPlatform must be a numeric value, not a string. The xlWindows member of the enumeration has the value 2. However, since that's the default anyway, you can simply omit this value when calling OpenText.
code>设置workBook1 = xlApp.ActiveWorkBook
Set workBook1 = xlApp.ActiveWorkBook
新生成的Excel实例没有活动工作簿，
A newly spawned Excel instance doesn't have an active workbook, so it's pointless to assign that to a variable before you actually open or create a workbook.
设置workBook1 = xlApp.WorkBooks.OpenText ）
OpenText 方法不返回对象，
... ooks.OpenText（...，true，CRLF，...）
字符串CRLF不是字符。
...，Array（...） true，false）
指定的最后两个参数是 TextVisualLayout 到右边到右边）和 DecimalSeparator 。它们都不是布尔值。
The last 2 parameters you specified are TextVisualLayout (specifies left-to-rigt/right-to-left) and DecimalSeparator. Both of them are not boolean values. Simply omit them if you don't know for certain that you need them.
workBook1.SaveY：\个人文件夹\XXXX\x.xlsx，xlNormal
保存方法现有名称下的工作簿。要使用其他名称保存工作簿，必须使用方法。常量 xlNormal 未在代码中的任何位置定义。此外， xlNormal 生成Excel 97/2003工作簿（ .xls ）。要将工作簿保存为Excel 2007/2010工作簿（ .xlsx ），必须使用 xlOpenXMLWorkbook 常数。 p>
The Save method saves a workbook under its current name. To save a workbook under a different name you must use the SaveAs method. The constant xlNormal isn't defined anywhere in your code. Plus, xlNormal produces an Excel 97/2003 workbook (.xls). To save a workbook as an Excel 2007/2010 workbook (.xlsx) you must use the xlOpenXMLWorkbook constant.
以下代码适用于我：
Const xlDelimited = 1
Const xlTextQualifierDoubleQuote = 1
Const xlOpenXMLWorkbook = 51
Set xl = CreateObject("Excel.Application")
xl.Workbooks.OpenText "Y:\Personal Folders\XXXX\TestFile1.csv", , , xlDelimited _
, xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
, Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
, Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook
wb.SaveAs "Y:\Personal Folders\XXXX\x.xlsx", xlOpenXMLWorkbook, , , , False
wb.Close
xl.Quit
这篇关于在VBScript中将.csv文件转换为Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！