I am working on importing a tab delimited text file using transfer text to a temp table. I created a specification, but for some reason Access will not translate 3 of the fields to date/time types. To get around that I attempted to make them all text which worked great, however it makes the next bit seem sticky.
The data I'm importing is expense information going into a temp table. From the temp table I'll compare it to the existing data to insure its correctness. Any data that is being imported that seems fishy for one reason or another will be sent to another table for later review by the user.
My question is (finally) can I take my existing temp table with its text instead of date fields and simply change the type of the field to date/time? Or will I have to append on new date fields to the temp table (along with others I'll need to add for the import) and then cast the string date vals into the new date fields? Or am I going about this all bass ackwards and someone has a much better idea of how to accomplish all this? Any replies to my novella appreciated. TIA
You can easily change the text field to date field if the imported date meets Access date requirements which are quite flexible. The better way is to have a permanent temp table structure where the fields in question are defined as dates so that you won't have to change the table structure.
If I have the permanent table structure will those date automatically be cast as dates. I attempted to put them in as dates but I came out with type conversion errors in a paste errors table. The dates are in the form d/m/y but the look like this 14/Aug/2004. For some reason no matter what setting I use in the import spec I seem to develop the same problem. Won't using the permanent table structure cause the same errors. If it is possible to change the field to date through code. What is the process. I've tried to alter the field.type property but had no luck.
Your answer gives the clue. I assume that it is an Excel file. There is something wrong with the data you're importing, and it's not the date format since that is only a display format. The problem is that the data file is not clean. Before import, open the file in a text editor and search the text for quotation marks ("). The row that contains them is where the import fails. You can test it by carefully removing the quotes, taking care that you don't delete the tabs. If the file is clean, it wouldn't matter whether you are importing the data into a permanent table, or creating the table on import. Permanent is better because you would have everything predefined. Let me know the results and post a reply about it.
Actually the file is a .txt file so there is no formatting added to the characters except for an odd shaped little box at the start of each line. The really wierd thing is that if I try to import with any date format it fails to import. If I make it a text field it imports fine. Then if I go to the table and change the field type to date Access makes no complaint at that point to change it all to date types. I've attached the text file if you want to look. Any ideas why this is happening this way?
Your file is perfectly clean. The odd shaped characters at the beginning do not show up on my text editor (TextPad 4.7.2 - 32 bit) but they are begin/end of line formatting characters which Access and TextPad automatically recognize.
Now for the import. On the Control panel\Regional settings\Medium date formats I set the format to exactly as you had it in the file: dd mmm yyyy. I tried the same with the time field, but this is unfortunately must be text because Access treats date data with : as text. I know that from VBA programming.
Next, I went to Access and clicked on External data\Import. On the Import file dialog, Advanced options, I adjusted the field settings, (corrected Access guesses) and saved the import specs for later reuse. Then I imported the file without a problem. Now you can re-use the same file structure.
Table name: 00016143
No of fields: 9
Record count: 112
0001 - 0256 255 = Vehicle Card Number Text
0256 - 0264 8 = Transaction Date Date/Time
0264 - 0272 8 = Posted Date Date/Time
0272 - 0527 255 = Merchant Name Text
0527 - 0535 8 = Fuel Quantity Double
0535 - 0790 255 = Customer Vehicle ID Text
0790 - 1045 255 = Driver Last Name Text
1045 - 1056 11 = Transaction Time Text (Known length)
1056 - 1064 8 = Gross Sales Dollars Currency
1064 - 1072 8 = Odometer Reading Double
As a footnote. Control panel Date format rules apply only on import, otherwise you can change the display format to anything you wish in Access. One cannot change the field structure of a table in VB. The process is to build an identical table from the orginial with the right field type and transfer the data in code. It's not worth it, even if you do the import on a daily basis. I am attaching the datatable with the saved import format specs.