How do I export data from a DataGridView in VB.net to a CSV file

[pauljohns353]

Hi,

I am new to VB.net programming and was wondering if anybody has any suggestions as to how I can export data from a DataGridView via a command button to a CSV file.

Any suggestions or sample code would be really appreciated.

Many thank.
Paul.

[debasiskc]

Did u get the solution of your problem???
Actually I am also required the same kind of solution.
If you u get that plz forword that to me.

[shaque]

Hi, I was working with CSV file and DataGridView in VB.NET. Having lots fo complexities i succeeded to retrieve a 5 columns CSV file into DataGridView. It is to be mentioned that the last two columns in the dgv are Combo type.

My goal was after retrieving the csv file I will edit other two selection columns and export the modified file in a different locations.

the content of the CSV file is as follows:

2014001,"Mensch , Samantha",CD Models Christian values,S,*
2014001,"Mensch , Samantha",CD Shows reverence during pray,S,
2014001,"Mensch , Samantha",CD Accepts responsibility for,S,
2014001,"Mensch , Samantha",CD Practices self control,S,
2014001,"Mensch , Samantha",CD Observes school rules,S,*
2014001,"Mensch , Samantha",CD Respects property,S,
2014001,"Mensch , Samantha",CD Respects others,S,
2014001,"Mensch , Samantha",Uses time wisely,S,
2014001,"Mensch , Samantha",Displ ays effort,S,
2014001,"Mensch , Samantha",Works well in a group,S,
2014001,"Mensch , Samantha",Works well independently,S ,

Code for the Reading CSV file as follows under a button:

Sub Action_Button
Dim lineNumber As Integer = 0
Dim line As String
Dim lToken() As String
Dim sTemp As String
Dim sGridRow(4) As String


OpenFileDialog1 .InitialDirecto ry = "c:\temp\"
'openFileDialog 1.Filter = "All files (*.*)|*.*"
OpenFileDialog1 .Filter = "CSV files (*.csv)|*.CSV"
OpenFileDialog1 .FilterIndex = 2
OpenFileDialog1 .RestoreDirecto ry = True
If (OpenFileDialog 1.ShowDialog() = Windows.Forms.D ialogResult.OK) Then
fName = OpenFileDialog1 .FileName
Me.Text = fName
End If
Me.Text = fName
Try
Dim reader As IO.StreamReader = New IO.StreamReader (fName)
'DataGridView2. Rows.Remove()
DataGridView2.R ows.Clear()
While reader.Peek <> -1
line = reader.ReadLine ()
lToken = Split(line, ",")

For i As Integer = 0 To lToken.Length - 3
sTemp = Replace(lToken( i), """", "")
sGridRow(i) = sTemp
Next
Me.DataGridView 2.Rows.Add(sGri dRow)
lineNumber += 1
End While

'Me.Text = Str(lineNumber)
reader.Close()
Catch ex As Exception
MessageBox.Show ("Error occured while reading the csv file." + ex.ToString)
End Try
End Sub

The above code works fine, Please see the attached.

Now I face the problem after modifying the row in the dgv; suppose I have selected a grade from the combo. Now I want to export the file in a selected location as with the same name as I imported.

I can import the file taking the data from the dvg. I gave the code here to populate the dvg rows. I need entire dvg data to read and write them in the file.
The code I don know...How to take the data from the dbg from the first row to the last? The code I wrote is pasted here, but it simply populates the current row column.

Private Sub SaveGridDataInF ile(ByRef fName As String)

'Dim writer As IO.StreamWriter = New IO.StreamWriter (fName)
Dim I As Integer = 0
Dim j As Integer = 0
Dim cellvalue$
Dim rowLine As String = ""
Try

FileOpen(1, fName, OpenMode.Output ) ' Open file for output.
'WriteLine(1, "Hello", " ", "World") ' Separate strings with space.
'WriteLine(1, SPC(5), "5 leading spaces ") ' Print five leading spaces.
'WriteLine(1, TAB(10), "Hello") ' Print word at column 10.
'FileClose(1) ' Close file.

'sData = DataGridView1.C olumns(1).Name. ToString()
'sData = Me.DataGridView 1.CurrentRow.Ce lls(i).Value.To String
'MessageBox.Sho w(sData)
For j = 0 To (DataGridView2. Rows.Count - 2)
For I = 0 To (DataGridView2. Columns.Count - 1)
If Not TypeOf DataGridView2.C urrentRow.Cells .Item(I).Value Is DBNull Then
cellvalue = DataGridView2.C urrentRow.Cells .Item(I).Value. ToString
Else
cellvalue = ""
End If

'DataGridView2. CurrentRow.Cell s.Item(I).Value = cellvalue
rowLine = rowLine + cellvalue + ","
Next
WriteLine(1, TAB(0), rowLine) ' Print word at column 10.
'writer.WriteLi ne()
rowLine = ""
Next
FileClose(1) ' Close file.
Catch e As Exception
MessageBox.Show ("Error occured while writing to the file." + e.ToString())
Finally
FileClose(1)
End Try

End Sub




Could you please help me giving the code that reads entire row column of the dvg?

[shaque]

At last i succeeded. That is I populated the datagridview control with the following syntax:

For j = 0 To (DataGridView2. Rows.Count - 2)
For I = 0 To (DataGridView2. Columns.Count - 1)
If Not TypeOf DataGridView2.I tem(I, j).Value Is DBNull Then
cellvalue = DataGridView2.I tem(I, j).Value Else
cellvalue = ""
End If
rowLine = rowLine + "" & Chr(34) & cellvalue & Chr(34) + ","
Next
rowLine = rowLine.Remove( rowLine.Length - 1, 1)
sw.WriteLine(ro wLine)
rowLine = ""
Next


Now I can export CSV file from datagrideview to a formated text file.
[EMAIL REMOVED]

Please give the subject of the email: CSF file Handler.
Thanks and regards
Shahidul Haque.

[pauljohns353]

Did u get the solution of your problem???
Actually I am also required the same kind of solution.
If you u get that plz forword that to me.

Yes I did get a solution to my problem...I know the code may seem a little complicated but it works a treat, obviously you will need to tweak any data but this is the code I needed for my solution.

Private Sub btnExportData_C lick(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles btnExportData.C lick
Dim fsStream As New FileStream("C:\ Temp\Test Results_" & txtTestSpecID.T ext & "_" & cboTestPhase.Te xt & ".csv", FileMode.Append , FileAccess.Writ e)
Dim swWriter As New StreamWriter(fs Stream)
Dim TotalPasses1 As Integer = 0
Dim TotalFails1 As Integer = 0
Dim TotalNA1 As Integer = 0

'Displays a Message Box informing the user you are about to Quit the application
Dim intReturnValue1 As Integer

intReturnValue1 = MsgBox("Data Saved Successfully", MsgBoxStyle.OkO nly + MsgBoxStyle.Inf ormation, "Data Saved")
Try
CheckSave()
swWriter.WriteL ine("Database, Test Spec ID, System, Version, Test Phase, Sites, Date/Time, Tester, Witness,Test Number, Test Step, Step 1, Step 2, Pass, Fail, N/A, Fault Reference,Fault Category,Commen ts,")
Dim temp As Integer = 0
For temp = 0 To grdResults.Rows .Count - 1
swWriter.Write( lblTestsSpec.Te xt & "," & txtTestSpecID.T ext & "," & cboSystem.Text & "," & txtVersion.Text & "," & cboTestPhase.Te xt & "," & cboSites.Text & "," & txtDateTime.Tex t & "," & txtTester.Text & "," & txtWitness.Text & ",")
Dim temp2 As Integer = 0
For temp2 = 0 To grdResults.Rows (temp).Cells.Co unt - 1
If IsDBNull((grdRe sults.Rows.Item (temp).Cells.It em(temp2).Value )) Then

Else
If (grdResults.Row s.Item(temp).Ce lls.Item(temp2) .Value) = "True" Then
swWriter.Write( "1")
If temp2 = 4 Then
TotalPasses1 = TotalPasses1 + 1
End If
If temp2 = 5 Then
TotalFails1 = TotalFails1 + 1
End If
If temp2 = 6 Then
TotalNA1 = TotalNA1 + 1
End If
ElseIf (grdResults.Row s.Item(temp).Ce lls.Item(temp2) .Value) = "False" Then
swWriter.Write( "")
Else
swWriter.Write( grdResults.Rows .Item(temp).Cel ls.Item(temp2). Value)
End If
End If

swWriter.Write( ",")
Next
swWriter.WriteL ine(",")
Next
swWriter.WriteL ine("," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & TotalPasses1 & "," & TotalFails1 & "," & TotalNA1 & ",")
swWriter.Flush( )
MsgBox("Data Saved Successfully")
swWriter.Close( )
Catch ex As Exception
MsgBox(ex.Messa ge)
End Try
End Sub

[sathyakvani]

hi

what is a csv file????

thanks in advance

[shaque]

Hi I am little bit confused about your question.

You wanted to know what is a CSV file? if this is the question, the simple answer is it is a Comma Separated Value file almost like a text file. It can be opened in XLS format also. and to open it in notepade is trivial.

Paste these lines in a text file and change the file name for example text.csv.
Try to open it in MS Excel and then see the csv file.

2014001,"Mensch , Samantha",CD Models Christian values,S,*
2014001,"Mensch , Samantha",CD Shows reverence during pray,S,
2014001,"Mensch , Samantha",CD Accepts responsibility for,S,
2014001,"Mensch , Samantha",CD Practices self control,S,
2014001,"Mensch , Samantha",CD Observes school rules,S,*
2014001,"Mensch , Samantha",CD Respects property,S,
2014001,"Mensch , Samantha",CD Respects others,S,
2014001,"Mensch , Samantha",Uses time wisely,S,
2014001,"Mensch , Samantha",Displ ays effort,S,
2014001,"Mensch , Samantha",Works well in a group,S,
2014001,"Mensch , Samantha",Works well independently,S ,

Thanks.
shaque

[debasiskc]

First of all thank u very much for giving some solution.

Actual my moto is to save the datagridvalues in any file(.txt /.csv).

I have already able to save the data in .txt file by using the simple code mentioned below:

using (StreamWriter MyFile = new StreamWriter(Ap plication.Start upPath+"\\About File Log.txt"))
{
for (int i = 0; i < dataGridView1.R owCount; i++)
{
MyFile.WriteLin e("File Name: " + FinalDirs[i] + ", " + "File Version: " + FinalVers[i] + ", " + "File Size: " + FinalSize[i] + ", " + "Descriptio n: " + FinalDesc[i]);
MyFile.WriteLin e("");

// Arbitrary objects can also be written to the file.
}
MyFile.Write("T his file generated on: ");
MyFile.WriteLin e(DateTime.Now) ;
MessageBox.Show ("Informatio n of all the files are successfully saved in the following location: \n"+Application .StartupPath+"\ \AboutFileLog.t xt", Application.Pro ductName, MessageBoxButto ns.OK, MessageBoxIcon. Information);
Close();
}
}

[pauljohns353]

hi

what is a csv file????

thanks in advance

A csv file is a comma seperated value, basically in laimans terms a text file that is easily imported into an Excel style spreadsheet.

Hope that makes things a little clearer.

[aalexandrov]

A csv file is a comma seperated value, basically in laimans terms a text file that is easily imported into an Excel style spreadsheet.

Hope that makes things a little clearer.

Hi, ... if it is helpfull you can use DataGridView Extension for .NET to export data to excel, html .... soon pdf. Also you can search for text after adding the component, to save settings like themes, to apply styles, manage columns and so on.
here it is the site http://www.completit.c om/Products/DGVE/Overview.aspx