-
1 Attachment(s)
Writing a ListView to a CSV File
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. :)
-
Re: Writing a ListView to a CSV File
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.
-
Re: Writing a ListView to a CSV File
Here:
Code:
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
-
Re: Writing a ListView to a CSV File
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 :
Code:
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!
-
Re: Writing a ListView to a CSV File
Well, I did that. But nothing changed. All the data were placed in the A1 cell with commas shown.
-
Re: Writing a ListView to a CSV File
Can you post an example of the actual file
-
Re: Writing a ListView to a CSV File
-
Re: Writing a ListView to a CSV File
BTW, why do you think the codes are rubbish? Those were only useful codes I found!
-
Re: Writing a ListView to a CSV File
Quote:
Originally Posted by
arithmetica
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.
-
1 Attachment(s)
Re: Writing a ListView to a CSV File
-
Re: Writing a ListView to a CSV File
Change the loop to
Code:
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.
-
Re: Writing a ListView to a CSV File
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.
-
Re: Writing a ListView to a CSV File
Ah, that's how it looks like in Notepad:
"Text1", "Text2", "Text3",
-
1 Attachment(s)
Re: Writing a ListView to a CSV File
That should be absolutely fine. For me using Excel it opens like this :
-
Re: Writing a ListView to a CSV File
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...
Code:
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
-
Re: Writing a ListView to a CSV File
Quote:
Originally Posted by
koolsid
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...
Code:
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.
-
Re: Writing a ListView to a CSV File
Well I don't understand what the problem is when the contents look alright in Notepad. :(
-
Re: Writing a ListView to a CSV File
Quote:
Originally Posted by
keystone_paul
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...
Quote:
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"...
-
Re: Writing a ListView to a CSV File
Quote:
Originally Posted by
keystone_paul
?
Have you read the question? He isn't manipulating anything in Excel, just writing out a CSV file.
Actually She..
-
Re: Writing a ListView to a CSV File
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?
-
Re: Writing a ListView to a CSV File
Well I don't know. I even tried out another CSV files that I've got but for none Excel produced columns..
-
Re: Writing a ListView to a CSV File
Quote:
Originally Posted by
koolsid
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.
-
Re: Writing a ListView to a CSV File
Quote:
Originally Posted by
arithmetica
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 :
Code:
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
-
Re: Writing a ListView to a CSV File
Quote:
Originally Posted by
keystone_paul
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
Quote:
She is writing to a CSV file which is manipulating Excel in this case.
Quote:
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....
-
Re: Writing a ListView to a CSV File
Quote:
Originally Posted by
keystone_paul
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 :
Code:
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
:thumb:
It worked for me!
Sounds like the problem was the listseperator in my windows.
Thanks a lot!
-
Re: Writing a ListView to a CSV File
Quote:
Originally Posted by
koolsid
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
Code:
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