Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

FREE 12 month online training for ASP.NET & MS Expression Studio and a Free copy of MS Expression Web with Windows Server Purchase
How to convert an Excel file to CSV format using VB.Net, ASP.Net.
Download Source Code : 1418_GetCellValuesJQuery.zip

A Visual Basic.Net console application can be used to test this method. It reads an Excel file and converts the data into CSV format.

Sub ExcelToCVS()

    Dim StrConn As String

    Dim DA As New OleDbDataAdapter

    Dim DS As New DataSet

    Dim DR As OleDbDataReader

    Dim Str As String

    Dim ColumnCount As Integer

    Dim OuterCount As Integer

    Dim InnerCount As Integer

    Dim RowCount As Integer

 

    StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/Sheet1.xls;Extended Properties=Excel 8.0;"

    Dim objConn As New OleDbConnection(StrConn)

    Try

      objConn.Open()

      If objConn.State = ConnectionState.Closed Then

        Console.Write("Connection cannot be opened")

      Else

        Console.Write("WelCome")

      End If

    Catch ex As Exception

      Console.Write(ex.ToString)

      Exit Sub

    End Try

    Dim objCmd As New OleDbCommand("Select * from [Sheet1$]", objConn)

    objCmd.CommandType = CommandType.Text

 

    Dim ObjStreamWriter As StreamWriter

    ObjStreamWriter = New StreamWriter("D:\New.CSV")

 

 

    Dim Count As Integer

    Count = 0

    Try

      DA.SelectCommand = objCmd

      DA.Fill(DS, "XLData")

    Catch ex As Exception

      Console.Write(ex.ToString)

    End Try

 

    RowCount = DS.Tables(0).Rows.Count

    ColumnCount = DS.Tables(0).Columns.Count

 

    For OuterCount = 0 To RowCount - 1

      Str = ""

      For InnerCount = 0 To ColumnCount - 1

        Str &= DS.Tables(0).Rows(OuterCount).Item(InnerCount) & ","

      Next

      'Str &= ","

      ObjStreamWriter.WriteLine(Str)

    Next

    ObjStreamWriter.Close()

    Try

    Catch ex As Exception

      Console.Write(ex.ToString)

      Dim SW As New StreamWriter("D:\mylog.log")

      SW.WriteLine(ex.ToString)

      SW.Close()

    End Try

 

    objCmd.Dispose()

    objCmd = Nothing

    objConn.Close()

    objConn.Dispose()

    objConn = Nothing

 

  End Sub

 

 

Article Comments
This code doesn't read the first row (i.e filed names). The below data are in the input xls file.

name empid
ravi 1193
selva 1124
arasu 1101
arun 236

Posted on 10/17/2006 9:25:19 AM by Varma

It really helped me. Iam not able to access the first row.Can you please help me how to access the first row.

Posted on 1/11/2007 4:01:57 PM by Rajendra Prasad

I tried the same thing and I am missing the first row too. I wouldn't have thought that the above code would assume that the first row is a header.

Posted on 1/30/2007 11:31:40 AM by DINESH VORA

Hi,
I want to convert from csv file to xml. any idea.... if so please help me.

Posted on 5/10/2007 7:19:18 AM by Pravat

Hi ,
Could you please give me how to read a datatable and change it to CSV file in C_sharp.
Thank you very much
kibrom

Posted on 5/21/2007 12:24:44 PM by kibrom

Per your comments I am posting the solution to having the first row(column names) display

Sub ExcelToCSV()
Dim StrConn As String
Dim DA As New OleDbDataAdapter
Dim DS As New DataSet
Dim Str As String
Dim ColumnCount As Integer
Dim OuterCount As Integer
Dim InnerCount As Integer
Dim RowCount As Integer

StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Excel.xls;Extended Properties=Excel 8.0;"
Dim objConn As New OleDbConnection(StrConn)
Try
objConn.Open()
If objConn.State = ConnectionState.Closed Then
Console.Write("Connection cannot be opened")
Else
Console.Write("WelCome")
End If
Catch ex As Exception
Console.Write(ex.ToString)
Exit Sub
End Try
Dim objCmd As New OleDbCommand("Select * from [Sheet1$]", objConn)
objCmd.CommandType = CommandType.Text

Dim ObjStreamWriter As IO.StreamWriter = New IO.StreamWriter("C:/NewFile.csv")

Try
DA.SelectCommand = objCmd
DA.Fill(DS, "XLData")
Catch ex As Exception
Console.Write(ex.ToString)
End Try

RowCount = DS.Tables(0).Rows.Count
ColumnCount = DS.Tables(0).Columns.Count

'------------This adds the column names to the csv file
'---------------Comment this out to ignore
Str = ""
For i As Integer = 0 To ColumnCount - 1
Str &= DS.Tables(0).Columns(i).ColumnName & ","
Next
ObjStreamWriter.WriteLine(Str)
'-------------------------------------------

For OuterCount = 0 To RowCount - 1
Str = ""
For InnerCount = 0 To ColumnCount - 1
Str &= DS.Tables(0).Rows(OuterCount).Item(InnerCount) & ","
Next
ObjStreamWriter.WriteLine(Str)
Next
ObjStreamWriter.Close()
Try
Catch ex As Exception
Console.Write(ex.ToString)
Dim SW As New IO.StreamWriter("C:\LogFile.log")
SW.WriteLine(ex.ToString)
SW.Close()
End Try

objCmd.Dispose()
objCmd = Nothing
objConn.Close()
objConn.Dispose()
objConn = Nothing

End Sub

Posted on 5/22/2008 1:47:40 PM by Brandon

this solution is in vb, can you post it in c#? I tried converting it with a converter and had errors on the following lines:

Str &= DS.Tables(0).Columns(i).ColumnName & ","

Str &= DS.Tables(0).Rows(OuterCount).Item(InnerCount) & ","

Thanks.

Posted on 9/17/2008 6:49:06 PM by Sara

THANK YOU

Posted on 12/30/2009 9:34:35 AM by chris

Hi,
How can I make this for multiples files?
Help me please!

Thanks.

Posted on 1/5/2010 6:31:09 AM by Eduardo

hi,
Brandon

first of all thanks for putting up solution your solution is working but bug is there if data is already with "," into mdb file then its taking same column data into different column into result CSV file.

Posted on 1/8/2010 5:23:15 PM by Samik

i want to know how to convert excel file to csv file through asp.net.
plz rep, if anyone know

Posted on 2/2/2010 5:06:29 AM by hiren

hiren:

just place this function in your code behind and call it.

Regards,
Faizan

Posted on 2/8/2010 12:46:59 PM by faizan ahmad

Thank you so much, this saves my problem of not having CR2008 in my PC (can't export report right into CSV from VB.NET).

:thumbs up:

Posted on 3/18/2010 9:02:31 AM by Johan

Do i have to have Excel install for this to work? Also if no Excel what would it require to run the Provider=Microsoft.Jet.OLEDB.4.0

Posted on 7/2/2010 1:09:40 PM by Jarrod

I see that. Brandon 's code is more better than comment's code but It is not resolve this problem.the first cell is not read true.

Posted on 8/31/2010 12:19:44 AM by Lipton

I see that. Brandon 's code is more better than comment's code but It is not resolve this problem.the first cell is not read true.

Posted on 8/31/2010 12:23:10 AM by Lipton

I have tried the reformatted code above but it certain cells on the header row do not wont to show. The rest of the rows are fine. Also how would you use a for each loop to do multiple files and move them to new subfolder

Posted on 12/2/2010 8:29:07 AM by Lineberger

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Convert text to image in Visual Basic.Net using Graphics namespace, in 10 easy steps.

Disclaimer - Privacy
© 2002-2014 DevASP.net