Querying a CSV File

  • Thread starter Thread starter ge0193387
  • Start date Start date
G

ge0193387

I have a csv file here that is output from another program. Once this
csv has been completed the information needs to be extracted in
multiple formats into a formatted excel spreadsheet (sheet a is
ordered a certain way with specific information and summary rows, ...)

I created the code listed below and tested it multiple times with
success. After which I ported the code to another application and it
ceased working. At first I thought the problem was with the file
being on a network location so I copied the file locally (and
schema.ini).

Dim CMD As OdbcCommand
Dim ConnDir As String = CSVFileName.Substring(0,
CSVFileName.LastIndexOf("\") + 1)
Dim Conn As New OdbcConnection("Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=" & ConnDir & ";Extensions=asc,csv,tab,txt;")
'IMEX=1;Persist Security Info=False")

Dim DR As OdbcDataReader
Dim SQL As String = "SELECT * FROM " & CSVFileName.Trim & " ORDER BY
PCN"
CMD = New OdbcCommand(SQL, Conn)
Conn.Open()
DR = CMD.ExecuteReader

'ERROR [42S02] [Microsoft][ODBC Text Driver] The Microsoft Jet
database engine could not find the object 'file.csv'. Make sure the
object exists and that you spell its name and the path name correctly.

One of the first responses I expect to see - "Is the file locked?"
A: I'm not sure how to check that but since the same code worked
before I don't see how

Any assistance is appreciated.

Christian
 
I found these notes:

<parameters>
<parameter name="Provider" value="Microsoft.Jet.OLEDB.4.0"
isSensitive="false" />
<!--
Notice the Data Source of a textfile is the DIRECTORY, not the full
path/filename
The Select statement looks like:
"Select * from mytextfile.txt"
-->

<parameter name="Data Source" value="c:\TextFilePubsEquiv\"
isSensitive="false" />
<parameter name="Extended Properties"
value="'text;HDR=YES;FMT=Delimited'" isSensitive="false" />
</parameters>


Which, let me translate.

The DataSource is the DIRECTORY.

The select statement is:
Select * from mytextfile.txt


Here is where you can screw up.

Setting the DataSource to the full path/filename.
Aka, the DataSource is NOT
c:\TextFilePubsEquiv\mytextfile.txt


and the select statement is NOT the full path/filename.
Select * from c:\TextFilePubsEquiv\mytextfile.txt


I think you're doing it right, but in you sample code (if you need to
repost) put something simpler like:


Dim ConnDir As String = "C:\mydirectory\"
dim CSVFileName as string = "mytextfile.txt"

...

Also, be very very anal about every space , semi colon, etc in your
connection string.
 
You are correct. I was just about to post that I'd solved the problem
using exactly what you said.

Thanks for the assistance.


I found these notes:

<parameters>
<parameter name="Provider" value="Microsoft.Jet.OLEDB.4.0"
isSensitive="false" />
<!--
Notice the Data Source of a textfile is the DIRECTORY, not the full
path/filename
The Select statement looks like:
"Select * from mytextfile.txt"
-->

<parameter name="Data Source" value="c:\TextFilePubsEquiv\"
isSensitive="false" />
<parameter name="Extended Properties"
value="'text;HDR=YES;FMT=Delimited'" isSensitive="false" />
</parameters>

Which, let me translate.

The DataSource is the DIRECTORY.

The select statement is:
Select * from mytextfile.txt

Here is where you can screw up.

Setting the DataSource to the full path/filename.
Aka, the DataSource is NOT
c:\TextFilePubsEquiv\mytextfile.txt

and the select statement is NOT the full path/filename.
Select * from c:\TextFilePubsEquiv\mytextfile.txt

I think you're doing it right, but in you sample code (if you need to
repost) put something simpler like:

Dim ConnDir As String = "C:\mydirectory\"
dim CSVFileName as string = "mytextfile.txt"

..

Also, be very very anal about every space , semi colon, etc in your
connection string.




I have a csv file here that is output from another program. Once this
csv has been completed the information needs to be extracted in
multiple formats into a formatted excel spreadsheet (sheet a is
ordered a certain way with specific information and summary rows, ...)
Click to expand...
I created the code listed below and tested it multiple times with
success. After which I ported the code to another application and it
ceased working. At first I thought the problem was with the file
being on a network location so I copied the file locally (and
schema.ini).
Click to expand...
Dim CMD As OdbcCommand
Dim ConnDir As String = CSVFileName.Substring(0,
CSVFileName.LastIndexOf("\") + 1)
Dim Conn As New OdbcConnection("Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=" & ConnDir & ";Extensions=asc,csv,tab,txt;")
'IMEX=1;Persist Security Info=False")
Click to expand...
Dim DR As OdbcDataReader
Dim SQL As String = "SELECT * FROM " & CSVFileName.Trim & " ORDER BY
PCN"
CMD = New OdbcCommand(SQL, Conn)
Conn.Open()
DR = CMD.ExecuteReader
Click to expand...
'ERROR [42S02] [Microsoft][ODBC Text Driver] The Microsoft Jet
database engine could not find the object 'file.csv'. Make sure the
object exists and that you spell its name and the path name correctly.
Click to expand...
One of the first responses I expect to see - "Is the file locked?"
A: I'm not sure how to check that but since the same code worked
before I don't see how
Click to expand...
Any assistance is appreciated.
Click to expand...
Christian- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top