We help IT Professionals succeed at work.

skip blank lines or lines that start with a ; when reading csv file

chadmanvb
chadmanvb asked
on
452 Views
Last Modified: 2008-02-01
I am reading a csv file to setup a table.  I want it to ignore any line that is blank or starts with a ;

below is my current code:
  Public Sub FillTable()
        CSVdt = New DataTable("CSV_Data")
        CSVdt.Columns.Add("Branch")
        CSVdt.Columns.Add("UserID")
        CSVdt.Columns.Add("Password")
        CSVdt.Columns.Add("Description")
        Dim sr As System.IO.StreamReader
        Dim strContents() As String
        Dim strData() As String
        Try
            sr = New System.IO.StreamReader("\\scripts\windows_scripts\Easypass\passwords.csv")
            strContents = sr.ReadToEnd.Split(vbNewLine)
            sr.Close()
        Catch Ex As Exception
            Console.WriteLine(Ex.Message)
        End Try
        For Each strLine As String In strContents
            strData = strLine.Split(",")
            CSVdt.Rows.Add(strData)
        Next
    End Sub
Comment
Watch Question

Commented:
Hi chadmanvb,

try this as your For Each loop:

        For Each strLine As String In strContents
            If strLine.StartsWith(";") Or strLine.Length = 0 Then
                ' skipping line
            Else
                strData = strLine.Split(",")
                CSVdt.Rows.Add(strData)
            End If
        Next


Cheers,
Hillwaaa
CERTIFIED EXPERT
Top Expert 2007

Commented:
If strData <> "" and strData.SubString(0,1) <> ";" Then
  strData = strLine.Split(",")
    CSVdt.Rows.Add(strData)

Author

Commented:
I am still getting blank lines when I do this and try to populate a combobox1 with this:
  Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
         Handles ComboBox1.SelectedIndexChanged
        ComboBox2.Items.Clear()
        For Each dr As DataRow In CSVdt.Select("[Branch] LIKE '*" & ComboBox1.Text & "*'")
            ComboBox2.Items.Add(dr("Description") & " " & dr("UserID"))
        Next
    End Sub
 Public Sub FillTable()
        CSVdt = New DataTable("CSV_Data")
        CSVdt.Columns.Add("Branch")
        CSVdt.Columns.Add("UserID")
        CSVdt.Columns.Add("Password")
        CSVdt.Columns.Add("Description")
        CSVdt.Columns.Add("CICS")
        CSVdt.Columns.Add("Special")
        Dim sr As System.IO.StreamReader
        Dim strContents() As String
        Dim strData() As String
        Try
            sr = New System.IO.StreamReader("\\nfdgnode-1\cloneout$\cloneconfig\scripts\windows_scripts\Easypass\passwords.csv")
            strContents = sr.ReadToEnd.Split(vbNewLine)
            sr.Close()
        Catch Ex As Exception
            Console.WriteLine(Ex.Message)
        End Try
        For Each strLine As String In strContents
            If strLine.StartsWith(";") Or strLine.Length = 0 Then
                'skipping(line)
            Else
                strData = strLine.Split(",")
                CSVdt.Rows.Add(strData)
            End If
        Next
       
    End Sub

    Private Sub GetUniqueBranches()
        Dim arr As New ArrayList
        For Each dr As DataRow In CSVdt.Rows
            If Not arr.Contains(dr("Branch").ToString.Trim) Then
                arr.Add(dr("Branch").ToString.Trim)
            End If
        Next
        ComboBox1.Items.AddRange(arr.ToArray)
    End Sub


using a csv file like:

;branch,userid,password,description,cics,special
UK Branch 10000,p12340,12340,FA,DUNC,Non-pilot user
UK Branch 10000,p12341,12341,OAG,NBUSKIR,Pilot User
UK Branch 10000,p12342,12342,OAG,CKETT,Non-pilot user

Canadian Branch 20000,p12343,12343,FA,DUNTX,Non-pilot user
Canadian Branch 20000,p12344,12344,OA,EAEYJ,Pilot User
Canadian Branch 20000,p12345,12345,OA,HAISMR,Pilot User

US Branch 30000,p12346,12346,FA,ELKYOK2,Pilot User
US Branch 30000,p12347,12347,OA,ELOK5,Non-pilot user
US Branch 30000,p12348,12378,OA,ELKC,Non-pilot user

Commented:
Sorry - the strLine length check needs to first call Trim to remove the end of line characters:

        For Each strLine As String In strContents
            If strLine.StartsWith(";") Or strLine.Trim.Length = 0 Then
                'skipping(line)
            Else
                strData = strLine.Split(",")
                CSVdt.Rows.Add(strData)
            End If
        Next
Retired
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks, That did it!  Chad
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Not a problem, glad to help. ;=)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.