bvmittal
asked on
CSV To EXCEL CONVERSION
Dear Sir,
I have a CSV (delimited character is ;) file and want to convert into EXCEL file using a VB program directly . For this I written following code :
Dim ex1 As Excel.Application
Dim ex2 As Excel.Workbook
Set ex1 = CreateObject("excel.applic ation")
Set ex2 = ex1.Workbooks.Open("c:\a.c sv")
ex1.Visible = False
ex2.SaveAs ("c:\bvm1.xls")
ex1.Quit
End Sub
My problem is that , I got the file in EXCEL , but delimeter characters are appearing in EXCEL file and no column wise seperation as per Delimeter character is made .
Kindly guide me with a sample code how to achieve the same .
Rgds
B Mittal
I have a CSV (delimited character is ;) file and want to convert into EXCEL file using a VB program directly . For this I written following code :
Dim ex1 As Excel.Application
Dim ex2 As Excel.Workbook
Set ex1 = CreateObject("excel.applic
Set ex2 = ex1.Workbooks.Open("c:\a.c
ex1.Visible = False
ex2.SaveAs ("c:\bvm1.xls")
ex1.Quit
End Sub
My problem is that , I got the file in EXCEL , but delimeter characters are appearing in EXCEL file and no column wise seperation as per Delimeter character is made .
Kindly guide me with a sample code how to achieve the same .
Rgds
B Mittal
Try this:
Dim ex1 As Excel.Application
Dim ex2 As Excel.Workbook
ex1 = CreateObject("excel.applic ation")
ex2 = ex1.Workbooks.Open("C:\a.c sv", , , , , , , , Chr(44))
ex1.Visible = False
ex2.SaveAs("c:\bvm1.xls")
ex1.Quit()
It asks you to save over bvm1.xls, if you say choose to overwrite it will make the .xls file open in the respected columns, without commas.
Dim ex1 As Excel.Application
Dim ex2 As Excel.Workbook
ex1 = CreateObject("excel.applic
ex2 = ex1.Workbooks.Open("C:\a.c
ex1.Visible = False
ex2.SaveAs("c:\bvm1.xls")
ex1.Quit()
It asks you to save over bvm1.xls, if you say choose to overwrite it will make the .xls file open in the respected columns, without commas.
Have you tried using a comma instead of a ; for the delimeter?
ASKER
As suggested by Cwile, I tried but it is not working . The generated XLS file still has Delimeter Character and Excel is not able to open the file as a true xls file , rather it identifies it as a csv file only .
Rgds
B V Mittal
Rgds
B V Mittal
Did you try my solution? It explicitly addresses CSV import.
Hi,
Here's some VB Code that I'd suggest:
Dim x
Dim MyString As String
Dim Newstring As String
Dim Oneletter
ConvertedName = "C:\result.xls"
sSourceFile = "C:\test.csv"
Open ConvertedName For Output As #2
Open sSourceFile For Input As #1
Do While Not EOF(1)
Input #1, MyString
For x = 1 To Len(MyString)
Oneletter = Mid(MyString, x, 1)
If Asc(Oneletter) = 59 Then
Oneletter = vbTab
End If
DoEvents
Newstring = Newstring & Oneletter
Next x
Print #2, Newstring
Newstring = ""
DoEvents
Loop
Close #1
Close #2
I've tested it at my systems and it worked ... if does not work at your systems please let me know!
Best regards,
Raisor
Here's some VB Code that I'd suggest:
Dim x
Dim MyString As String
Dim Newstring As String
Dim Oneletter
ConvertedName = "C:\result.xls"
sSourceFile = "C:\test.csv"
Open ConvertedName For Output As #2
Open sSourceFile For Input As #1
Do While Not EOF(1)
Input #1, MyString
For x = 1 To Len(MyString)
Oneletter = Mid(MyString, x, 1)
If Asc(Oneletter) = 59 Then
Oneletter = vbTab
End If
DoEvents
Newstring = Newstring & Oneletter
Next x
Print #2, Newstring
Newstring = ""
DoEvents
Loop
Close #1
Close #2
I've tested it at my systems and it worked ... if does not work at your systems please let me know!
Best regards,
Raisor
You can. of course explicitly name the parameters too:
ex1.Workbooks.Open FileName:="c:\a.csv", Format:=xlCSV, Delimiter:=","
ex1.Workbooks.Open FileName:="c:\a.csv", Format:=xlCSV, Delimiter:=","
Hi,
I might add that I've placed:
test1;test2;test3
test4;test5;test6
in a file named "C:\test.csv"
... and it turned out to be well formated "C:\result.xls"
;-))
Best regards,
Raisor
I might add that I've placed:
test1;test2;test3
test4;test5;test6
in a file named "C:\test.csv"
... and it turned out to be well formated "C:\result.xls"
;-))
Best regards,
Raisor
ASKER
As suggested by Cwile , I tried and incase Delimeter Character used is "," then I get proper XLS output file generated . But incase Delimeter is ";" then output XLS file has all delimeter character in it and not properly formated .
Kindly help me .
Regards
B V Mittal
Kindly help me .
Regards
B V Mittal
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
forgot to add that you need to make sure that the textbox's multiline is set to true.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
I haven't tested other approaches but mine ... mine worked out on my systems and the solution did what bvmittal was asking for (if I understood his question well) ... if it didn't work out for bvmittal ... well, then I'm sorry ;-))
Best regards,
Raisor
I haven't tested other approaches but mine ... mine worked out on my systems and the solution did what bvmittal was asking for (if I understood his question well) ... if it didn't work out for bvmittal ... well, then I'm sorry ;-))
Best regards,
Raisor
Set ex2 = ex1.Workbooks.Open("c:\a.c