0

I'm being presented with a CSV file that i would like to import to a datatable. The challenge I have is that the file has 2 different delimters. The first few columns are delimited with a "tab" and the rest with a";". I can handle the one easily but not sure how to handle both. The code that I have so far but struggling to find a way to expand this to import it the single step:

Public Function LoadFileToDatatable(ByVal FullFilePath As String)
    'Load the Testfile into an datatable
    Dim folder As String = System.IO.Path.GetDirectoryName(FullFilePath)
    Dim filename As String = System.IO.Path.GetFileName(FullFilePath)
    Dim con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folder & ";Extended Properties=""text;HDR=No;FMT=Delimited"";"
    Dim dt As New DataTable

    Using Adp As New OleDbDataAdapter("Select * From " & filename, con)
        Adp.Fill(dt)

        'Remove the first row as it contains the header data
        Dim theRow As DataRow = dt.Rows(0)
        dt.Rows.Remove(theRow)
    End Using
    Return dt
End Function
Stephen Pefanis
  • 305
  • 1
  • 3
  • 16
  • 1
    I know it is not the correct way, but why not just do a replace all in the file? Replace one of the delimiter with the other one. Just read the file as a string, replace all `;` with `\t`, and then use this string to fill dataadapter. – jitendragarg Apr 28 '16 at 03:58
  • http://stackoverflow.com/questions/5715338/csv-string-to-datatable This question will help you convert string to datatable. – jitendragarg Apr 28 '16 at 04:02
  • Personally, I would tend to use a `TextFieldParser`. It has a `Delimiters` property that accepts multiple delimiters. You can then call `ReadFields` to get a `String` array that you can use to populate a `DataRow`. – jmcilhinney Apr 28 '16 at 04:05
  • 1
    I didn't know textfieldparser can take a file. :/ in that case, that is really the better way. Edit: Stephen, use this question. http://stackoverflow.com/questions/3507498/reading-csv-file It solves your problem, in much more elegant way. – jitendragarg Apr 28 '16 at 04:23
  • Thanks for the suggestions. I will look into the solution jitendra, At this stage i have got it working by replaceing the "\t" with a ";" but a more elegant solution would be better. – Stephen Pefanis Apr 29 '16 at 02:03

0 Answers0