Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import CSV fieldname problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import CSV fieldname problem
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 On 6 Feb, 07:21, SingLoke wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import CSV fieldname problem
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. "RichardSchollar" wrote: 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 On 6 Feb, 07:21, SingLoke wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import CSV fieldname problem
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" wrote: 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 On 6 Feb, 07:21, SingLoke wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import CSV fieldname problem
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 On 6 Feb, 09:37, SingLoke wrote: 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" wrote: 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 On 6 Feb, 07:21, SingLoke wrote: 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- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import CSV fieldname problem
I got another message:
"Invalid bracketing of name '[testing_A.1D]'. .... "RichardSchollar" wrote: 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 On 6 Feb, 09:37, SingLoke wrote: 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" wrote: 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 On 6 Feb, 07:21, SingLoke wrote: 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- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import CSV fieldname problem
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? On 6 Feb, 11:02, SingLoke wrote: I got another message: "Invalid bracketing of name '[testing_A.1D]'. ... "RichardSchollar" wrote: 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 On 6 Feb, 09:37, SingLoke wrote: 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" wrote: 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 On 6 Feb, 07:21, SingLoke wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Problem | New Users to Excel | |||
Import problem | New Users to Excel | |||
How to know Rank Of FieldName ?? | Excel Worksheet Functions | |||
Save file as filename plus fieldname | Excel Programming | |||
Import problem | New Users to Excel |