Import CSV fieldname problem

[Guest]

I'm tring to import a csv file by using the following code:

Dim csvFolder As String
Dim sConnectionString As String
Dim objConn As New ADODB.Connection
Dim rs As New Recordset

csvFolder = "C:\csvdata\"
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & csvFolder & ";Extended Properties=Text;"
objConn.Open sConnectionString

SelectStr = "SELECT testing_A1D from Main.csv"
rs.Open SelectStr, objConn, adOpenDynamic

When I run the code, I got the following error message:
"No value given for one or more required parameters"

But if I try to use "Select * " I can get the data. However, I noted that
the fieldname changed to "testing_A#1D".

I also tried "SELECT testing_A#1D from Main.csv", but I got another error:
Syntax errro in date in query expression 'testing_A#1D'

Please advice how to solve the problem. Thanks

 

[RichardSchollar]

Hi SingLoke

I am unable to recreate the error you are seeing (the code works
perfectly on my machine). The only time I get that error is when the
field name (eg testing_A1D) does not match up to a field name in the
target csv file.

Hence, I suspect that although they may look the same (ie the SQL
query name and the csv file header) they differ in some way. Try
copying from your SQL string into the csv and saving the csv down, and
seeing if that makes a difference.

Hope this helps!

Richard

 

[Guest]

Actually, if I imported the csv into an access database, I can run the SQL
statement (same as the one I run in Excel VBA) in Access.

The only difference is the fieldname.

If you can send me your email, I can send my csv file to you. Thanks.

 

[Guest]

I found the problem. The oringal fieldname has a "." in it (e.g.
"testing_A.1D", Access just deleted the dot while in VBA, it changed the "."
to "#".

So, when I try to apply "Select testing_A.1D from main.csv" I got an error
message. do you have any idea on how to select the "testing_A.1D" from the
csv file?

Thanks.

 

[RichardSchollar]

Sure - write your query string as:

SelectStr = "SELECT [testing_A.1D] from Main.csv"

The [] brackets should mean you can import without a problem.

Best regards

Richard

 

[Guest]

I got another message:

"Invalid bracketing of name '[testing_A.1D]'.

....

Sure - write your query string as:

SelectStr = "SELECT [testing_A.1D] from Main.csv"

The [] brackets should mean you can import without a problem.

Best regards

Richard

I found the problem. The oringal fieldname has a "." in it (e.g.
"testing_A.1D", Access just deleted the dot while in VBA, it changed the "."
to "#".

So, when I try to apply "Select testing_A.1D from main.csv" I got an error
message. do you have any idea on how to select the "testing_A.1D" from the
csv file?

Thanks.















- Show quoted text -

 

[RichardSchollar]

I must apologies - it appears I am incorrect and you may not be able
to only specify that column for import (I thought the square brackets
would work, but obviously they did not). Is there any chance you can
either:

1. Change the name of this field (ie so it does not contain the dot
(.))

or

2. Import all columns (ie SELECT * FROM....) into the recordset, and
then only extract the one you want?

I got another message:

"Invalid bracketing of name '[testing_A.1D]'.

...

Sure - write your query string as:

SelectStr = "SELECT [testing_A.1D] from Main.csv"

The [] brackets should mean you can import without a problem.

Best regards

- Show quoted text -