Remove empty columns from a tab delimited text file?

[Pirre001]

How can I open a tab delimited text file and remove the columns that are empty and the save it?
Empty columns are those that have no value in the first row.

[jmcilhinney]

I'd suggest reading the data into a DataTable, testing the data in the first row, removing the appropriate DataColumns, then writing the data back out to the file. You could use ADO.NET to read the data, or a TextFieldParser, or a StreamReader. I'd go with the TextFieldParser. You'd then use a StreamWriter to write the data back out again in a nested For or For Each loops.

[Pirre001]

Thanks jmcilhinney,

Do you have any examples you can show?

[Pirre001]

Ok, this imports a text file to its data table.
But how can I delete these empty columns? And how can I then export back the text file? Can I export with other delemiters also?

Private Function BuildDataTable(ByVal fileFullPath As String, ByVal seperator As Char) As DataTable
 
        Dim myTable As DataTable = New DataTable("MyTable")
        Dim i As Integer
        Dim myRow As DataRow
        Dim fieldValues As String()
        Dim f As IO.File
        Dim myReader As IO.StreamReader
        Try
            'Open file and read first line to determine how many fields there are.
            myReader = f.OpenText(fileFullPath)
            fieldValues = myReader.ReadLine().Split(seperator)
            'Create data columns accordingly
            For i = 0 To fieldValues.Length() - 1
                myTable.Columns.Add(New DataColumn("Field" & i))
            Next
            'Adding the first line of data to data table
            myRow = myTable.NewRow
            For i = 0 To fieldValues.Length() - 1
                myRow.Item(i) = fieldValues(i).ToString
            Next
            myTable.Rows.Add(myRow)
            'Now reading the rest of the data to data table
            While myReader.Peek() <> -1
                fieldValues = myReader.ReadLine().Split(seperator)
                myRow = myTable.NewRow
                For i = 0 To fieldValues.Length() - 1
                    myRow.Item(i) = fieldValues(i).ToString
                Next
                myTable.Rows.Add(myRow)
            End While
        Catch ex As Exception
            MsgBox("Error building datatable: " & ex.Message)
            Return New DataTable("Empty")
        Finally
            myReader.Close()
        End Try
 
        Return myTable
    End Function

Call it with

BuildDataTable("C:\Testfile.txt", vbTab)

[Pirre001]

Someone...?

[Dave D]

You added columns with myTable.Columns.Add
You should be able to drop the ones you don't want with Remove, e.g. to drop column 3, myTable.Columns.Remove(3)

Remember they count from 0, of course.

[Pirre001]

Ok, but how can I drop empty columns? What I mean is, how can I figure out wich columns who are empty? That's the problem...

[Dave D]

You defined an empty column as "Empty columns are those that have no value in the first row.". So you need to test every field in the first row to see if it's empty (Null).

Something like

myRow = myTable.Rows(0)
    For i = myTable.Columns.Count - 1 To 0 Step -1
    If myRow.Item(i).isNull Then myTable.Columns.Remove(i)
Next i

ought to work (no I have not tested it).

Note that it's stepping down from the last column, so it only removes columns you've already checked.

There are ways to solve your problem without building tables at all. You could read in the first row, set an array GoodCol() as Boolean to True or False depending whether each field is empty, then read in and split each row, writing out only the fields for which GoodCol(i) was true.