Import CSV to DataTable without header row

[cmhunt]

Hi

I'm trying to import a csv file into a data table without using the first row as a header row. My code is below:

        Dim Conn As OdbcConnection
        Dim dt As New DataTable
        Dim da As OdbcDataAdapter
        Dim strConnstr, strImportFolder, strFilename As String
        strImportfolder = "\\path"
        strFilename = "file.csv"
        strConnstr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & strImportfolder & "\;"
        Conn = New Odbc.OdbcConnection(strConnstr)
        da = New Odbc.OdbcDataAdapter("select * from [" + strFilename + "]", Conn)
        da.Fill(dt)

I have looked through tutorials but can't find how to prevent this. I need to read every row and not treat the first row as header row.

Any help would be gratefully appreciated.

Chris

 

[ca8msm]

I need to read every row and not treat the first row as header row.

What is treating it as a header row? Is it a control like a DataGrid?


----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[cmhunt]

The first row is going in as DataTable headings.

Thanks

Chris

 

[ca8msm]

Sorry I'm still a bit confused! What I don't understand is where the "headings" bit is coming from i.e. which property of which control is showing that it is a "header" row?





----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[cmhunt]

Ok, the values in the first row of the CSV file which the DataAdaptor is using to fill the DataTable are being used as the column headers for the DataTable so when iterating through the DataTable in later processing, the DataTable has one less row than the CSV file (the first row).

Apologies if my explanation isn't very clear.

Chris

 

[ca8msm]

OK - I see now! So for example, if you look at:

dt.Rows.Count()

it is actually one less than expected.

Sorry it took me a while to grasp that!!!

Let me have a look into this for you...

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ca8msm]

OK at first glance it looks a bit trickier than I thought it would be!

The other options you have, until I find an easier way!, would be:

1) Read the csv into a data table, add a new row at the beginning and then populate another data table.

2) Read the csv file in as a stream, add a new row with tthe column headers in and write the csv back out. It could then be read into your data table as normal.

Is there any particular reason that the csv doesn't have header rows as I would have thought it better practice to do so.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[cmhunt]

Hi

After some fiddling I found that simply adding a blank line at the top of the input files means everything works file. Not the perfect solution as messing with input files is just asking for something to go wrong, but I can't think of another way of doing it. Has anyone got any ideas whether it's the MS Text Driver or the DataTable or a mixture of both that's actually causing this to happen??

Thanks for your help. Seems an untidy solution but it seems to work ok.

Chris

 

[ca8msm]

After some fiddling I found that simply adding a blank line at the top of the input files means everything works file.

That's why I was asking why doesn't the csv file have a header row - if you have the option of being able to add a blank line, why not actually add the correct headers?

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[cmhunt]

Just thought I'd let you know that I've found a more permanent and robust solution. I found an article on using the Microsoft Text Driver and schema.ini file at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Using this, I can turn off the driver expecting a header row with "ColNameHeader=FALSE".

Perfect!!

Thanks to ca8msm for your comments.

Chris

 

[ca8msm]

And thanks to you for posting your solution - may come in handy. Have a star!

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.