Solved

CSV To EXCEL CONVERSION

Posted on 2006-07-08
17
535 Views
Last Modified: 2013-11-13
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.application")
Set ex2 = ex1.Workbooks.Open("c:\a.csv")

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
0
Comment
Question by:bvmittal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
17 Comments
 
LVL 15

Expert Comment

by:bpmurray
ID: 17064977
Try this:

Set ex2 = ex1.Workbooks.Open("c:\a.csv", 1, False, 2)
0
 
LVL 2

Expert Comment

by:cwile
ID: 17064978
Try this:

    Dim ex1 As Excel.Application
    Dim ex2 As Excel.Workbook
    ex1 = CreateObject("excel.application")
    ex2 = ex1.Workbooks.Open("C:\a.csv", , , , , , , , 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.
0
 
LVL 17

Expert Comment

by:jasonsbytes
ID: 17067078
Have you tried using a comma instead of a ; for the delimeter?
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:bvmittal
ID: 17068521
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
0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17068603
Did you try my solution? It explicitly addresses CSV import.
0
 
LVL 15

Expert Comment

by:Raisor
ID: 17069754
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
0
 
LVL 15

Expert Comment

by:bpmurray
ID: 17069780
You can. of course explicitly name the parameters too:

ex1.Workbooks.Open FileName:="c:\a.csv", Format:=xlCSV, Delimiter:=","
0
 
LVL 15

Expert Comment

by:Raisor
ID: 17069798
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
0
 

Author Comment

by:bvmittal
ID: 17074246
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
0
 
LVL 15

Accepted Solution

by:
Raisor earned 84 total points
ID: 17075709
Hi,

Have you tried yet?:

    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

... placing:

test1;test2;test3
test4;test5;test6

in a file named: "C:\test.csv"

... and if you tried, what went wrong?

As I've stated ... I've tested the code on my systems and it perfectly worked the way you'd asked for ...


Best regards,
Raisor
0
 
LVL 2

Assisted Solution

by:cwile
cwile earned 83 total points
ID: 17075945
I misread your original post.
Here is something quick I sorta thought up that you may wish to try:
Need the IMPORTS SYSTEM.IO at the beginning. Could convert the targets and what not to variables if you'd like. When it asks to save the file, just make sure that the file type is set to .xls and not .csv and it should convert the semi-colons to commas and then save fine for excel format.

Dim ex1 As Excel.Application
    Dim ex2 As Excel.Workbook
    Dim slines As String
    Dim target As String = "c:\a.csv"
    Dim x As Integer = 0

    TextBox1.Visible = False '// hide the textbox

    For Each slines In File.ReadAllLines(target)
      If x > 0 Then '// if you don't want the column headers
        TextBox1.Text = TextBox1.Text & Replace(slines, Chr(59), Chr(44)) & vbCrLf
      End If
      x = x + 1
    Next
    'TextBox1.Visible = True

    '// creat temp file to store new data
    File.WriteAllText("c:\temp.csv", TextBox1.Text)


    ex1 = CreateObject("excel.application")
    ex2 = ex1.Workbooks.Open("C:\temp.csv", , , , , , , , Chr(44))
    ex1.Visible = False
    ex2.SaveAs("c:\bvm1.xls")
    ex1.Quit()

    '// delete temp file
    File.Delete("c:\temp.csv")
0
 
LVL 2

Expert Comment

by:cwile
ID: 17076060
forgot to add that you need to make sure that the textbox's multiline is set to true.
0
 
LVL 15

Assisted Solution

by:bpmurray
bpmurray earned 83 total points
ID: 17076676
By the way, you'll want to save and close correctly - try:
        ex2.SaveAs("c:\temp\zzz.xls", Excel.XlFileFormat.xlWorkbookNormal)
        ex2.Close(False)
0
 
LVL 15

Expert Comment

by:Raisor
ID: 17228778
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ejb wildfly example 2 75
VB.NET 2008 Publish Error 2 44
Infrastructure development vs software development 3 82
C# Windows Form Navigation - Total Beginner 9 51
This is an explanation of a simple data model to help parse a JSON feed
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question