在VBScript中将.csv文件转换为Excel [英] Convert .csv file into Excel in VBScript

问题描述

我在写一个程序，将.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)

报告标题，商品性能特征报告,,报告
性能特征报告,,,,,,,开始
日期，8/7/2012 ,,,,,,,,,,模板
名称，XXXX-Test-VVV-两位置， ,,,,,,,,总计
Productions ,,,,,,,,,,,

Report Title,Goods Performance Characteristics Report,,Report Title,Goods Performance Characteristics Report,,,,,,, Begin Date,8/7/2012,,,,,,,,,, Template Name,XXXX-Test-VVV-Two-Positions,,,,,,,,,, Total Productions,,,,,,,,,,,

，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

保存方法现有名称下的工作簿。要使用其他名称保存工作簿，必须使用 SaveAs 方法。常量 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！