Convert .csv file into Excel in VBScript

Question

I'm writing a program to convert a .csv file into an Excel file in VBScript.

This is the portion of my .csv file (It has a header as well)

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

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

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.

Thanks in advance

Ansgar Wiechers · Accepted Answer · 2013-04-05 14:07:09Z

There are several issues with the code from your updated question:

Const XlPlatform = "xlWindows"

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.
Set workBook1 = xlApp.ActiveWorkBook

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.
Set workBook1 = xlApp.WorkBooks.OpenText(...)

The OpenText method does not return an object, so there's nothing to assign in the above statement.
...ooks.OpenText(..., true, "CRLF", ...)

The string "CRLF" is not a character. Why do you want to specify line-breaks as separator-characters anyway?
..., Array(...), true, false)

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.Save "Y:\Personal Folders\XXXX\x.xlsx", xlNormal

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.

The following code worked for me:

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

Community · Accepted Answer · 2017-05-23 11:58:45Z

0

I'm using Excel 2003.The following is a code I got to specify the .csv file format

Set xl = CreateObject("Excel.Application")

Set wb = xl.Workbooks.OpenText "input.csv", , , xlDelimited, xlDoubleQuote, _
  False, False, True, , , , dataTypes

from Vbscript to import csv into excel

Could you please explain the function of the following line?

Set wb = xl.Workbooks.OpenText "input.csv", , , xlDelimited, xlDoubleQuote, _
      False, False, True, , , , dataTypes

My implementation of the OpenText property for the above mentioned .csv file

Set workBook1 = xlApp.WorkBooks.OpenText("D:\Personal Folders\XXXX\TestFile1.csv", , ,xlDelimited, ,false ,false ,false ,True,false ,false ,"~")

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Apr 3, 2013 at 15:11

SriniShine

1,1397 gold badges31 silver badges47 bronze badges

7 Comments

Ansgar Wiechers Over a year ago

OpenText is the method for importing text files. Since VBScript doesn't support named parameters, you have to specify all parameters in the proper order. You can omit the values for parameters that you want to remain at default value, though, and you can omit trailing default values entirely. The dataTypes value is an array that defines the format for each imported column. When the file is opened successfully, the created workbook object is assigned to the variable wb.

SriniShine Over a year ago

Ansgar:Thanks alot for the reply.I learnt there are 12 parameters for the OpenText property.But now I'm getting an error saying "Unable to get the OpenText property of the WorkBooks class".Do you know the reason for this?I edited the obove script with my OpenText version

SriniShine Over a year ago

I have included my implementation for the OpenText in the above post.Thanks

Ansgar Wiechers Over a year ago

OpenText doesn't return a workbook object, so you'll have to do assign the workbook object to a variable in a second step: xlApp.Workbooks.OpenText "...", ..., "~" : Set workBook1 = xlApp.ActiveWorkbook.

SriniShine Over a year ago

I'm still getting the error "Unable to get the OpenText property of the WorkBooks class".

|

user2140261 · Accepted Answer · 2013-04-03 15:15:31Z

-1

You need to specify what Format you are saving as. The following will specify it as a standard .xls for 2003

    Workbooks.Open Filename:= _
    "D:\Personal Folders\XXXXX\TestFile1.csv"

    ActiveWorkbook.SaveAs Filename:= _
    "D:\Personal Folders\XXXXX\x.xls", FileFormat:=xlNormal

    ActiveWorkbook.Close

Also can try

ActiveWorkbook.SaveAs Filename:= _
    "D:\Personal Folders\XXXXX\x.xls", FileFormat:= 56

edited Apr 3, 2013 at 15:15

answered Apr 3, 2013 at 12:39

user2140261

8,0037 gold badges35 silver badges45 bronze badges

4 Comments

SriniShine Over a year ago

Thanks for the reply.Currently I'm using Excel 2003 (.xls).how can I specify the data format in Excel 2003..???

user2140261 Over a year ago

Try the updated code. It's been a while but I think that is how its done.

Ansgar Wiechers Over a year ago

Your code is VBA, not VBScript. Named parameters don't work in VBScript.

SriniShine Over a year ago

Thanks for the replay.But I'm looking for a VBScript implementation.

Collectives™ on Stack Overflow

Convert .csv file into Excel in VBScript

3 Answers 3

Comments

7 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related