Importing data from delimited text file in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
Hello to all! I was assigned a task which involves importing data from a
Click to expand...
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 said:
Is there a way to do this via SQL in Access?
Click to expand...

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?
 
Thanks man, you don't know from what kind of trouble you got me out! :-)

AlCamp said:
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 said:
Hello to all! I was assigned a task which involves importing data from a
Click to expand...
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! :-)
Click to expand...
 
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 said:
hi,

Vorias said:
Is there a way to do this via SQL in Access?
Click to expand...

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 <--
Click to expand...
 
Back
Top