asked on

VBScript to convert CSV file to Excel - number format issue

Hi,

I have this csv file saved on the c drive as item.csv below is contents of the csv file:
Item Report,,,
id,merchant_date,item_id,amount
20070612005814,2007-06-12,1815918227960176043016,65.00
20070612005814,2007-06-12,1815919623710176043016,182.34
20070612005814,2007-06-12,1815931096710176043016,25.00

When you open the csv file in Notepad it looks fine,however, when opened with Excel the "id" column and "item_id" column display with a scientific notation. I need a VBScript to convert these two columns in a format that is the original view and saved as an excel file. I have tried a bunch of things but am at a loss, below is the script I have been playing with.

'******************************************************
Option Explicit

Dim fldr, f, file,strOrigFile, strFile, fso, strDirectory
Dim objExcel

strOrigFile = "c:\items.csv"
'******************************************************
'CONVERTS THE FILE TO AN EXCEL FILE
'******************************************************
strFile = replace(strOrigFile,".csv",".xls")

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open strOrigFile

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile, -4143

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close
objExcel.DisplayAlerts = False
objExcel.Application.Quit

'******************************************************
'FORMAT THE EXCEL FILE AND SAVE IT
'******************************************************

objExcel.Workbooks.Open strFile

objExcel.columns("A:A").numberformat="0"
objExcel.columns("C:C").numberformat="0"

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile, -4143

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing

msgbox "FINISHED!!!"

'******************************************************

Now the "id" column looks good but the "item_id" puts all zero's after the 15th digit. I'm not sure how to convert this csv file to excel so that it displays correctly in Excel.

RobSampson

Hi, you can try a VBS like this:
'===============
strFileOutputPath = Replace(Wscript.ScriptFullName, wscript.ScriptName, "") & "LeadingZeroTest2.txt"
Const xlDelimited = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText _
strFileOutputPath,,,xlDelimited,,,False,False,True,,,,Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2))
'================

I'm trying to make this a little more dynamic, but hopefully that will work for now.

What is does is emulate the Text Import Wizard, setting the field data types to "Text".

Regards,

Rob.

RobSampson

Here's a version that is a little more dynamic. All you need to change the is
strFile
to match the file path that you want to open, and
intNumberOfColumns
to specify how many columns it should turn into Text data type.

'=======================
strFile = Replace(Wscript.ScriptFullName, wscript.ScriptName, "") & "LeadingZeroTest2.txt"
intNumberOfColumns = 4
strDataTypes = "Array("
For intCount = 1 To intNumberOfColumns
      If Right(strDataTypes, 1) = "(" Then
            strDataTypes = strDataTypes & "Array(" & intCount & ", 2)"
      Else
            strDataTypes = strDataTypes & ", Array(" & intCount & ", 2)"
      End If
Next
strDataTypes = strDataTypes & ")"
MsgBox strDataTypes
Const xlDelimited = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText strFile,,,xlDelimited,,,False,False,True,,,,Eval(strDataTypes)
'==================

Regards,

Rob.

WatchoutMac

ASKER

Thanks for the reply Rob.

When Excel opens this it still shows the "id" and "item_id" column in scientific notation.

I also wanted to clarify that I only want to convert these two columns to their real values. I don't want the "merchant_date" or "amount" columns to be text espicially the "amount" column.

If it is converted to text sorting on the "amount" from Excel will not be correct. Example when sorting by price 182.34 would end up showing before 65 which is wrong.

WatchoutMac

ASKER

sorry I posted that before your second message. I am testing your second one now

WatchoutMac

ASKER

The seconds script still opens the Excel file with scientific notation. The first and third colmun in this file need to be their original value. Do you see what I mean when using the below file as the input?

Item Report,,,
id,merchant_date,item_id,amount
20070612005814,2007-06-12,1815918227960176043016,65.00
20070612005814,2007-06-12,1815919623710176043016,182.34
20070612005814,2007-06-12,1815931096710176043016,25.00

RobSampson

Ok, we'll have to make it less dynamic to be able to specifically format certain columns, but try this. It sets Columns A and C to General text format, then after it opens, it sets them to Number format. When they are in General format, it sets them to scientific notation. Number format seems to work.
'==================
strFileOutputPath = Replace(Wscript.ScriptFullName, wscript.ScriptName, "") & "LeadingZeroTest2.txt"
strDataTypes = "Array("
strDataTypes = strDataTypes & "Array(1, 1)"            ' Column 1, General Format
strDataTypes = strDataTypes & ", Array(2 , 2)"      ' Column 2, Text Format
strDataTypes = strDataTypes & ", Array(3 , 1)"      ' Column 3, General Format
strDataTypes = strDataTypes & ", Array(4 , 2)"      ' Column 4, Text Format
strDataTypes = strDataTypes & ")"
Const xlDelimited = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText strFileOutputPath,,,xlDelimited,,,False,False,True,,,,Eval(strDataTypes)
'Set columns 1 and 3 to Number format
objExcel.ActiveSheet.Columns("A:A").NumberFormat = "0"
objExcel.ActiveSheet.Columns("C:C").NumberFormat = "0"
'==================

Regards,

Rob.

WatchoutMac

ASKER

Thanks Rob,

This is where I got with my original VBScript. The first column "id" looks good but the "item_id" column puts all zero's after the 15th digit.

This is what I get before and after:

BEFORE:
Item Report,,,
id,merchant_date,item_id,amount
20070612005814,2007-06-12,1815918227960176043016,65.00
20070612005814,2007-06-12,1815919623710176043016,182.34
20070612005814,2007-06-12,1815931096710176043016,25.00

AFTER:
Item Report
id      merchant_date      item_id      amount
20070612005814      6/12/2007      1815918227960170000000      65
20070612005814      6/12/2007      1815919623710170000000      182.34
20070612005814      6/12/2007      1815931096710170000000      25

See the item_id is not showing the full number. I don't know if it's because the number is too large or if this is more of an excel question. What do you think?

RobSampson

You're right, I'm sorry, I didn't notice that it changed the number. I cannot find any information on being able to work with numbers greater than 15 digits for any mathematical purposes. The only way to display them is in a "Text" format. Even in a macro, if you add two of these large numbers together, it produces a scientific notation formatted number.

Any number that is longer that 12 digits is going to have to remain as "Text" format, and you won't be able to do any calculations with those.

Rob.

WatchoutMac

ASKER

I agree with you there, I guess the 'item_id" can be text but the question is how do I convert it to text?

I'm thinking I should just read through the csv file, loop through all the linkes and re-write the file enclosing the "id" and "item_id" columns between double quotes with a begining = sign. That way I think excel will display the original number.

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WatchoutMac

ASKER

That manually import seemed to work. I was hoping to use a VBScript to do this for me and only format the 2 columns to text not all of them. I guess it's either all the columns or none right?

WatchoutMac

ASKER

Thanks for your help Rob. I'm accepting you last solution because having all the columns as text is better than that scientific notation. Thanks again Rob

RobSampson

Yeah, sorry about that. Text seems to be the only way to deal with it in a readable format.
It doesn't necessarily have to be all or none in Text format though, you really only need Text format for cases such as keeping leading zeros, or in this case, numbers that are too long. The other formats can just be General, as in the case below (taking the code from a couple of posts ago), setting Columns 2 and 4 to General upon import:
strDataTypes = strDataTypes & "Array(1, 2)" ' Column 1, Text Format
strDataTypes = strDataTypes & ", Array(2 , 1)" ' Column 2, General Format
strDataTypes = strDataTypes & ", Array(3 , 2)" ' Column 3, Text Format
strDataTypes = strDataTypes & ", Array(4 , 1)" ' Column 4, General Format

But yes, setting the number fields to Text seems to be the only way to go.

Regards,

Rob.