本文介绍了在VBScript中将.csv文件转换为Excel的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



我在写一个程序,将.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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-29 02:26