Importing data from delimited text file in Access

Hello to all! I was assigned a task which involves importing data from a delimited text file in already created tables in Access. I haven't worked at all with vb in Access, so it is kinda difficult for me to make the above task "automated". Is there a way to do this via SQL in Access? The location of the text files is pre-defined. I would appreciate your help, cause I am in big trouble! :-)

Vorias,
It's not as daunting as it first appears...
Basically, you'll use the TransferText function to programmatically bring
the Ascii/Delimited data into Access.
Here's a sample that imports an Ascii delimited file, using a schema.ini
file called MyImportSpecs, to a table in Access called tblUpdateData.

DoCmd.TransferText acImportDelim, "MyImportSpecs", "tblUpdateData",
[UpdateFilePath] & "\" & [UpdateFileName]

Before I run the command, I get the UpdateFilePath & UpdateFileName from
the user via 2 input boxes, and plug those variables into the function.
([UpdateFilePath] & "\" & [UpdateFileName]).
I develop an Import specification file by importing the data via the
wizard, until I get all the specs correct, and the file data imports
correctly... then using Advanced, I save those Import specs in a schema.ini
file (MyImportSpecs)

Go to Help under TransferText, and build your TransferText function...
step by step.

hth
Al Camp


"Vorias Peter" Vorias wrote in message
...
Hello to all! I was assigned a task which involves importing data from a
delimited text file in already created tables in Access. I haven't worked at
all with vb in Access, so it is kinda difficult for me to make the above
task "automated". Is there a way to do this via SQL in Access? The location
of the text files is pre-defined. I would appreciate your help, cause I am
in big trouble! :-)

hi,

Vorias Peter wrote:
Is there a way to do this via SQL in Access?

I don't know the correct syntax, but the solution reads somewhat like that:

INSERT INTO YourTable SELECT * FROM [YourTextFile, Specfication for Import]

The other way is to import the text file manually, creating your import
specification and save it. After this you can use DoCmd.TransferText do
redo this job.

-- stefan --

Have you tried iimporting a few text files by using file-get external
data-import?



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Thanks man, you don't know from what kind of trouble you got me out! :-)

"AlCamp" wrote:

Vorias,
It's not as daunting as it first appears...
Basically, you'll use the TransferText function to programmatically bring
the Ascii/Delimited data into Access.
Here's a sample that imports an Ascii delimited file, using a schema.ini
file called MyImportSpecs, to a table in Access called tblUpdateData.

DoCmd.TransferText acImportDelim, "MyImportSpecs", "tblUpdateData",
[UpdateFilePath] & "\" & [UpdateFileName]

Before I run the command, I get the UpdateFilePath & UpdateFileName from
the user via 2 input boxes, and plug those variables into the function.
([UpdateFilePath] & "\" & [UpdateFileName]).
I develop an Import specification file by importing the data via the
wizard, until I get all the specs correct, and the file data imports
correctly... then using Advanced, I save those Import specs in a schema.ini
file (MyImportSpecs)

Go to Help under TransferText, and build your TransferText function...
step by step.

hth
Al Camp


"Vorias Peter" Vorias wrote in message
...
Hello to all! I was assigned a task which involves importing data from a
delimited text file in already created tables in Access. I haven't worked at
all with vb in Access, so it is kinda difficult for me to make the above
task "automated". Is there a way to do this via SQL in Access? The location
of the text files is pre-defined. I would appreciate your help, cause I am
in big trouble! :-)

Thanks a lot Stefan about the SQL tip. I was not sure whether such a "select" could work in Access, but now I'll give it a shot! :-)


"stefan hoffmann" wrote:

hi,

Vorias Peter wrote:
Is there a way to do this via SQL in Access?

I don't know the correct syntax, but the solution reads somewhat like that:

INSERT INTO YourTable SELECT * FROM [YourTextFile, Specfication for Import]

The other way is to import the text file manually, creating your import
specification and save it. After this you can use DoCmd.TransferText do
redo this job.

-- stefan --