Writing a ListView to a CSV File

[arithmetica]

Hey folks,
I found the following links to fill a listview and then write it to a CSV.

http://www.freevisualbasic2008source...fillalistview/
http://www.freevisualbasic2008source...iewtoacsvfile/

And I modified the codes and created the project which is attached to this post.

The project works OK but my only problem is that when it creates the CSV file, columns are not created in excel. For example if I expect the data to be placed in A1, B1 and C1 cells, the data will all be placed in A1.

Can anyone solve my problem?

Thanks for your help.

[keystone_paul]

Can you show your code - many people (me included) won't download your project but if you post the relevant part of code within [ CODE ] and [ /CODE ] tags we can look at it on here.

[arithmetica]

Here:

Imports System
Imports System.IO
Imports System.Collections
Public Class Form1
    Private MyListText(10) As String
    Private MyListItem As ListViewItem
    Private MyCSVWriter As System.IO.StreamWriter
    Private MyFileName As String
    Private MyNewLine As String
    Private MyText1 As String
    Private MyText2 As String
    Private MyText3 As String

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ListView1.Clear() '.....Clear the existing data from the listview 
        ListView1.View = View.Details '.....IMPORTANT - Set the view to Details to allow tabulation (column mode) 
        '.....Add ListView Column sizes and Titles 
        ListView1.Columns.Add("Title1", 200, HorizontalAlignment.Left)
        ListView1.Columns.Add("Title2", 200, HorizontalAlignment.Left)
        ListView1.Columns.Add("Title3", 200, HorizontalAlignment.Left)
        MyListText(0) = "Text1"
        MyListText(1) = "Text2"
        MyListText(2) = "Text3"
        MyListItem = New ListViewItem(MyListText)
        '.....Display the record in the ListViewBox 
        ListView1.Items.Add(MyListItem)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        WriteListViewToCSVFile()
    End Sub

    Sub WriteListViewToCSVFile()
        Dim CSVWriter As New StreamWriter("C:\Test.csv")
        For i As Integer = 0 To Me.ListView1.Items.Count - 1
            For j As Integer = 0 To Me.ListView1.Columns.Count - 1
                CSVWriter.Write(Me.ListView1.Items(i).SubItems(j).Text)
            Next
            CSVWriter.WriteLine()
        Next
        CSVWriter.Close()
    End Sub

End Class

[keystone_paul]

Well CSV stands for comma-separated values, ie the columns are delineated by commas and rows are delineated by carriage returns.

You code doesn't put in any commas, just carriage returns (when you call WriteLine).

Try replacing your inner loop with this :

            For j As Integer = 0 To Me.ListView1.Columns.Count - 1
                CSVWriter.Write(Me.ListView1.Items(i).SubItems(j).Text) & ", "
            Next

NB - its not entirely your fault - the two sample bits of code you linked to are both rubbish!

[arithmetica]

Well, I did that. But nothing changed. All the data were placed in the A1 cell with commas shown.

[keystone_paul]

Can you post an example of the actual file

[arithmetica]

That's the CSV file the project creates.

[arithmetica]

BTW, why do you think the codes are rubbish? Those were only useful codes I found!

[keystone_paul]

BTW, why do you think the codes are rubbish? Those were only useful codes I found!

Why are they rubbish? Because they will never do anything other than write a single entry on each line - exactly as you have found.

I asked if you would post the file, not a screenshot of how excel is interpreting the file...

I suspect the problem is that the entire line may be enclosed in quotes.

[arithmetica]

Sorry!
It's attached.

[keystone_paul]

Change the loop to

            For j As Integer = 0 To Me.ListView1.Columns.Count - 1
                CSVWriter.Write(Chr(34) & (Me.ListView1.Items(i).SubItems(j).Text) & Chr(34) & ", ")
            Next

I hadn't realised that you'd basically taken the exact code from those samples. Like I say they are very poor examples of how to write CSV files as they don't actually produce any columns - it might as well be a straight text file.

[keystone_paul]

Can you post the contents of the file rather than attaching archives - like I say I've no interest in downloading files.

You can get the contents of the file by right-clicking and selecting Open with... and selecting Notepad.

Even without the quotes the file produced with my first amendment worked fine on my machine.

[arithmetica]

Ah, that's how it looks like in Notepad:

"Text1", "Text2", "Text3",

[keystone_paul]

That should be absolutely fine. For me using Excel it opens like this :

[Siddharth Rout]

After you write to the Excel file try this in the End where WB is your workbook Object, Sheets(1) is the sheet you are working with... Please change them to relevant names...

WB.Sheets(1).Range("A1").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

[keystone_paul]

After you write to the Excel file try this in the End where WB is your workbook Object, Sheets(1) is the sheet you are working with... Please change them to relevant names...

WB.Sheets(1).Range("A1").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

Huh?

Have you read the question? He isn't manipulating anything in Excel, just writing out a CSV file.

[arithmetica]

Well I don't understand what the problem is when the contents look alright in Notepad.

[Siddharth Rout]

Huh?

Have you read the question? He isn't manipulating anything in Excel, just writing out a CSV file.

I went on the basis of this...

The project works OK but my only problem is that when it creates the CSV file, columns are not created in excel. For example if I expect the data to be placed in A1, B1 and C1 cells, the data will all be placed in A1.

Can anyone solve my problem?

She is writing to a CSV file which is manipulating Excel in this case. What My code does is using the facility which is called "Text To Columns"...

[arithmetica]

?

Have you read the question? He isn't manipulating anything in Excel, just writing out a CSV file.

Actually She..

[keystone_paul]

Apologies for the gender mixup!

The only thing I can think of is if you are in Iran does Windows have a different list separator than comma?

[arithmetica]

Well I don't know. I even tried out another CSV files that I've got but for none Excel produced columns..

[keystone_paul]

She is writing to a CSV file which is manipulating Excel in this case. What My code does is using the facility which is called "Text To Columns"...

Writing out a CSV file is not necessarily manipulating excel - it just so happens that excel is the tool being used to view the output.

Using the method you suggest is entirely architecturally more complex and relies on the machine generating the csv file having Excel installed.

There is no workbook object to reference in this example.

[keystone_paul]

Well I don't know. I even tried out another CSV files that I've got but for none Excel produced columns..

OK so its not an issue with your code as such if all CSV files behave the same way, I'd imagine that it is to do with regional settings either in Windows or in Excel.

Try this instead :

            For j As Integer = 0 To Me.ListView1.Columns.Count - 1
                CSVWriter.Write((Me.ListView1.Items(i).SubItems(j).Text) & System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator)
            Next

[Siddharth Rout]

Writing out a CSV file is not necessarily manipulating excel - it just so happens that excel is the tool being used to view the output.

Agreed and that is the reason why I mentioned

She is writing to a CSV file which is manipulating Excel in this case.

Using the method you suggest is entirely architecturally more complex and relies on the machine generating the csv file having Excel installed.

There is no workbook object to reference in this example.

The workbook object was an example. There is still data which is being outputted to Range("A1")....

The only condition, the above code will fail is if in Iran they have something else for ","....

Edit: I don't have access to vb.net at the moment but later in the day, I will amend the above code and give you an exact example....

[arithmetica]

OK so its not an issue with your code as such if all CSV files behave the same way, I'd imagine that it is to do with regional settings either in Windows or in Excel.

Try this instead :

            For j As Integer = 0 To Me.ListView1.Columns.Count - 1
                CSVWriter.Write((Me.ListView1.Items(i).SubItems(j).Text) & System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator)
            Next

It worked for me!
Sounds like the problem was the listseperator in my windows.
Thanks a lot!

[keystone_paul]

The only condition, the above code will fail is if in Iran they have something else for ","....

There's absolutely no need to do it using references to excel - it introduces unnecessary complexity and dependencies.

I've amended the code above so it will work with whatever the comma separator

           For j As Integer = 0 To Me.ListView1.Columns.Count - 1
                CSVWriter.Write((Me.ListView1.Items(i).SubItems(j).Text) & System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator)
            Next