Friday, February 3, 2017

Importing a text file into MS Access without errors - Part 2

In the previous post we saw that the wizard imports a text file with errors. In particular the columns with time related data were not imported.

In order to import correctly without errors you may need to pay attention to the default format of the OrderDate and RequiredDate columns.

During importing the FFD.txt when you come to this step of the wizard:


AccTxtImportJan3_00

You notice that the date columns contain duplicates of data. Change the Indexed field as shown(change to Yes(Duplicates OK)):


AccTxtImportJan3_01.png

Click the Advanced... button at the bottom of the above screen.

This displays the FFD Import Specification as shown.

AccTxtImportJan3_02.png

Change the Date Order to YMD (from MDY)
Change the Date Delimiter to - (from /)

Click OK.
Click Next.

In the next screen of Import Text Wizard allow Access to add Primary Key.

Click Finish.
The data is imported without errors as shown.


AccTxtImportJan3_03.png

Looks like the time information did not come through. This is because they are all zeros.

I just added a time information for the first three records in the order date and renamed the text file to FFD3.txt.



When this was imported following exactly the same steps as before the time information was also imported as shown.


AccTxtImportJan3_04.png

Access has the right tool to import the text file except that close attention must be paid to the data that is imported.