Sunday, March 23, 2008

Exporting ListView to CSV File in VB.NET

I have posted an article on copying a ListView to the Windows Clipboard, but I have also found it useful to export a ListView directly to a .CSV file (comma separated value file). The process to go from a ListView to the clipboard or to a CSV file is similar.

There are number of rules for creating a CSV file. Here is a good place to look if you want to see them all. I have simplified the process to a few rules that will work for most cases. I put quotes around all values and column names. Technically, this is not required, but by putting them around all cases eliminates the need to determine if you need them or not. I also replace any quote in the data with a double quote.

Public Function ExportListViewToCSV(ByVal filename As String, ByVal lv As ListView) As Boolean

Try

' Open output file
Dim os As New StreamWriter(filename)

' Write Headers

For i As Integer = 0 To lv.Columns.Count - 1
' replace quotes with double quotes if necessary
os.Write("""" & lv.Columns(i).Text.Replace("""", """""") & """,")
Next

os.WriteLine()

' Write records
For i As Integer = 0 To lv.Items.Count - 1
For j As Integer = 0 To lv.Columns.Count - 1
os.Write("""" & lv.Items(i).SubItems(j).Text.Replace("""", """""")+ """,")
Next

os.WriteLine()

Next

os.Close()

Catch ex As Exception
' catch any errors
Return False
End Try

Return True

End Function


You may want to get the file name from the user and automatically open the file in Excel too. Here is an example of how to do that:


Public Sub TestExportToCSV()

Dim dlg As New SaveFileDialog
dlg.Filter = "CSV files (*.CSV)|*.csv"
dlg.FilterIndex = 1
dlg.RestoreDirectory = True

If dlg.ShowDialog = Windows.Forms.DialogResult.OK Then

If ExportListViewToCSV(dlg.FileName, ListView1) Then

Process.Start(dlg.FileName)

End If

End If

End Sub

11 comments:

Anonymous said...

Thanks I've been looking for code to export ListView for ages!!

Thanks again.

Anonymous said...

pretty good but under
'write records you need to start with:

for i as integer = 1 to lv.items.count -1

srego said...

If we start the loop at 1 instead of 0, you would miss the first row of the listview.

Anonymous said...

Why does the 'StreamWriter' in my VB have the blue line error that says StreamWriter is not defined??

I'm using VB 2008 and trying to export the data from ListView1 to CSV file.

srego said...

You need the following Import at the top of the app:

Imports System.IO

Anonymous said...

Thanks Sir. I really appreciate your work. It's really help me.

I have a question :
How I add a piece of code that allow me to choose different directory to save the file?

I really need your help sir.

thanks

srego said...

Try this posting to see how to select the filename to save the file as:

http://dotnetref.blogspot.com/2011/07/selecting-files-to-open-or-save-in.html

Smart Future said...

hi buddy,

I try to following this step to export listview table to csv but i have a problem. I have 1 button to proses exportfile on the listview but when 1 click i found error..
please help me to fix it ..

picture error
http://i53.tinypic.com/108hk6c.png

thank you' i hope u can reply my message

srego said...

It should be:

"CSV files (*.CSV)|*.csv"

sorry, formatting snafu.

Smart Future said...

perfectly, it's really help me ..

I love this tutorial
thank you so much .. :)

Anonymous said...

Could anyone to tell me how about using vb 6?