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:


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


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

This displays the FFD Import Specification as shown.


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.


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.


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

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...