Importing from text file to Access

  • Thread starter Thread starter SHARON
  • Start date Start date
S

SHARON

Hello:

Does anyone know how to use macro or code to automatically
import a text file into Access. The file is a delimited,
comma file. I've tried the transfer text macro but cannot
get it to work. Any suggestions and examples would be
great!

Thanks,
Sharon
 
I need specifics, are you importing to a table already
created? you can use and save import specs via "get
external data" and create a macro to import the data into
a new or existing table using "transfer text" hope this
helps.

Ken
 
Hello Ken:

I'm trying to automate a process to import a text file. I
created a macro using transfertext action. It's working
but I'm finding the transfertext action isn't giving me
the options the manual import wizard does. Is there a way
to include the following in my macro or can this be done
with code?

First, the file I need to import has two lines that are
rejecting out into an error table. I don't need to see
these two records, so how can I keep the macro from
creating the error table?

Secondly, there's only one column in the text file. It
doesn't have a header name. Is there a way to have it name
the field while it's importing.

And last, if I import this file manually, I get a prompt
to overwrite the table. How can I get the macro to let me
either overwrite the old table or append to the old data?
Both options need to be available for the user.

Thanks for your assistance,
Sharon
 
1. It is giving you an error table because a) you have a
table defined and are importing into this table and access
does not like the format of the field...numeric..text. To
stop the error set the field to text at a long enough
length. b) you don't have a table and manually define the
field and access does not like the format you have defined.

2. Obviously since you are asking about overwritting the
table you have defined a table to be overwritten when you
import records manually. We can probably tell it to
overwrite or append but should we...both options need to
be available...

solution:

1) Create a table, if your data is a string of text or a
numeric value create the field as such. Let's say you are
defining a comment field.

name: value length
comment text 50

save and name the table.

2) Now get external data, define the field and import into
the table you have saved. Before you finish the import
click advanced tab and save as and remember the name you
use because you can use that import in your macro to
import text.

You can do what you think is the easiest, create a master
table and a secondary table import to the secondary and
create a query to delete and append records back and forth
or just delete the records before importing, what ever
works for you.

Ken
 
did anyone know a solution for this?

i have an "access database" and 2 tables
and i wanna read from a text file, and then insert datas to my database , which has a formation like

John,Saler,20,July,50000,Washington
Mary,Manager,25,October,120000,Chicago

etc...

if you could help me , i will be glad
 
Sekonde,

From your Access database, go to the File|Get External Data|Import
menu, and then follow the steps of the wizard to get the data from the
text file into an Access table. Please post back here if you need any
further help with this.
 
Hello
First import text manually, this will allow you to save your own import
specyfications along the way. Then create makro - Transfer Text.
You can then attach the macro to the button. Works like a charm.

Barb
 
Thanks for your attention but i need a C,Java or another code
to make this automaticaly. I wanna that When i start this code program
it will insert these datas from text to my database in to tables..

not from menu.
confused.gif

Steve Schapel said:
Sekonde,

From your Access database, go to the File|Get External Data|Import
menu, and then follow the steps of the wizard to get the data from the
text file into an Access table. Please post back here if you need any
further help with this.

--
Steve Schapel, Microsoft Access MVP
Click to expand...
 
Sekonde,

Well, since we are talking about an Access database here, it is unlikely
that we would look at C or Java. As mentioned by Barb, you could use a
macro with the TransferText action, and an Import Specification which
you can define by running through the process manually via the menu
first. Or you could use a VBA procedure.
 
ok forget about c or java code :)
now i want to do it by access

in my database there ara two tables
1-personal
ID,name,surname,birth_place
2-pass
ID,password,password_date

my text file's format is
ID,name,surname,birthplace,password,password_date

how can i transfer this text file into my database
it should automaticaly place datas in to correct table?

if you could help, i will be glad to you :)
 
Hi SecondeR.

It looks to me like you need to transfer text twice.Once for each table you
populating.
Like Steve mentioned: Go to File, Get external Data, Import... follow the
wizard and save your Import specifications. Do it twice.Then create a macro
with two TransferText actions. After that you can atach the macro to a button.

Thanks
Barb
 
i dont know access very much i am working oracle more.

but in this problem i must do it in access. so could you help and say me how can i create this macro?

could someone tell me this in detail?
 
Back
Top