Save data from a listbox to a .CSV file.

Save data from a listbox to a .CSV file.

• binod m paul

  hehe, no worries :-) best wishes and Merry Christmas
  
  

• djdotti

  ah!

  you know what we could do we could read each row of data and then write it to disk - would this be a better option for you

  
  
  

• Fippy

  I know what you mean.

  Well basically after each "item," you write Environment.NewLine to make a new line (new record) Question I have is, where are the fields (columns) coming from in the listbox

  
  
  

• Mike Berro

  how do you mean :-)

  a CSV file is a comma delimeted file. any chance posting us the output and what you are expecting

  
  
  

• asiaindian

  The items are coming from my SQL database: Item Table.

  Here is my statement :

  SELECT ItemLookupCode, Description, Cost, Price, SUM(Price - Cost) AS DIFF FROM Item WHERE (DepartmentID <> '24') AND (Inactive = '0') AND (Price - Cost > 0.009 OR Price - Cost < - 0.009) GROUP BY ItemLookupCode, Description, Cost, Price ORDER BY Diff

  Hope this will sched more light on what I need.

  
  
  

• Arie Jones

  Thank you very much ahmedilyas!!! You solved 2 days of trying and trying in 1 min.

  Thanks a Bunch!!!

  
  
  

• Wee Bubba

  no worries at all.

  So if we are reading data from a SQL, we can use a SqlDataReader which will read any results returned to us from our query. We will read data until no more data to read (row by row) and access the columns by index, or column Name. Let's give this a shot!....

  Dim command as new SqlCommand("SELECT ItemLookupCode, Description, Cost, Price, SUM(Price - Cost) AS DIFF FROM Item WHERE (DepartmentID <> '24') AND (Inactive = '0') AND (Price - Cost > 0.009 OR Price - Cost < - 0.009) GROUP BY ItemLookupCode, Description, Cost, Price ORDER BY Diff", new SqlConnection(ConnectionString))

  command.Connection.Open()

  Dim data as new System.Text.StringBuilder()

  Dim theReader as SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)

   

  while theReader.Read() = true

     Dim currentRowData(theReader.FieldCount) as Object 'declare an array to hold our results

     theReader.GetValues(currentRowData) 'gets our results and places it into our array

   

     for each currentResult as object in currentRowData

        data.Append(currentResult.ToString())

        data.Append(",")

     next

     data.Append(Environment.NewLine)

  end while

   

  command.Connection.Close()

   

  My.Computer.FileSystem.WriteAllText("filename.txt", data.ToString(), false)

   

  does this work for you

  this will basically go through each row of data and get results for each row of data and append it into a string via the StringBuilder, then at the end of it all, write it to disk!

  
  
  

• tunes2468

  what you need to do is loop through each item in the listbox and write it to the file. Example....

  private sub DoSave() Dim SFD as new SaveFileDialog() Try With SFD    .AddExtension = True    .CheckPathExists = True    .CreatePrompt = False    .OverwritePrompt = True    .ValidateNames = True    .ShowHelp = True    .DefaultExt = "txt"    .Filter = _    "CSV Files (*.csv)|*.csv|" & _    "All files|*.*"    .FilterIndex = 1    If .ShowDialog() = Windows.Forms.DialogResult.OK Then       Me.DoSaveItems(.FileName)    End If End With Catch ex As Exception    MsgBox(ex.Message, MsgBoxStyle.Exclamation, Me.Text) End Try end sub   private sub DoSaveItems(byval fileName as String) if fileName is nothing = false then    if fileName.Length > 0 then        using writer as new System.IO.StreamWriter(fileName)          for each currentItem as object in Me.ListBox1.Items             writer.Write(currentItem.ToString() & ",")          next       end using    end if end if   end sub   private sub cmdSave_Click(byval sender as object, byval e as eventargs) handles cmdSave.Click    Me.DoSave() end sub

   

  you could also do this in the DoSaveItems sub :

   

  private sub DoSaveItems(byval fileName as String) if fileName is nothing = false then    if fileName.Length > 0 then        Dim items as new System.Text.StringBuilder()          for each currentItem as object in Me.ListBox1.Items             items.Append(currentItem.ToString())             items.Append(",")          next            My.Computer.FileSystem.WriteAllText(fileName, items.ToString(), false)    end if end if   end sub

  
  
  

• xhead

  no the code wont bring up that dialog, you need to implement it and can be easily integrated. you can place that code above in the DoSaveItems sub we created earlier and do the same thing as before. The invalid cast exception, hmm. Any chance of showing where that error is being generated from, which line of code I Think it maybe:

  data.Append(currentResult.ToString())

  
  
  

• hanagomi

  I might have a little trouble now: I opened the file with Exel and instead of saving it in exel format it saved all the records in one row.... any reason why it should do something like that
  
  

• Rodolfo Caporale

  I thought it would look like a comma separated file that you could open in excel that looked like this:

  Item 1: Item lookupcode, Description, Cost, Price

  Item 2: Item lookupcode, Description, Cost, Price

  Item 3: Item lookupcode, Description, Cost, Price

  and so on.

  At this stage it looks like this : Item 1: Item lookupcode, Description, Cost, Price, Item 2:Item lookupcode, Description, Cost, Price, Item 3: Item lookupcode, Description, Cost, Price

  I am sorry if I caused confusion.

  
  
  

• Diango

  I got the Environment.NewLine to work and it is now saving my csv file the way I want it to be. I am having another issue though. Some of the Item.Description has got a comma in the description and that is not throwing my file out of sync. Do you maybe know of something I could do to go around this issue

  I also would like it if I could place headers on my Listbox as well as save those... Any ideas

  Hope I am not a bother.

  Thanks!!!

  
  
  

• Chuck Cobb

  I know I am missing something. I am getting the following error :

  System.InvalidCastException

  I know what I can cast, but I am not sure how we will place this in the code.

  Another question: Will this still bring up the save dialog and give the users the option of saving the file

  
  
  

• niallhannon

  It sounds like it could work. It would be great if you could explain to me what that is, because I have never done that and I am really eager to learn VB.

  Thanks ahmedilyas!

  
  
  
• Save data from a listbox to a .CSV file.