Help editing SQL string to import a text file into Access

[Guest]

Hello, I'm using Access 2002

I use the following code to import data from Excel into an Access DB table. Could someone please tell me if this SQL string can be edited to instead do the same thing with a text file, and if so, edit the string for me

"SELECT [SheetName$].* INTO [DestinationTable] FROM [SheetName$] IN 'FullExcelName'[EXCEL 5.0;];

Also, I'm assuming the "Excel 5.0" portion of the above string indicates version 5 of Excel? What would be other acceptable arguments for this? I have tried some, but they don't seem to work

Thanks so much in advance

 

[Dale Fye]

Quartz,

Check out the TransferDatabase action, I think it will give you the
functionality you are looking for, and will even let you define input
specifications.

Dale

Hello, I'm using Access 2002.

I use the following code to import data from Excel into an Access DB

table. Could someone please tell me if this SQL string can be edited to
instead do the same thing with a text file, and if so, edit the string for
me?

"SELECT [SheetName$].* INTO [DestinationTable] FROM [SheetName$] IN 'FullExcelName'[EXCEL 5.0;];"

Also, I'm assuming the "Excel 5.0" portion of the above string indicates

version 5 of Excel? What would be other acceptable arguments for this? I
have tried some, but they don't seem to work.

 

[david epsom dot com dot au]

"SELECT [SheetName$].* INTO [DestinationTable] FROM [SheetName$] IN
'FullExcelName'[EXCEL 5.0;];"

"SELECT * INTO [DestinationTable] FROM [Text;DATABASE=c:\folder].[a.txt];"

When working with text files (as with Paradox and other DOS based database
systems), the FOLDER is the database, the FILE is the table. (There are
several different ways of writing the actual SQL string).

Other database types accepted are [ODBC; [EXCEL 8.0; [dBASE IV; [Excel 97;
and [; (jet mdb database). Look for help on the 'connect property'.

If you EXPORT a text file first, Jet will create a schema.ini file in the
folder which describes the table you exported. You can modify that to get
more control over I/O. The schema file can also be used in TransferText
statements as an alternative to an IMEX spec.

(david)

Hello, I'm using Access 2002.

I use the following code to import data from Excel into an Access DB

table. Could someone please tell me if this SQL string can be edited to
instead do the same thing with a text file, and if so, edit the string for
me?

"SELECT [SheetName$].* INTO [DestinationTable] FROM [SheetName$] IN 'FullExcelName'[EXCEL 5.0;];"

Also, I'm assuming the "Excel 5.0" portion of the above string indicates

version 5 of Excel? What would be other acceptable arguments for this? I
have tried some, but they don't seem to work.

 

[Guest]

Thanks David! It works great!

Can you please give me an ODBC example as well?

For example, my connection string looks like this:

"PROVIDER=MSDASQL.1;DRIVER={ORACLE ODBC DRIVER};SERVER=Ora;DBQ=Ora;UID=User;PWD=User;"

Thanks again.

 

[david epsom dot com dot au]

Can you please give me an ODBC example as well?

SELECT * INTO [DestinationTable] FROM
[ODBC;DATABASE=mydb; DRIVER={ORACLE ODBC DRIVER}; SERVER=Ora; DBQ=Ora;
UID=User; PWD=User].[source_table.txt];"

Linked tables use a DAO/Jet connection string. I think the
example you give below is an ADO connection string? Similar,
but not exactly the same. You can get the DAO/Jet connection
string from a linked table like this in the immediate window:
?codedb.tabledefs("link_name").connect

or by looking in the msysobjects system table in an MDB.

(david)

 

[Ron Gold]

Quartz,

Check out the TransferDatabase action, I think it will give you the
functionality you are looking for, and will even let you define input
specifications.

Dale

Hello, I'm using Access 2002.

I use the following code to import data from Excel into an Access DB

table. Could someone please tell me if this SQL string can be edited to
instead do the same thing with a text file, and if so, edit the string for
me?

"SELECT [SheetName$].* INTO [DestinationTable] FROM [SheetName$] IN 'FullExcelName'[EXCEL 5.0;];"

Also, I'm assuming the "Excel 5.0" portion of the above string indicates

version 5 of Excel? What would be other acceptable arguments for this? I
have tried some, but they don't seem to work.

Thanks so much in advance!