Inserting a column during importing of File

[Guest]

I want to know if there is a way that I can insert a new column on a file
during the importing process. What I mean is that I am importing 35 text
files, per day, onto my database. Now the thing is that these files doesn't
have a column for dates, so when i import them to a table, there is no way
for me to figure out what dates the files are from.
Just want to have a way, so that when i import, there will be like a window
dialog that will prompt me to give the file a date and that date will somehow
be created in a new colum.

 

[Roger Carlson]

Not with the standard import feature, but you can write code to do it.
There are several ways you can do this, but I'd need more information.

Are these files imported into separate tables or into a single master table?
What are examples of the table name(s) and field name(s). Where do the
files reside? In the same directory as the database or somewhere else? If
elsewhere, what's the actual path? (Details like this help us to craft a
more complete answer.) Are you at all familiar with VB coding?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Douglas J Steele]

Import them into an existing table that has a date field, with its default
set to Date(), rather than importing them into a new table each time.

 

[Guest]

The 35 files can be uploaded to 35 tables in the database, one for each file
or one table, if there is another column for location.
Right now, the file has these names as columns: Batch#, Trans Type, Card
Number, Amount, Autho#, Cashier. I need to add a column for the date for each
section of the file. So if one file has 32 lines of info, each line will have
to have a date. If i was to import all 35 files to one table, then i would
need another column for location. That will def. be the better choice for
this project.
The files are in the same directory as the database

 

[Roger Carlson]

There are a number of ways to do this. One of the easiest (though least
elegant) is to do something like this:

1) Create 35 tables, one for each file. In these tables, have two extra
columns: Location (Text) and FileDate (Date/Time). Set the default value
for Location to the location that file is for. Set the FileDate default
value to Date(). (Borrowing from Doug here.)

2) Before each update, clear the tables.

3) Import each file into the appropriate table.

4) Append the data from each table into the master table, which also has the
Location and FileDate fields.

Steps 2-4 can be automated in a macro or in VB. If you're not familiar with
VB coding, I suggest creating a macro and then converting it to VB code.
(You can use the SaveAs on the File menu to save the macro as a VB module.)
This will add error trapping to the code.

One of the difficulties with this method is that repeated importing and
clearing of temporary tables can cause your database to bloat. You'll have
to Compact it periodically.

As I said, there are other ways that do not have this problem and if you
want me to address them, just say so.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Is there a way to have one table with 2 add'l column and when importing, user
is being prompt to give file a date and location (which will be added to the
line itself)
for instance, if i get a file that i want to import, i would just like to be
prompt, location and date and then import

I can't do the date() column thing cause i can get a file from a week ago
but when i import, it is going to add the current date.

 

[Guest]

Is there a way to have one table with 2 add'l column and when importing, user
is being prompt to give file a date and location (which will be added to the
line itself)
for instance, if i get a file that i want to import, i would just like to be
prompt, location and date and then import

I can't do the date() column thing cause i can get a file from a week ago
but when i import, it is going to add the current date.

 

[Roger Carlson]

Not in the import itself, but you can have a parameter in the Append query.
Thing is, you still have to import the file to a temporary table, then
append the data to you main table. Try this:

1) Create 1 temporary table called TempTable.
2) Create your Append Query. It would look something like this:

PARAMETERS [Enter File Date] DateTime;
INSERT INTO MainTable ( [Batch#], [Trans Type], [Card Number], [Amount],
[Autho#], Cashier, Location, FileDate)
SELECT [Batch#], [Trans Type], [Card Number], [Amount], [Autho#], Cashier,
[Enter Location] As Location, [Enter File Date] As FileDate
FROM TempTable;

Note: The Parameters statement will guarentee the user inputs a date.
Note2: There's no backing out with this. If they type the Location or Date
wrong, it will go in wrong into the main table. You'd have to Delete them
after the fact.

3) Clear the TempTable table.
4) Import your file to the existing TempTable table
5) Run the Append query.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L