Reading from a csv file and writing it into xls file

Question

1.00/5 (1 vote)

See more:

In my vb.net winform application I need to read data from sample.csv file and write that data into demo.xls. I used Stream Reader classes to read the data from csv into a string. When I used StreatWriter class the entire row is coming in a single cell, which is not my aim. How to solve this issue?

sample.csv contains:
ID Name Sal Location
1 tom 345 USA
2 Martin 4533 NJ
3 Dell 3433 York

Can anyone suggests me please?

Posted 13-Jun-11 1:22am

Member 8001800

Updated 13-Jun-11 1:25am

v2

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2011-06-13T01:46:00

Solution 1

That's not a csv file. A CSV file is comma-delimited.

If it WERE an actual CSV file, excel will already openit as you expect it to.

Posted 13-Jun-11 1:46am

#realJSOP

Comments

Member 8001800 13-Jun-11 7:52am

That is Microsoft Excel Comma Seperated values File, which looks like xls file but not xls file. I tried to change the extension to .xls and tried to open. In xls every row that present in sample.csv comes in a single cell.WhenI tried to save as sample.xls file it is working as I want. So I want to automate the saveas functionality.

S Houghtelin · Answer 2 · 2011-06-13T01:47:00

Solution 2

VB

sInputArray = Split(input_csv_stiring, ",")

This assumes the csv file columns are in the correct order. write each array member to the correspondong Excel column.

[Update]
ioInput is an array with same number of elements as number of columns.

VB

While Not ioLine = ""
    ioLine = ioFile.ReadLine
    ioInput = Split(ioLine, ",")
    '--> Write to Excel Object for each line
End While

Also, to write to an Excel file you will need to interop with Excel from VB http://support.microsoft.com/kb/301982[^]

Posted 13-Jun-11 1:47am

S Houghtelin

Updated 13-Jun-11 2:46am

v3

Comments

Member 8001800 13-Jun-11 7:57am

Here is the code i have written: Can you suggest me where i need to add above code.

Dim ioFile As New System.IO.StreamReader("C:\sample.csv")
Dim ioLine As String
Dim ioLines As String
ioLine = ioFile.ReadLine
ioLines = "ID,Name,Sal,Location"
ioLines &= vbCrLf & ioLine
While Not ioLine = ""
ioLine = ioFile.ReadLine
ioLines = ioLines & vbCrLf & ioLine
End While
Dim ioWriter As New System.IO.StreamWriter("C:\new.csv")
ioWriter.WriteLine(ioLines)
ioFile.Close()
ioWriter.Close()

In this code I am trying to read from a sample.csv file and writing it into new.csv file. Could you please suggest the changes?

Wayne Gaylard · Answer 3 · 2011-06-13T02:25:00

Try this from your code in the comments

VB

Dim sb As StringBuilder = New StringBuilder()
       Using ioFile As New System.IO.StreamReader("sample.csv")
           While Not ioFile.EndOfStream
               sb.Append(ioFile.ReadLine & vbCrLf)
           End While
       End Using
       Using ioWriter As New System.IO.StreamWriter("new.csv")
           ioWriter.Write(sb.ToString())
       End Using

that should do what you want I think.

Reading from a csv file and writing it into xls file

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Reading from a csv file and writing it into xls file

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us