coperations07
asked on
Ignore empty lines from csv file
Hi,
I'm using VS2010 with VB.Net.
I have a function that reads in a csv file to a datatable. My problem is that the last couple rows in the datatable are blank. I need to ignore these rows. I've tried checking if the Line Legnth is > 0, but it still brings in. I'm assuming the Legnth check doesn't work b/c there are commas in the line. I've tried checking the value of the first column in the datatable after each line writes to it and then deleting it if it is empty or null but this didn't work either. I would prefer to not write it to the datatable if I can catch it before. Here's what I'm working with...
I'm using VS2010 with VB.Net.
I have a function that reads in a csv file to a datatable. My problem is that the last couple rows in the datatable are blank. I need to ignore these rows. I've tried checking if the Line Legnth is > 0, but it still brings in. I'm assuming the Legnth check doesn't work b/c there are commas in the line. I've tried checking the value of the first column in the datatable after each line writes to it and then deleting it if it is empty or null but this didn't work either. I would prefer to not write it to the datatable if I can catch it before. Here's what I'm working with...
Dim sr As New StreamReader(path)
sr.ReadLine() 'skip the header line
Dim sFileData As String = sr.ReadToEnd()
sr.Close()
sr.Dispose()
Dim sLines As String() = sFileData.Split(ControlChars.Lf)
Dim dtRecs As New DataTable()
Dim sArray As String() = sLines(0).Split(","c)
For Each s As String In sArray
dtRecs.Columns.Add(New DataColumn())
Next
Dim row As DataRow
Dim sLastLine As String = ""
For Each ss As String In sLines
If ss.Length > 0 Then
row = dtRecs.NewRow()
sLastLine = ss.Replace(Convert.ToString(ControlChars.Cr), "")
row.ItemArray = sLastLine.Split(","c)
dtRecs.Rows.Add(row)
End If
Next
I'm assuming the Legnth check doesn't work b/c there are commas in the line.
If that's the case, then your comparison could be modified to:
If Trim(ss.replace(",","")) <> ""
(and the above should also be <>, not = )
ASKER
Thanks sirbounty. I've tried all 3 suggestions, but the two empty rows are still coming in.
ASKER
I didn't realize this earlier, but this started out bringing in 3 empty rows. With the empty line check in place it only brings in 2 empty rows. I just saved the csv as a textfile and opened it up. The textfile looks like it has 1 empty row at the bottom. So now I'm thinking I may be looping too many times and adding extra rows...
Could you post a snippet of the source file, obfuscating any sensitive data, or course...
ASKER
Here is the file I'm working with.
AA-READY.csv
AA-READY.csv
I'm surprised the 2nd suggestion didn't work...here's the full block
Dim row As DataRow
Dim sLastLine As String = ""
For Each ss As String In sLines
If Trim(ss.replace(",","")) <> "" Then
row = dtRecs.NewRow()
sLastLine = ss.Replace(Convert.ToString(ControlChars.Cr), "")
row.ItemArray = sLastLine.Split(","c)
dtRecs.Rows.Add(row)
End If
Next
ASKER
Yes that is the same block that I am using. I copy/pasted yours in just to be sure, but it is the same. I'm setting the dtRecs table as a datasource for a datagridview so I could see its contents. One of the empty rows I was seeing was just because the allowusertoaddrow setting was on. I set that to false and it only has 1 empty row now.
Where are you seeing the empty row? This is in a datagrid?
Wonder if you could post the source code, because this appears to work for me...
Wonder if you could post the source code, because this appears to work for me...
ASKER
Here's most of what is running in this event. The btnRun click kicks it off.
Private Sub btnRun_Click(sender As System.Object, e As System.EventArgs) Handles btnRun.Click
'Make sure occassion/program was selected. Also the room and mcc.
sProgram = cboOcc.Text
sRmMcc = txtRmMcc.Text
If sProgram = "" Then
MessageBox.Show("Select the Occassion first.")
Exit Sub
End If
If sRmMcc = "" Then
MessageBox.Show("Enter the room and mcc. Then try again.")
Exit Sub
End If
'Open directory for file selection and import.
Dim sImportFile As String
Dim openFileDialog1 As New OpenFileDialog()
openFileDialog1.Title = "Select the initial line setup file."
openFileDialog1.InitialDirectory = "C:\Users\" & Environment.UserName & "\HOF\"
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True
sImportFile = openFileDialog1.ShowDialog()
If sImportFile = System.Windows.Forms.DialogResult.OK Then
Try
'Call function to read the csv file and save it into the datatable.
Dim dt As DataTable = ReadCSV(openFileDialog1.FileName)
Dim sYear As String = dt.Rows(0)(1)
Dim iCheck As Integer = ImportPrep1(sProgram, sYear) 'Check if data already exists. Clean out temp tables.
dgvLSU.DataSource = dt 'testetestetsetest*****!!!
Exit Sub
'*************************************************
If iCheck > 0 Then
Dim Answer As DialogResult = MessageBox.Show("The program and year for this data already exists in the database. Would you like to continue and purge the existing data for this program/year out and import the new data in?", _
"Program/Year already exists!", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)
If Answer = Windows.Forms.DialogResult.Yes Then
'Remove the data from tbl_lsu and continue.
Dim sqlDel As String = "DELETE tbl_lsu WHERE program = '" & sProgram & "' AND progYear = '" & sYear & "'"
Dim con As New SqlConnection(My.Settings.setDB)
con.Open()
Dim cmd As New SqlCommand(sqlDel, con)
cmd.ExecuteNonQuery()
con.Close()
Else
Exit Sub
End If
End If
SaveToDatabase(dt) 'Call sub to insert the data from datatable to database table.
Import1() 'update some stuff
CreateFillBackup2() 'build fill and backup locations.
AssignLocs3() 'assign newly built locations.
Workbench5() 'insert data into workbench table.
NeutralID()
LSUFinal()
dgvLSU.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
Catch Ex As Exception
MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
Finally
End Try
End If
End Sub
Function ReadCSV(ByVal path As String) As System.Data.DataTable
'This function brings in the csv file. The first line is skipped because it contains headers.
Try
Dim sr As New StreamReader(path)
sr.ReadLine() 'skip the header line
Dim sFileData As String = sr.ReadToEnd()
sr.Close()
sr.Dispose()
Dim sLines As String() = sFileData.Split(ControlChars.Lf)
Dim dtRecs As New DataTable()
Dim sArray As String() = sLines(0).Split(","c)
For Each s As String In sArray
dtRecs.Columns.Add(New DataColumn())
Next
'Dim row As DataRow
'Dim sLastLine As String = ""
'For Each ss As String In sLines
' If Trim(ss) <> String.Empty Then 'ignore any empty lines.
' row = dtRecs.NewRow()
' sLastLine = ss.Replace(Convert.ToString(ControlChars.Cr), "")
' row.ItemArray = sLastLine.Split(","c)
' dtRecs.Rows.Add(row)
' Else
' MessageBox.Show("tewst")
' End If
'Next
Dim row As DataRow
Dim sLastLine As String = ""
For Each ss As String In sLines
If Trim(ss.Replace(",", "")) <> "" Then
row = dtRecs.NewRow()
sLastLine = ss.Replace(Convert.ToString(ControlChars.Cr), "")
row.ItemArray = sLastLine.Split(","c)
dtRecs.Rows.Add(row)
End If
Next
''Remove any blank rows. Flag all to delete and then call acceptchanges to delete the flagged rows. This retains the index during loop.
'For i As Integer = 0 To dtRecs.Rows.Count - 1
' If dtRecs.Rows(i)(0) = "" Then
' dtRecs.Rows(i).Delete() 'flag to delete.
' End If
'Next
'dtRecs.AcceptChanges() 'delete flagged rows.
Return dtRecs
Catch ex As Exception
Throw ex
End Try
End Function
Public Sub SaveToDatabase(ByVal dt As DataTable)
'Insert the data from the datatable into the database table.
Try
Using cn As New SqlConnection(My.Settings.setDB)
cn.Open()
Using copy As New SqlBulkCopy(cn)
copy.DestinationTableName = "tbl_import"
copy.WriteToServer(dt)
End Using
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
Dim sErr As String = ImportError(dt)
MessageBox.Show(sErr)
End Try
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfecto! Many thanks Sir.
Happy to help - thanks for the grade! :^)
For Each ss As String In sLines
If Trim(ss) = "" Then 'or compare against string.empty
....