ACC: Importing Text File with Dates in DDMMMYY Format (95/97)

ID: Q159323

The information in this article applies to:

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you import a delimited text file containing a field with values in a Medium Date format (DDMMMYY), the data for that field is not imported and an import errors table is created.


CAUSE

Microsoft Access does not recognize the Medium Date format when importing dates in a delimited text file.


RESOLUTION

To import Medium Dates in a delimited text file into a new table, follow these steps:

  1. On the File menu, point to Get External Data, and then click Import.


  2. In the Import dialog box, select Text Files in the Files of type box.


  3. Find the text file you want to import, select it, and then click Import to start the Import Text Wizard (or Text Import Wizard in version 7.0).


  4. In the "Choose the format that best describes your data" dialog box, click Delimited, and then click Next.


  5. In the "What delimiter separates your fields?" dialog box, select a Delimiter and Text Qualifier depending on the format of the text file you are importing. If the first line in the text file contains field names, check the First Row Contains Field Names box. Click Next.


  6. In the "Where would you like to store your data? dialog box, click In a New Table, and then click Next.


  7. In the "You can specify information about each of the fields you are importing" dialog box, click the column heading of the column that contains the dates to select that column. In the Data Type box, select Text. Click Next.


  8. In the primary key dialog box, click an appropriate option: "Let Access add Primary Key," "Choose my own Primary Key," or "No Primary Key." Click Finish. The Wizard creates your table with a text field containing the dates in medium date format.


  9. Open the new table in Design view and change the following properties of the medium date field: 
    
      Data Type: Date/Time
      Format: Medium Date

    When you save the table and close it, the text values are converted to dates.


You can use the preceding steps to import a delimited text file and append it to an existing table. In step 6, click the option In an Existing Table instead of In a New Table, and make sure the existing table's date field is a Text data type. After you import the data, change the field's data type to Date/Time and set its Format property to Medium Date.


MORE INFORMATION

Steps to Reproduce Behavior


  1. Open any text editor, such as Notepad.


  2. Type the following four lines of delimited text: 

    
      DDMMMYY,MMDDYY
      01-Dec-95,12/1/95
      31-Jan-96,1/31/96
      15-Mar-94,3/15/94


  3. Save the file as TestDate.txt.


  4. Start Microsoft Access and open any database.


  5. On the File menu, point to Get External Data, and then click Import.


  6. In the Import dialog box, select Text Files in the Files of type box.


  7. Locate TestDate.txt, and then click Import to start the Import Text Wizard (or Text Import Wizard in version 7.0).


  8. In the "Choose the format that best describes your data" dialog box, click Delimited, and then click Finish. Note that you receive a message that some of the data (the dates in medium date format) were not imported successfully.



REFERENCES

For more information about this issue in Microsoft Access 2.0, please see the following article here in the Microsoft Knowledge Base:

Q131933 ACC2: Importing a Text File with Dates in DDMMYY Format

For more information about changing a field's data type, search the Help Index for "data types, converting," or ask the Microsoft Access 97 Office Assistant.

For more information about formatting dates, search the Help Index for "dates, formats," or ask the Microsoft Access 97 Office Assistant. 


Keywords          : kbinterop IsmTxtd 
Version           : 7.0 97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 19, 1999