Importing .csv file into a datatable VB.NET

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do you tell the .csv file that you are to import that it has no row
headers as I went to the web site http://www.connectionstrings.com/ and under
text file it says:

"HDR=Yes;" indicates that the first row contains columnnames, not data

So i have done HDR=No but this does not work as.
 
¤ How do you tell the .csv file that you are to import that it has no row
¤ headers as I went to the web site http://www.connectionstrings.com/ and under
¤ text file it says:
¤
¤ "HDR=Yes;" indicates that the first row contains columnnames, not data
¤
¤ So i have done HDR=No but this does not work as.

Perhaps you could post a sample of your text file structure?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Jonathan, what kind of results do you get using HDR=No ? If you set it to No, then Access will set the Column Names to:
Field1, Field2, Field3 etc. If that is not what you want, then you will have to set the Column/Field Names in code. Otherwise,
you will get the default names.
james
 
My code:

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\;Extended Properties=""Text;HDR=No;FMT=Delimited""")

Dim da As New OleDbDataAdapter()

Dim ds As New DataSet()

Dim cd As New OleDbCommand("SELECT * FROM C:\Test.csv, cn)

cn.Open()
da.SelectCommand = cd
ds.Clear()
da.Fill(ds, "CSV")
dg.DataSource = ds.Tables(0)
cn.Close()

Test.csv contains 13,472 rows but when it is opened in the datagrid it
returns 13,471 rows
 
What happens is instead of getting 13472 rows I get 13471 rows in the
datagrid. I am aware of Access doing this but I am working in VB.NET trying
to import a csv file into a datagrid.
 
¤ My code:
¤
¤ Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
¤ Source=C:\;Extended Properties=""Text;HDR=No;FMT=Delimited""")
¤
¤ Dim da As New OleDbDataAdapter()
¤
¤ Dim ds As New DataSet()
¤
¤ Dim cd As New OleDbCommand("SELECT * FROM C:\Test.csv, cn)
¤
¤ cn.Open()
¤ da.SelectCommand = cd
¤ ds.Clear()
¤ da.Fill(ds, "CSV")
¤ dg.DataSource = ds.Tables(0)
¤ cn.Close()
¤
¤ Test.csv contains 13,472 rows but when it is opened in the datagrid it
¤ returns 13,471 rows

Which row is missing?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Jonathan, try changing :
dg.DataSource = ds.Tables(0)
to:
dg.DataSource = ds.Tables(0).DefaultView

Also, you might want to change this:
da.Fill(ds,"CSV")
to:
da.Fill(ds)

(both the above work for me)

I just compared what you are doing to some code in an application I have been working on that Imports CSV files to new Access
Database files and the lines above seems to be the only difference I can see. As Paul asked which row are you missing? It would
have to be either the first or the last one. Have you tried importing the CSV file into Access (build a new mdb & table) to see
if the missing row shows up there?
james
 
Still doesn't work

james said:
Jonathan, try changing :
dg.DataSource = ds.Tables(0)
to:
dg.DataSource = ds.Tables(0).DefaultView

Also, you might want to change this:
da.Fill(ds,"CSV")
to:
da.Fill(ds)

(both the above work for me)

I just compared what you are doing to some code in an application I have been working on that Imports CSV files to new Access
Database files and the lines above seems to be the only difference I can see. As Paul asked which row are you missing? It would
have to be either the first or the last one. Have you tried importing the CSV file into Access (build a new mdb & table) to see
if the missing row shows up there?
james
Click to expand...
 
¤ First row
¤

I have to say that this doesn't make any sense unless HDR is set to a value of YES. You're not using
a schema.ini file that would override this setting in the connection string are you? Are your column
names F1, F2, F3, etc?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top