Importing text via SQL: what is the proper alias in SELECT clause?

[Max Moor]

Hi All,
I'm using the SQL statement below to import from a text file. As you
see, I'm using "F1" as the alias to identify the column in the file. This
works if I'm importing from an Excel worksheet (with no column names).

strSQL = "Insert INTO tblImport (Name, Address) " & _
SELECT F1 As Name, F2 As Address " & _
FROM " & strFilename & "#" & strExtension & " " & _
IN '" & strFullFilename & "' 'Text;'"

When I try to use this syntax to import a text file, I get an error:
"No value given for one or more required parameters." Can anyone tell me
the proper syntax to alias the columns of a text file?

Max

 

['69 Camaro]

Hi, Max.

Are you receiving error # 3061? This error has similar syntax to your
stated error message, but it's not verbatim: "Too few parameters.
Expected: 2."

If so, then you'll either have to include headers (column names) in your
text file that match the column names stated in your SQL statement (F1, F2),
or else create a schema.ini file with all the settings, including column
names and data types, configured. The specific column names need to be
mapped from the columns in the text file to the corresponding columns in the
table, so you won't be able to use column aliases in this query.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Max Moor]

Are you receiving error # 3061? This error has similar syntax to your
stated error message, but it's not verbatim: "Too few parameters.
Expected: 2."

Hi, Gunny. Nope. It's -2147217904 (80040e10). "No value given for one or
more required parameters."

If so, then you'll either have to include headers (column names) in
your text file that match the column names stated in your SQL
statement (F1, F2), or else create a schema.ini file with all the
settings, including column names and data types, configured. The
specific column names need to be mapped from the columns in the text
file to the corresponding columns in the table, so you won't be able
to use column aliases in this query.

I supose I didn't state my question quite right, in either case. The "F1",
"F2", etc. are the default names for columns in an Excel spreadsheet that has
not column names. I'm looking for the default names to use for a text (tab)
of csv file, so I can assign columns to my existing table, when the text file
has no column names.

 

['69 Camaro]

Hi, Max.

Your -2147217904 (80040e10) "No value given for one or more required
parameters." error is due to your text file violating data integrity or a
constraint, such as having at least one record where a column is blank, but
the table's corresponding field requires a value. Check your fields that
you're importing into to find the ones that require values, disallow zero
lengths, disallow nulls for an index, have a validation rule that isn't
being met, et cetera. At least one of your records isn't abiding by at
least one of these rules you have set up in the design of the table or in a
relationship between this table and another.

After experimenting, I think I've found the syntax you need to add to get
those default F1 and F2 column names without using a schema.ini file:

strSQL = "Insert INTO tblImport (Name, Address) " & _
SELECT F1 As Name, F2 As Address " & _
FROM " & strFilename & "#" & strExtension & " " & _
IN '" & strFullFilename & "' 'Text;HDR=NO;'"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

['69 Camaro]

Oh, and check the table's field sizes and check for data type mismatches,
too. If the column's string length in the text file exceeds the field size
or if the data types don't match, then the value will be prevented from
being entered into that field in the record. So if that field is a
"required" field, then that would give you the error you encountered.

HTH.
Gunny

 

[Jamie Collins]

I'm using the SQL statement below to import from a text file. As you
see, I'm using "F1" as the alias to identify the column in the file. This
works if I'm importing from an Excel worksheet (with no column names).

strSQL = "Insert INTO tblImport (Name, Address) " & _
SELECT F1 As Name, F2 As Address " & _
FROM " & strFilename & "#" & strExtension & " " & _
IN '" & strFullFilename & "' 'Text;'"

When I try to use this syntax to import a text file, I get an error:
"No value given for one or more required parameters." Can anyone tell me
the proper syntax to alias the columns of a text file?

As a guess, try:

strSQL = "Insert INTO tblImport (Name, Address) " & _
SELECT F1 As Name, F2 As Address " & _
FROM " & strFilename & "#" & strExtension & " " & _
IN '" & strFullFilename & "' 'Text;HDR=No'"

Personally, I prefer the alternative syntax e.g.

FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt

Jamie.

--

 

[Max Moor]

Hi Again,
I'm getting closer via your and Jamies advice. My SQL now looks like
the below. I've found that I can import some of the file.

All my table fields are set to "text," and have more than enough
room. zero length is allowed on all, none are required, etc.

I've found that if my zipcode data looks like "98765," things are
fine. If they look like "98765-4321," I get a numerical overflow. How can
I, in the SQL statement, specify that all data should be treated as text?

- Max





strSQL = "Insert INTO tblImport (Name, Address) " & _
"SELECT F1 As Name, F2 As Address " & _
"FROM [Text;HDR=No;Database=" & strPath & ";]." & strFilename & "#" &
strExtension

 

[Jamie Collins]

I've found that if my zipcode data looks like "98765," things are
fine. If they look like "98765-4321," I get a numerical overflow. How can
I, in the SQL statement, specify that all data should be treated as text?

You can't do it in the SQL statement. Use a schema.ini file:

http://msdn.microsoft.com/library/d.../en-us/office97/html/workingwithtextfiles.asp

You will no doubt already have one in you folder (strPath), sp it
should be a case of editing it with e.g.

Col9=MyZipcode Char Width 10

Jamie.

--

 

['69 Camaro]

Hi, Max.

It doesn't matter what you do in the SQL statement to manipulate the data
type, nor does it matter the data type of the destination column. The
overriding factor is the data type of the source column. The "numerical
overflow" error is due to Access interpreting your Zip Code column as a
numerical data type, instead of a text data type.

When importing a text file via a SQL statement, there are only two ways to
control the data type of the source column:

1.) Use a schema.ini file in the same directory as the source text file to
name specific column data types; or

2.) Ensure that at least five of the first eight records in the source text
file are of the data type you want in the source column. Why five? That's
a majority of the first eight records Access scans to determine the data
type of each column in the source text file.

So, if you want to avoid creating the schema.ini file, you'll need to make
sure that five of those first eight records use the Zip+4 format in your Zip
Code column, not the five digit format.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Max Moor]

Hi, Max.

It doesn't matter what you do in the SQL statement to manipulate the
data type, nor does it matter the data type of the destination column.
The overriding factor is the data type of the source column. The
"numerical overflow" error is due to Access interpreting your Zip Code
column as a numerical data type, instead of a text data type.

When importing a text file via a SQL statement, there are only two
ways to control the data type of the source column:

1.) Use a schema.ini file in the same directory as the source text
file to name specific column data types; or

2.) Ensure that at least five of the first eight records in the
source text file are of the data type you want in the source column.
Why five? That's a majority of the first eight records Access scans
to determine the data type of each column in the source text file.

So, if you want to avoid creating the schema.ini file, you'll need to
make sure that five of those first eight records use the Zip+4 format
in your Zip Code column, not the five digit format.

HTH.
Gunny

Hi Gunny,
I guess I'm not sure what I'll do at this point, but at least I know
my options. Thanks for the help!

- Max

 

[Max Moor]

(e-mail address removed) (Jamie Collins) wrote in

You can't do it in the SQL statement. Use a schema.ini file:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office
97/html/workingwithtextfiles.asp

You will no doubt already have one in you folder (strPath), sp it
should be a case of editing it with e.g.

Col9=MyZipcode Char Width 10

Jamie.

--

Hi Jamie,
As I told Gunny, I don't know for sure what I'll end up doing. At
least I understand my choices now. Thanks for the link. It's a good
reference.

Thanks, Max