Insert date into file import

[b_lwalker]

I have a database that imports a .csv file generated by another
program. Unfortunately this program doesn't include a date in each
record in the .csv file.

I can import the .csv file into my table alright, but I need to attach/
insert a date (which I can get via VB code) into each imported
record. Any ideas?

At the moment I'm using the Docmd.TransferText command.

 

[tina]

have you tried importing the data, then running an Update query on the
table, to add a date to each record?

hth

 

[Allen Browne]

Importing is usually an error-prone process. Even if the CSV is generated by
other software, you still have potential issues so sort out. Things like:
- problems with currency symbols
- problems with dates
- zero-length strings instead of nulls,
- trailing spaces,
- validation rules not met,
- text that needs to be converted to numbers for foreign keys,
- foreign key values need to be added to a lookup table before import,
- line-wraps in the wrong places,
- duplicates of existing entries,
- users importing the same file multiple times,
- needing to write to multiple tables (e.g. Orders header entry, then
details),
- etc, etc.

To sort out this mess, what I find I normally have to do is to create a
temporary import table with just Text columns. Then run a series of queries
to test for all the invalid things you consider likely. You write each issue
to another table, flagging each issue as Critical or Warning. The interface
lets the user import the file (into the temp table), performs the analysis,
handles the lookups, displays the problems in a list box, and offers an
interface into the temp table for editing. Once all critical problems are
handles, you enable the command button for the final step which adds the
data to your real tables.

This final button creates a record in an ImportBatch table, which is
structured so you can recogize if the file has already been imported (same
file name and file date/time.) You then execute a series of append query
statements to write the data to your real tables, inside a transaction so
you can rollback if there was an unforeseen error. The real target table has
a BatchID foreign key. Use that key value to identify where the import file
name and date, and the user who performed the import.

The BatchID foreign key means you can also offer an "Undo last import"
option.

Suggested ImportBatch table fields:
ImportBatchID AutoNumber
ImportFile Text (full path and file name)
ImportFileDateTime Date/Time (date/time of the file)
ImportBy Text (user name)
ImportDateTime Date/Time (when imported)

Hope you can adapt those ideas to your specific needs.

 

[Dirk Goldgar]

In

I have a database that imports a .csv file generated by another
program. Unfortunately this program doesn't include a date in each
record in the .csv file.

I can import the .csv file into my table alright, but I need to
attach/ insert a date (which I can get via VB code) into each imported
record. Any ideas?

At the moment I'm using the Docmd.TransferText command.

I'd probably link to the file, rather than importing it directly, and
then use an append query to copy the records from the linked table to
the final target. I'd let that query insert the date as a calculated
field in the query.

 

[b_lwalker]

Wow Allen - that sounds pretty comprehensive. Probably a little more
comprehensive than what I'm after for this particular application -
however if you have links to any examples that would be greatly
appreciated. I could also use that to sort out my error table problem
too.

Many thanks,

Bruce

 

[Allen Browne]

I don't have an example db for download.

Naturally not all issues apply in every case, Bruce. The list is just a
memory jogger.

It's easy enough to create a temp table with all Text fields (other than an
AutoNumber p.k.) Empty the table with:
dbEngine(0)(0).Execute "DELETE FROM Table1", dbFailOnError
Then use TransferText to populate it.

Next, you run a series of Update queries to find all the problems. The WHERE
clause of each one identifies the records; the UPDATE clause sets a field to
critical or warning.

Next, load a form to show the problem records, and let the user edit to fix
them.

Hmm, I'm starting to repeat my previous thread here. There's a few hours
work involved in setting this up, but hopefully it's enough to get you on
the track.

 

[b_lwalker]

Thank you Allen. I never knew about the dbEngine command
before...that's another one to remember!!

Worked a charm - cheers.