CSV select not reading the first line

[tshad]

I have a program that is reading a .csv file into a dataset and works fine
except that it is dropping the first line. I assume that is because it is
dropping the header. The problem is the first line is not a header.

This was working before and I am not sure what caused it not to work. I am
using OleDbDataAdapter.

Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim conn As New OleDb.OleDbConnection(ConStr)

Dim da As New OleDb.OleDbDataAdapter("Select * from " & pathfile, conn)
da.Fill(DataSetObj)

When I look at the DataSet, all the rows are there except the first line.

I also change the HDR parameter to HDR=Yes just to see what would happen and
it still dropped the header.

What is happening here?

Thanks,

Tom

 

[tshad]

I figured out what was happening, but not why.

It seems that if I run the program as:

Dim da As New OleDb.OleDbDataAdapter("Select * from " &
"c:\importFile\temp.csv", conn)

It drops the first line. If I run it as

Dim da As New OleDb.OleDbDataAdapter("Select * from " & "temp.csv", conn)

It doesn't drop the first line. That is the only difference. What would
cause this to happen?

Thanks,

Tom

 

[Herfried K. Wagner [MVP]]

It seems that if I run the program as:

Dim da As New OleDb.OleDbDataAdapter("Select * from " &
"c:\importFile\temp.csv", conn)

It drops the first line. If I run it as

Dim da As New OleDb.OleDbDataAdapter("Select * from " & "temp.csv", conn)

It doesn't drop the first line. That is the only difference. What would
cause this to happen?

Maybe the first line is considered to contain column names? I suggest to
take a look at the connection string used to instantiate the connection
object. It's possible to specify wether to interpret the first row as
column header line there by setting 'HDR' to 'yes' or 'no' there.

Further information:

<URL:http://www.connectionstrings.com/?carrier=textfile>

 

[tshad]

I was looking at the connection string and realized that I was specifying
the directory in the select statement. This seems to be the problem.

If you notice, I have "c:\ImportFile\temp.csv" instead of "temp.csv" in my
select statement. This is exactly the same since temp.csv is in the
c:\importFile directory. I could understand the problem if I was changing
the folder from the connection string. But here is the Connection string I
am using:

Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""

Here "Path" is equal to "c:\ImportFile\" (which is why my select statment
with no path works correctly). But I am not changing the directory so why
would the program act differently? It obviously has nothing to do with
schemi.ini files or being in a different directory. It is exactly the same.
So why does it take out the header (1st line) in one and not the other????

Thanks,

Tom

 

[Herfried K. Wagner [MVP]]

Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""

First, I do not know the reason for the problem you are experiencing.
However, you may have to remove the backslash prior to the last double quote
in the connection string. Double quotes cannot be escaped by preceeding
them with a backslash in VB.NET. Instead, they are simply duplicated inside
the string literal, which is already the case in your code snippet.