How to convert an Excel file to CSV format using VB.Net, ASP.Net.

Search - Articles

Home
Dev Articles
Sample Chapters
Add a Listing
Contact

Dev Articles

ASP.NET (338)
C# (300)
SQL Server (53)
VB.NET (195)
Web Services (2)

Search Directory

ASP.Net AJAX
AJAX ToolKit
.NET Namespaces
Applications
Articles & Samples
Assembly & Controls
Community
Developer Sites
Downloads
Hosting Services
Introduction
Knowledge Base
Sample Chapters
WebCasts

ASP.NET

Applications
Articles & Samples
ASP.Net Sites
Assembly & Controls
Downloads
Errors & Bugs
Introduction
Knowledge Base
Sample Chapters
WebCasts

VB.Net

Articles & Samples
Downloads
Errors, Bugs & Fixes
Introduction
Knowledge Base
Sample Chapters
WebCasts

C-Sharp

Applications
Articles & Samples
C-Sharp Sites
Errors, Bugs & Fixes
Introduction
Knowledge Base
Sample Chapters
WebCasts

SQL Server

Applications
Articles & Samples
SQL Sites
Downloads
Errors, Bugs & Fixes
Introduction
Knowledge Base
Sample Chapters
WebCasts

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.

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) & ","

'Str &= ","

ObjStreamWriter.WriteLine(Str)

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

Varma

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

Rajendra Prasad

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

DINESH VORA

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

Pravat

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

kibrom

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

Brandon

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

Sara

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

chris

THANK YOU

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

Eduardo

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

Thanks.

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

Samik

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

hiren

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

faizan ahmad

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

Johan

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

Jarrod

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

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:19:44 AM by Lipton

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

Lineberger

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 :
	Send me an email about this article when other users post a Comment

<< Convert text to image in Visual Basic.Net using Graphics namespace, in 10 easy steps.

How to create, move and delete directory and validate existence of directory using System.IO namespace in VB.Net. >>