asked on

Trying to import a text file into VB application

I have VB program that reads in data from various file formats (CSV, text, etc). The problem is that with the attached file, seems to have both CSV and tab delimiters. I can import it (w/wizard) to excel with both tab and/or comma delimiters, but when i try to read it into the prgram (w the QSR case with a comma delimiter, only the first column is read in. Is there syntax for both tab and column delimiters? If i convert the file in excel to a tab deliimited text fie, it comes in just fine. Any other thoughts?

Case "QSR File"
         Try
           Call FillGridText(",")
           Catch ex As Exception
           If CmdLine Then
            EventLogOI.WriteEntry("EASBulkTrans execution halted: " & _
            Error calling FillGridText(,) subroutine. " & ex.Message)
               End
            Else
            MsgBox("Error calling FillGridText(,) subroutine. " & ex.Message & ": " & ex.StackTrace)
            End If
          End Try
Case "Text File (tab delimited)"
           Try
           Call FillGridText("TAB")
           Catch ex As Exception
          If CmdLine Then
        EventLogOI.WriteEntry("EASBulkTrans execution halted: " & _
        "Error calling FillGridText(TAB) subroutine. " & ex.Message & ": " & ex.StackTrace)
         End
        Else
        MsgBox("Error calling FillGridText(TAB) subroutine. " & ex.Message & ": " & ex.StackTrace)
        End If
        End Try

Open in new window

SAMPLE.txt

Dana Seaman

Can you post code for FillGridText? You may need to Remove Chr(34) from Sample.txt lines.

jcgeAudit

ASKER

Here you go...

Public Sub FillGridText(ByVal SepOI As String)
 
        'Create Data Table and add to Dataset
        Dim dt As New DataTable
        Dim c As Integer
        Try
            DS = New System.Data.DataSet
            'Set DS number of columns to 40
            For c = 1 To 40
                dt.Columns.Add("Field" & c)
            Next
            DS.Tables.Add(dt)
        Catch ex As Exception
            MsgBox(ex.Message & ": " & ex.StackTrace)
        End Try
 
        'Process csv file into DS
        Dim sr As System.IO.StreamReader
        Dim LineOI As String
        Dim Fields() As String, f As Integer
        Dim dr As DataRow
        Try
            sr = New System.IO.StreamReader(frm.txtFilePath.Text & frm.txtFileName.Text, System.Text.Encoding.Default, True)
            Do
                LineOI = sr.ReadLine
                If SepOI = "TAB" Then
                    If Not (LineOI Is Nothing) And (InStr(LineOI, Chr(9)) > 0) Then
                        'MsgBox("before fix " & LineOI)
                        'LineOI = FixLine(LineOI)
                        LineOI = Replace(Replace(LineOI, Chr(34), ""), "$", "")
                        'MsgBox("after fix " & LineOI)
                        Fields = Split(LineOI, Chr(9))
                        dr = dt.NewRow()
                        For f = 0 To Fields.Length - 1
                            If f < c - 1 Then 'Only works with first 40 columns in the file
                                dr(f) = Replace(Fields(f).ToString, Chr(34), "")
                            End If
                        Next
                        dt.Rows.Add(dr)
                    End If
                Else
                    If Not (LineOI Is Nothing) And (InStr(LineOI, SepOI) > 0) Then
                        'MsgBox("before fix " & LineOI)
                        LineOI = FixLine(LineOI)
                        'MsgBox("after fix " & LineOI)
                        Fields = Split(LineOI, SepOI)
                        dr = dt.NewRow()
                        For f = 0 To Fields.Length - 1
                            If f < c - 1 Then 'Only works with first 40 columns in the file
                                dr(f) = Replace(Fields(f).ToString, Chr(34), "")
                            End If
                        Next
                        dt.Rows.Add(dr)
                    End If
                End If
            Loop Until LineOI Is Nothing
            With frm.dgFileData
                .DataSource = DS
            End With
            DSRowCount = DS.Tables(0).Rows.Count
            sr.Close()
        Catch ex As Exception
            If CmdLine Then
                EventLogOI.WriteEntry("EASBulkTrans execution halted: " & _
                "Error filling dataset with Text/CSV file (" & FPath & FileOI & "). " & ex.Message)
                End
            Else
                MsgBox("Error filling dataset with Text/CSV file (" & FPath & FileOI & "). " & ex.Message & ": " & ex.StackTrace)
            End If
        End Try
        'sr = Nothing
 
    End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Dana Seaman

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer