VBA Script (CSV to EXCEL) data type Error

[Marc L]

So read again post #16 as the Macro Recorder will give the correct setup if the one operating manually understands​

the need and well setup the Import Wizard, at kid level, worked each time I had to use it or to train beginners …​

​

Once the import works manually it can easily be automatized whatever just following the VBA help or using the Macro Recorder.​

Or as it's a common issue - I met it several times on forums - you can easily find solutions on Web …​

​

 

[BobBridges]

Sorry for the delay, J; I'm back now.

You say an ETL process collects the data, and then a VB script converts it into an .xls file. Then you write of opening the .csv—so does the VB script create an .xls or a .csv? Or put it this way: At what point is the .csv created that you are trying to open?

Here's the problem, if I understand it correctly: You have a .csv that includes one column with text values that can be understood as numerals in scientific notation. I've experimented and I can see no way to persuade Excel to treat such a column as text. The nature of the .csv format, as you no doubt already understand, is that it's plain text, so there's no way to tell Excel about any special handling for a particular column. And without knowing that, Excel insists on interpreting "123E7", even in double quotes, as 1.23E+9.

So it seems to me that you cannot deal with this by working with a .csv; you must have, or create, an .xls. I can think of three ways to do this:

1) You say that a VB script creates an .xls that is then used by a legacy app. You didn't say that the column is wrong in the .xls; so open the .xls rather than the .csv, and work from that.

2) Maybe you misspoke; maybe the VB script creates a .csv. If so, you can modify the script to create an .xls instead, so that the problem column can retain its text format despite the fact that it looks to Excel like a number.. Or you can copy that script and modify it to create an xls in addition to the .csv.

3) If for some reason neither of those options is available to you, then you have to write a VBA or VBS program that will read the .csv as text, and create an .xls from it. That's what my Friday 18:36 post is about.

 

[jvishnu46]

Sorry for the delay, J; I'm back now.

You say an ETL process collects the data, and then a VB script converts it into an .xls file. Then you write of opening the .csv—so does the VB script create an .xls or a .csv? Or put it this way: At what point is the .csv created that you are trying to open?

Here's the problem, if I understand it correctly: You have a .csv that includes one column with text values that can be understood as numerals in scientific notation. I've experimented and I can see no way to persuade Excel to treat such a column as text. The nature of the .csv format, as you no doubt already understand, is that it's plain text, so there's no way to tell Excel about any special handling for a particular column. And without knowing that, Excel insists on interpreting "123E7", even in double quotes, as 1.23E+9.

So it seems to me that you cannot deal with this by working with a .csv; you must have, or create, an .xls. I can think of three ways to do this:

1) You say that a VB script creates an .xls that is then used by a legacy app. You didn't say that the column is wrong in the .xls; so open the .xls rather than the .csv, and work from that.

2) Maybe you misspoke; maybe the VB script creates a .csv. If so, you can modify the script to create an .xls instead, so that the problem column can retain its text format despite the fact that it looks to Excel like a number.. Or you can copy that script and modify it to create an xls in addition to the .csv.

3) If for some reason neither of those options is available to you, then you have to write a VBA or VBS program that will read the .csv as text, and create an .xls from it. That's what my Friday 18:36 post is about.

BOB,

Thank you for your reply. legacy application accepts only xls and after ETL generates csv i am using vbscript to convert it into xls . i tried one work around its working as individual code but its not working when you put in my existing code.

i tried populating data as "='123E456" and this value in excel generated as ='123E456 if i replace the = with none its remaining as 123E456 in text format.

objExcel.Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "@"
    Selection.Replace What:="=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

i included this after the open text , its throwing me error as expected statement for replace command , could you please help me . i am not VBA guy trying to fix this .

 

[BobBridges]

Got it: Some ETL process (probably one you're not responsible for) generates a .csv, and you're the one writing the VBS to convert the .csv to .xls. In that case I think the only thing you can do is write the VBS to read the .csv as a text file, rather than let Excel read it directly.

Someone here may be able to tell us what I'm missing, but so far I have failed to get Excel to read a .csv containing a value such as "123E456" and to understand it as a string value rather than as a number. Since you cannot get Excel to do it while reading the .csv, your VBS will have to read the .csv itself—as a text file, as I said—and plug the values into the Excel worksheet. Here's how I would do it (in general):

1) Use FileSystemObject ("FSO") to read the file, line by line.
2) Divide each line into separate fields, by commas. You can probably use the Split function for that.
3) Create an array with the right number of columns and a sufficient number of rows, and fill in each value, row by row and line by line.
4) Start a new workbook and worksheet in Excel.
5) Tell Excel that the nth column is to be text.
6) Then pour the array from step 3 into the worksheet.

I'm confident you'll ask for help with that, and I don't mind—but I gotta go get something to eat first. Then I'll probably sit down to write such a program in VBS and post it here. But if you don't hear from me in an hour or so, you have my permission to bug me about it; I may just get distracted after I've eaten, answering other emails or something.

 

[BobBridges]

Got it! Here's a very bare-bones VBS program that reads a file as text and puts the values into a worksheet. You'll have to adjust it to your own circumstances, of course; the number of columns, which column is causing you the problem, perhaps the maximum number of rows, obviously the filename, all those details. And I used my own naming conventions, which you're sure to hate: Every programmer loves his own naming system and hates everyone else's, right?

dim arw(9999, 4) 'make enough rows and columns
set ofs = CreateObject("Scripting.FileSystemObject")
set ofi=ofs.OpenTextFile("C:\rhb\x.csv")
do while not ofi.atendofstream
  ld=ofi.readline
 
  ' Split the line into individual fields by comma and plug them into the worksheet array.
  arl=split(ld,",")
  rC=rC+1
  for jf=0 to ubound(arl,1)
    vf=arl(jf)
	if left(vf,1) = """" then vf=mid(vf,2,len(vf)-2) 'strip the quotes if present
    arw(rc,jf) = vf
	next

  loop

' Now put the results into a worksheet.
set oxl=CreateObject("Excel.Application")
set owb=oxl.workbooks.add
set ows=owb.activesheet
oxl.Windows(1).WindowState = -4137 'xlMaximized
oxl.visible=true
ows.columns(3).numberformat="@" '<- set the problem column to text ~before~ pasting
oxl.range(ows.cells(1,1),ows.cells(rc+1,5)).value=arw 'I had to go to row rC+1 because
  ' all arrays in VBS have a lower bound of 0, never 1.  It's different in VBA.

Oh, almost forgot to add: There's a statement inside the loop that strips the double quotes from each field, if they're there. Not sure whether your .csv has them, and whether it's appropriate to strip them in the case of every field; you may have to adjust a bit.

 

[BobBridges]

Oh, and you said you're not a VBS geek so be sure and ask questions about any part of that you don't fully understand. You want to become a VBS geek, after all, right?