• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Script (CSV to EXCEL) data type Error

J

jvishnu46

New Member
Hello,

i am using VB script to convert the csv file into xls ( ofc 97 -2003) version. when i conver the file and open with excel i am getting error for the below data.

Example : Source CSV DATA: "423E2847" , Target (xls) its converted into scientfic form: 4.2e+13 like this.

i have text qualifier as double quotes and using text data type in VB still i am unable to get this corrected. for long numbers its showing correctly issue occurig only the text contains "E" letter in it. i want the source data to be displayed in excel as it is i.e 423E2847
Note: i want this to be in script . i coped the script i am using below.

Script:

'======================================
' Convert CSV to XLS
'
' arg1: source - CSV path\file
' arg2: target - Excel path\file
'======================================

srccsvfile = Wscript.Arguments(0)
tgtxlsfile = Wscript.Arguments(1)



'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(,"Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then '> 0
Set objExcel = CreateObject("Excel.Application")
End If

objExcel.Visible = false
objExcel.displayalerts=false



'Defining all columns as string
strDataTypes= "Array("
strDataTypes= strDataTypes & "Array(1,2)" ' Column 1 as Text format
strDataTypes= strDataTypes & "Array(2,2)"
strDataTypes= strDataTypes & "Array(3,2)"
strDataTypes= strDataTypes & "Array(4,2)"
strDataTypes= strDataTypes & ")"

objExcel.Workbooks.OpenText srccsvfile,,,1 _
, 1, True, False, False, True,False,False, _
, Eval(strDataTypes)
set wb=objExcel.ActiveWorkbook

'Adjust width of columns

'This code could be used to AutoFit a select number of columns
For intColumns = 1 To 17
objExcel.Columns(intColumns).AutoFit()
Next

'Make Headings Bold
objExcel.Rows(1).Font.Bold = TRUE

'Freeze header row
With objExcel.ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
objExcel.ActiveWindow.FreezePanes = True

'Add Data Filters to Heading Row
objExcel.Rows(1).AutoFilter

'set header row gray
objExcel.Rows(1).Interior.ColorIndex = 15



'Save Spreadsheet, 56 = Excel 2003-2007
wb.SaveAs tgtxlsfile, 56, , , , False
wb.Close

'Release Lock on Spreadsheet
objExcel.Quit()
 
I haven't finished looking at your VBS program, but my eye caught on strDataTypes, which you've set to "Array(Array(1,2)Array(2,2)Array(3,2)Array(4,2))". Don't you need commas between each of those Arrays inside the parens?
 
its just typo here , in my actual script there are commas strDataTypes = strDataTypes & ", Array(2 , 2)" ,
Can you please check entire script and suggest me how we can achieve the expected output.
 
Well, I thought maybe it would be something obvious :). Next question: What error do you get, and when? I hear you say it's when you open Excel, but I'm not sure I have the right picture. You run the script, I take it, and it seems to save the new .xls correctly and quit. Then you double-click on the new .xls, Excel fires up, Excel tries to open the .xls and ... at that point Excel finds it cannot open the workbook? What does it say, exactly?

Or is the error at some other point?
 
There is no error , when i open the excel after conversion , the data type is converted into Scientfic instead of 423E2847 this value displaying as 4.2e+123 something like this.
 
According to forum rules, edit your initial post and use the code tags, easy via the code icon in the 3 dots menu.​
​
Maybe just a bad code …​
Open the csv file like when you open a workbook and well answer to the Import Wizard, in particular for the columns data type.​
Once the result is the expected one, redo the same operations just activating before the Macro Recorder​
in order to get your code base with the appropriate import setup …​
​
 
Reposting the script:
Code: 
'======================================
' Convert CSV to XLS
'
' arg1: source - CSV path\file
' arg2: target - Excel path\file
'======================================

srccsvfile = Wscript.Arguments(0)
tgtxlsfile = Wscript.Arguments(1)



'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(,"Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then '> 0
Set objExcel = CreateObject("Excel.Application")
End If

objExcel.Visible = false
objExcel.displayalerts=false



'Defining all columns as string
strDataTypes= "Array("
strDataTypes= strDataTypes & "Array(1,2)" ' Column 1 as Text format
strDataTypes= strDataTypes & ", Array(2,2)"
strDataTypes= strDataTypes & ", Array(3,2)"
strDataTypes= strDataTypes & ", Array(4,2)"
strDataTypes= strDataTypes & ")"

objExcel.Workbooks.OpenText srccsvfile,,,1 _
, 1, True, False, False, True,False,False, _
, Eval(strDataTypes)
set wb=objExcel.ActiveWorkbook

'Adjust width of columns

'This code could be used to AutoFit a select number of columns
For intColumns = 1 To 17
objExcel.Columns(intColumns).AutoFit()
Next

'Make Headings Bold
objExcel.Rows(1).Font.Bold = TRUE

'Freeze header row
With objExcel.ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
objExcel.ActiveWindow.FreezePanes = True

'Add Data Filters to Heading Row
objExcel.Rows(1).AutoFilter

'set header row gray
objExcel.Rows(1).Interior.ColorIndex = 15



'Save Spreadsheet, 56 = Excel 2003-2007
wb.SaveAs tgtxlsfile, 56, , , , False
wb.Close

'Release Lock on Spreadsheet
objExcel.Quit()
 
Marc L said:
According to forum rules, edit your initial post and use the code tags, easy via the code icon in the 3 dots menu.​
​
Maybe just a bad code …​
Open the csv file like when you open a workbook and well answer to the Import Wizard, in particular for the columns data type.​
Once the result is the expected one, redo the same operations just activating before the Macro Recorder​
in order to get your code base with the appropriate import setup …​
​
Click to expand...
i want to run this script through autosys , so i cannot use the manual import wizard. i reposted the script as i cannot edit my intial post
 
BobBridges said:
Well, I thought maybe it would be something obvious :). Next question: What error do you get, and when? I hear you say it's when you open Excel, but I'm not sure I have the right picture. You run the script, I take it, and it seems to save the new .xls correctly and quit. Then you double-click on the new .xls, Excel fires up, Excel tries to open the .xls and ... at that point Excel finds it cannot open the workbook? What does it say, exactly?

Or is the error at some other point?
Click to expand...
There is no error , when i open the excel after conversion , the data type is converted into Scientfic instead of 423E2847 this value displaying as 4.2e+123 something like this.
 
Oh, I see what you're saying; it's a text value, such a "123E456", and you want it to be interpreted as a character string, but Excel insists on interpreting it as a numeric value 123e+456.

I'm not sure what's going wrong. I wrote a text input file:
Code: 
String,1,"123E45",6,String
String,1,"143E45",6,String2
String,1,"133E45",6,String3

...and manually read it in using data-to-text conversion; I had to specify Text for the third column or it converted it to numeric, as you said. Then I recorded a macro to do the same thing, and as far as I can tell it did it the same way you're doing it. Here's the statement it generated:
Code: 
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1)), _
    TrailingMinusNumbers:=True

That handled the third column as text, not numeric. So I don't know that I can come up with a fix for you. If I were doing this myself, I'd do it the hard way: I'd use the FileSytemObject to read the file line by line, parse out the pieces and fill in the worksheet that way, specifying Text format for the column that's giving you problems. But then I'm an old programmer from 'way back; I'm more comfortable doing things that way that relying on the TextToColumns method, which I've never used in VBA although I use it pretty routinely when I'm doing things by hand.
 
BobBridges said:
Oh, I see what you're saying; it's a text value, such a "123E456", and you want it to be interpreted as a character string, but Excel insists on interpreting it as a numeric value 123e+456.

I'm not sure what's going wrong. I wrote a text input file:
Code: 
String,1,"123E45",6,String
String,1,"143E45",6,String2
String,1,"133E45",6,String3

...and manually read it in using data-to-text conversion; I had to specify Text for the third column or it converted it to numeric, as you said. Then I recorded a macro to do the same thing, and as far as I can tell it did it the same way you're doing it. Here's the statement it generated:
Code: 
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1)), _
    TrailingMinusNumbers:=True

That handled the third column as text, not numeric. So I don't know that I can come up with a fix for you. If I were doing this myself, I'd do it the hard way: I'd use the FileSytemObject to read the file line by line, parse out the pieces and fill in the worksheet that way, specifying Text format for the column that's giving you problems. But then I'm an old programmer from 'way back; I'm more comfortable doing things that way that relying on the TextToColumns method, which I've never used in VBA although I use it pretty routinely when I'm doing things by hand.
Click to expand...
Can you please help me to rewrite/update my code using your method, like reading from file , line by line and writing it as excel.
 
jvishnu46 said:
i want to run this script through autosys , so i cannot use the manual import wizard. i reposted the script as i cannot edit my intial post
Click to expand...
As you can easily import manually the file on any Excel computer and just well answering to the Import Wizard​
you will have the correct code, that resolves the issue 99,999% of the time !​
As the direct import way is not via OpenText …​
​
jvishnu46 said:
There is no error , when i open the excel after conversion , the data type is converted into Scientfic instead of 423E2847 this value displaying as 4.2e+123 something like this.
Click to expand...
'Cause of the bad code which does not use any Excel import feature …​
Using OpenText as a source data, you must first define each column data format on the destination worksheet​
before copying the data from the source sheet (OpenText) to the destination worksheet.​
Less efficient than using an Excel import feature doing all the necessary at once just with an unique command …​
​
 
How can we define the destination worksheet data type , in array i am using as Array(1,2 ) - this will define the data as text. but how to define the destination data type?
 
Last edited by a moderator:
OK forget the copy​
but try first on an Excel computer on a local drive after rewriting the array part directly in the OpenText codeline​
without using Evaluate (superfluous here !) like in the VBA help sample Array(Array(1,1), Array(2, 2), …)​
'cause if the import setup is correct Excel does not convert anything to the scientist syntax …​
​
 
I confused Eval versus the powerful Excel function Evaluate - as here it's an Excel VBA forum ! -​
so you must very check what is the result of Eval in your VBScript code​
as like written in the VBA help the OpenText method expects an array of arrays …​
​
According to VBScript help and VBA help, Eval seems also useless here.​
​
Another concern is your On Error Resume Next codeline masking any error raising​
in particular in the OpenText codeline, who knows …​
​
So you must first try locally on an Excel computer in order to first write the appropriate code under VBA​
- easy via the Macro Recorder - and once it's achieved you can try on the same computer under a VBScript​
and once done with success, you should easily amend the VBScript under 'autosys' …​
​
 
Script is doing fine for everything else, except for just when the data occurs as "423E13234" with letter E in it. for some reason its not keeping the text as text but converting into scientific
 
Last edited by a moderator:
It's a common issue as you can see on Web, each time it's just a bad code for some reason, like a bad import column setup …​
​
Restart operating manually and if it works so it could be easily automatized just starting from the Macro Recorder.​
​
 
jvishnu46 said:
Can you please help me to rewrite/update my code using your method, like reading from file , line by line and writing it as excel.
Click to expand...

Looks like Mark L has some good ideas, and I wouldn't want to talk you out of using them. But I can give you a sort of basic structure for reading a file directly, sure. You'll have to adapt it to your own input.
Code: 
fnf = <full path and filename> 'let's say the variable fnf has the full filename

' We're going to use the FileSystemObject for manipulating files in Windows.  For documentation see
' https://docs.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/windows-scripting/hww8txat(v=vs.84)
Set ofs = CreateObject("Scripting.FileSystemObject")
If Not ofs.FileExists(fnf) then 'the FileExists method confirms that you have the right filename
  ' Blah, blah, blah
  End If

Set ofi = ofs.OpenTextFile(fnf, 1) 'OpenTextFile returns an object for reading the file; 1 means "for reading"
Do While Not ofi.AtEndOfStream 'The AtEndOfStream property indicates the end of the input file
  ld = ofi.ReadLine 'read the input file one line at a time

  ' The variable LD now contains one line of your input.  Insert statements here to break it up into pieces and put
  ' the pieces into the next row of your worksheet.

  Loop
ofi.Close 'close the file when you're done with it

Before you start putting those string-values-that-look-like-numbers into the worksheet, tell Excel that that column is to be text format, like this:
Code: 
ows.Columns(cStr).NumberFormat = "@"

...where ows is the worksheet object and cStr is whatever column number has that value that's giving you trouble. (This method is fresh in my mind because I've been writing something that's giving me the same problem, that is, strings that look to Excel like numbers.)

How you parse each line into pieces depends, of course, on what your input line looks like. And whether you write the pieces directly into the worksheet, or put them into an array and then dump the array into the worksheet, depends on how many rows you have. It's more trouble to create an array, load up the array and then set the Excel range equal to that array. But if you're working with more than a couple hundred rows you'll find doing it that way is pretty slow; if you put it into an array and then load the array you'll be able to do tens of thousands of lines before you run into the same problem.
 
Bob,​
​
using FSO - I often avoid it until VBA can't do but here VBA can - you can use its method ReadAll​
and parse each 'line' to an array via the VBA function Split according to the end of line sequence …​
​
 
Sometimes I use ReadAll, but not often. I've been programming for decades, and I think my mental arteries are hardened into a pattern. What delimiter do you use to split the lines into an array? xlCrLf, I guess?
 
I check first the EOL sequence under NotePad++ for example then I use the appropriate VBA constant : vbCr, vbCrLf or vbLf …​
Or for variable EOL the universal replace way to parse finally only on vbLf.​
​
 
BobBridges said:
Looks like Mark L has some good ideas, and I wouldn't want to talk you out of using them. But I can give you a sort of basic structure for reading a file directly, sure. You'll have to adapt it to your own input.
Code: 
fnf = <full path and filename> 'let's say the variable fnf has the full filename

' We're going to use the FileSystemObject for manipulating files in Windows.  For documentation see
' https://docs.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/windows-scripting/hww8txat(v=vs.84)
Set ofs = CreateObject("Scripting.FileSystemObject")
If Not ofs.FileExists(fnf) then 'the FileExists method confirms that you have the right filename
  ' Blah, blah, blah
  End If

Set ofi = ofs.OpenTextFile(fnf, 1) 'OpenTextFile returns an object for reading the file; 1 means "for reading"
Do While Not ofi.AtEndOfStream 'The AtEndOfStream property indicates the end of the input file
  ld = ofi.ReadLine 'read the input file one line at a time

  ' The variable LD now contains one line of your input.  Insert statements here to break it up into pieces and put
  ' the pieces into the next row of your worksheet.

  Loop
ofi.Close 'close the file when you're done with it

Before you start putting those string-values-that-look-like-numbers into the worksheet, tell Excel that that column is to be text format, like this:
Code: 
ows.Columns(cStr).NumberFormat = "@"

...where ows is the worksheet object and cStr is whatever column number has that value that's giving you trouble. (This method is fresh in my mind because I've been writing something that's giving me the same problem, that is, strings that look to Excel like numbers.)
i
How you parse each line into pieces depends, of course, on what your input line looks like. And whether you write the pieces directly into the worksheet, or put them into an array and then dump the array into the worksheet, depends on how many rows you have. It's more trouble to create an array, load up the array and then set the Excel range equal to that array. But if you're working with more than a couple hundred rows you'll find doing it that way is pretty slow; if you put it into an array and then load the array you'll be able to do tens of thousands of lines before you run into the same problem.
Click to expand...
i tried the numberformat thing but its still converted into scientfic form. i tried this with open text after loading the data into excel
 
Did you remember to change the cell format to text before you pasted a value into it? Because that's what caused me the problem, and it took me a while to figure it out.

Wait, "with open text" and "after loading the data into Excel"? I'm not sure what you're saying, but it doesn't sound like what I meant you to do. Tell me what you did, step by step.

Or just do this: In a new Excel worksheet, type "5e5" into a cell and hit <Enter>. Notice that Excel interprets it as 500000. Change the format of that cell to Text, and see that the value is still 500000; it's too late, you see, because Excel thinks it's a number; it no longer remembers the keystrokes you typed in.

In another cell, change the format to Text, then type in "5e5". Note that this time it displays the keystrokes as a character string, just as you wanted. This is what you have to get your program to do: Set the NumberFormat property to "@" before you put any value into it.
 
Hi Bob,

Let me give you the whole steps .
CSV file is generated by ETL application. its moved to windows server and there using the VB script converts into xls ( 2003) format and fed to some legacy application. there is no manual intervention in this process everything run through autosys.

i tried your method, when open the csv file with excel its converting it to exponential and unable to revert that. so in that case before converting it into excel what ever the method its reading is converting into exponential.

i tried giving a single quote in the data with this its converted properly but the single quote also exist as data. if i tried to remove that manually instantly its converting to exponential

in open text even though i am using target as text , with the number formatting also , all the columns in excel is showing as general and this cell as scientific. i am unable to figure out how to get rid of this issue.
 
Last edited by a moderator:
You must log in or register to reply here.
Back
Top