import mm/dd/yyyy hh:mm:ss AM date format

[Guest]

I receive a text file (.csv file) with dates in it in mm/dd/yyyy hh:mm:ss AM
format (e.g. 1/3/2006 2:07:01 PM). I am trying to import this to a date/time
column in my database using the TransferText action but I get an import error
of Type Conversion Failure. I get this using the TransferText action or
manually clicking File / Get External Data / Import...

I've tried creating an input mask, I've clicked the "Advanced" button to
create an Import Spec but still with no luck. The best I have been able to
do so far is import it to a text field.

If I can't import this directly to a date/time field my plan b is to bring
it in to a text field and then (in an automated way, preferably using actions
in a macro) convert the text field to date/time. Any advise on this is
appreciated as well.

Thanks

 

[Guest]

Use an intermediate transfer table. In that table, define the field as text.
The append the data imported from the text file to the intermediate table to
your "live" table. I think it will take care of the data conversion. If
not, you can do the conversion in the query you use to move data from the
intermediate to the live table. The order of events would be:
Delete all data in the intermediate table
Do the transfer to the intermediate table
Run the Append query to move the data to the live table.

 

[Biswajit Jena]

Hi,

I am in same situation. However I am stuck in this line

DoCmd.TransferText acImportDelim, "tblTempUploadWeir", text.csv

Would you please tell me , where am I doing wrong ? It is not working
this way ..

Thanks
BJ

 

[Biswajit Jena]

Hi,

I am using the following line to import a CSV file to access.

DoCmd.TransferText acImportDelim, "tblTempUploadWeir", test.csv

But its not working. Seems some basic error.

Would you tell me the correct one please ?

Thanks
BJ

 

[Guest]

I believe your suggestion will work, but I have 39 tables to do this in.
Also this will cause my database to double in size which could cause problems
in some cases. I have added the RunCommand action with the command
CompactDatabase to the macro, but I get the error message "You can't compact
the open database while running a macro or visual basic code. Do you have
any suggestions for how I can make this more efficient?

Thanks

 

[Guest]

If you need to keep database size down, then delete the data from the
intermediate tables after you have done the append. I would still leave the
code in to delete the data prior to the append so in case there is an error
that leaves data in the intermediate table, you wont get something you don't
want.

As to the compact problem, since I never use Macros or Actions (I do
everything with VBA and methods, I can't say what will solve your problem.
If you have a size issue, I would suggest you set your database up to compact
on close.

Do you have that much data? The size limit for an mdb is 2 gig. Now, if
you are committing the sin of running an unsplit database and it is shared on
a server, then you will have to accept performance degradation.