Find current Row in a DataTable

Hi all,

I am getting slightly frustrated with this problem now and everthing I try does not work! I have some code that selects data from a SQL table and stores it in a DataTable. I then loop through this DataTable to write out the data as CSV. The problem is that I have to put text qualifiers around certain values. Here is a sample of the CSV file I need to output:


Telephone Directory Assignment,vid7.23,7.0.5.3,,,,
0,1,2,3,4,5,6
Name,Number,Prime Name,Privacy,Cluster Element Id/Int/Ext/Speed Call,Department,Location
"Bloggs,Joe",9~12345678,No,No, ,,
"Beam,Jim",9~12345678,No,No, ,,
"Smith,John",9~12345678,No,No, ,,
"Doe,John",9~12345678,No,No, ,,


The CSV file is then uploaded to a server where it is converted into the server's address book format and sent out to all our VoIP phones.

I can output the CSV no problem but what I cannot do is put the text qualifiers starting from the 4th row onwards. When I output the CSV, every row in the first column has "" around it! Here is my code:

Private Sub ExportGridToCSV()
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter
Dim MyDataTable As New DataTable

Dim strSQL As String = _
"SELECT [phonebook_Name], [phonebook_Number], [phonebook_Primename], [phonebook_Privacy], [phonebook_Cluster],
[phonebook_Dept], [phonebook_Location] FROM [wb_TelephoneBook]"
MyConnection = New SqlConnection( _
ConfigurationManager.ConnectionStrings("plistConnectionString").ConnectionString)
MyConnection.Open()
MyDataAdapter = New SqlDataAdapter(strSQL, MyConnection)
MyDataAdapter.Fill(MyDataTable)
MyConnection.Close()

If IO.File.Exists("GridData.csv") Then IO.File.Delete("GridData.csv")

Dim sw As StreamWriter = New StreamWriter(Server.MapPath("GridData.csv"), False)
Dim iColCount As Integer = MyDataTable.Columns.Count
Dim iRowCount As Integer = MyDataTable.Rows.Count
Dim i As Integer = 0

For Each dr As DataRow In MyDataTable.Rows
i = 0
Do While i < iColCount
If (Not Convert.IsDBNull(dr(i))) Then
If i = 0 Then ' checking for first column
sw.Write("""" & dr(i).ToString() & """")
Else
sw.Write(dr(i).ToString())
End If
End If
If i < iColCount - 1 Then
sw.Write(",")
End If
i += 1
Loop
sw.Write(sw.NewLine)
Next dr
sw.Close()
End Sub

I can check to see if I am on the first column but I cannot find a way to check what row I am currently looping through. Why is there no CurrentRow property! :confused:

If anyone has an idea on how I can check the row index (so I can apply the text qualifiers from row 4 onwards) then please post them.

Thanks.

The row item array should correlate to the columns. For example if you have 5 columns, each row's itemArray() length is 5.

Therefore do a for each against all rows. Get the ItemArray() or collection of objects:

myObjectArray = row.ItemArray

Now iterate through myObjectArray until length -1 and you can start at position 4 or anywhere within that collection.

When you say you can't get the current "row" that is what the for each iteration does. If the Rows.Count is 50, your for each will loop through 5O rows, the ItemArray will get all values that correlate to COLUMNS for that row. Using a For Each and an integer that increments will return the numeric equivlent of what "row" you are on (start at 0 or 1, i++, continue) but I don't see why you care what row it is as long as you iterate through the entire collection and pull out the specific items you need from each row

Hi MKoslof,

I decided that the code was not very effcient for writing out values that required text qualifiers so I looked at someones C# code that seemed a lot more efficient so I wrote a VB.NET version of that and it works a lot better:

Public Sub ExportCSV(ByVal dt As DataTable, ByVal OutputHeaders As Boolean)
Dim FILENAME As String = Server.MapPath("GridData.csv")
Dim sw As New StreamWriter(FILENAME)

If OutputHeaders Then
Dim arr As String() = New String(dt.Columns.Count - 1) {}
For i As Integer = 0 To dt.Columns.Count - 1
arr(i) = dt.Columns(i).ColumnName
arr(i) = OutPutWriteValues(arr(i))
Next i

sw.WriteLine(String.Join(",", arr))
End If

For j As Integer = 0 To dt.Rows.Count - 1
Dim dataArr As String() = New String(dt.Columns.Count - 1) {}
For i As Integer = 0 To dt.Columns.Count - 1
Dim o As Object = dt.Rows(j)(i)
dataArr(i) = OutPutWriteValues(o)
Next i
sw.WriteLine(String.Join(",", dataArr))
Next j

sw.Close()

End Sub

Public Function OutPutWriteValues(ByVal dtInput As Object) As String
If dtInput Is Nothing OrElse dtInput Is Convert.DBNull Then
Return ""
ElseIf dtInput.ToString().IndexOf(",") = -1 Then
Return dtInput.ToString()
Else
Return """" & dtInput.ToString() & """"
End If

End Function

Thanks for the help.