Importing .csv file into a datatable VB.NET

[Jonathan]

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.

[Cor Ligthert]

Jonathan,

I did not try it however did you try not to use that parameter.

Cor

[Paul Clement]

On Tue, 15 Mar 2005 09:33:04 -0800, "Jonathan" <Jo******@discussions.microsoft.com> wrote:

¤ 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)

[james]

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
"Jonathan" <Jo******@discussions.microsoft.com> wrote in message news:E8**********************************@microsof t.com...

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.

[Jonathan]

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
"Paul Clement" wrote:

On Tue, 15 Mar 2005 09:33:04 -0800, "Jonathan" <Jo******@discussions.microsoft.com> wrote:

¤ 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 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.

"james" wrote:

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
"Jonathan" <Jo******@discussions.microsoft.com> wrote in message news:E8**********************************@microsof t.com...

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.

[Jonathan]

Yes I tried not using the paramter but it didn't work

"Cor Ligthert" wrote:

Jonathan,

I did not try it however did you try not to use that parameter.

Cor

[shriop]

You're not using anything specific to .Net. You're using the exact same
thing that Access would use to open this CSV file. I'd recommend you
use a different parser. http://www.geocities.com/shriop/index.html

[Paul Clement]

On Wed, 16 Mar 2005 01:27:06 -0800, "Jonathan" <Jo******@discussions.microsoft.com> wrote:

¤ 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)

[james]

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

"Jonathan" <Jo******@discussions.microsoft.com> wrote in message news:B8**********************************@microsof t.com...

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
"Paul Clement" wrote:

On Tue, 15 Mar 2005 09:33:04 -0800, "Jonathan" <Jo******@discussions.microsoft.com> wrote:

¤ 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]

Still doesn't work

"james" wrote:

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

"Jonathan" <Jo******@discussions.microsoft.com> wrote in message news:B8**********************************@microsof t.com...

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
"Paul Clement" wrote:

On Tue, 15 Mar 2005 09:33:04 -0800, "Jonathan" <Jo******@discussions.microsoft.com> wrote:

¤ 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]

First row

"Paul Clement" wrote:

On Wed, 16 Mar 2005 01:27:06 -0800, "Jonathan" <Jo******@discussions.microsoft.com> wrote:

¤ 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)

[Paul Clement]

On Fri, 18 Mar 2005 04:55:02 -0800, "Jonathan" <Jo******@discussions.microsoft.com> wrote:

¤ 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)