Cleaning up dates in text files

You can use the TransferText command in Access to import and export text files. A common example is writing out CSV files to be imported into another database system. It's quick, and you can build output file names dynamically, but there is one major drawback. If you export dates they are written out as a timestamp: dates with trailing zeros (representing the time portion of the date), and many systems won't import dates with that format. I came up against this problem recently, and had to find a way to automate the creation and cleanup process. With hundreds of files to process, any manual intervention wasn't going to be feasible.

• Download the sample database

The sample database shows the original code and one of several ways to automate the cleanup.

Using DoCmd.TransferText generates a file with timestamps in the date fields

If you click the top button on the form you will see a Notepad file like this:

The code that generates the file is shown below. There are two lines of interest. The first generates the file name and path, and the second writes the file.

Private Sub cmdFirst_Click()
''========================================================================
''Code for writing out a delimited text file.
''Demonstrates trailing zeros in date fields.
''Created by Denis Wright, 2 July 2009
''========================================================================
    Dim strFile As String
    
    'Write out a CSV file to the same directory as the database, date-stamped.
    'The field headers are written out with the file
    strFile = CurrentProject.Path & "\Orders_" & Format(Date, "yyyymmdd") & ".csv"
    DoCmd.TransferText acExportDelim, , "tblOrders", strFile, True
    
End Sub

Use the FileSystemObject's text features to clean up the file

The second button demonstrates the clean-up method. It adds a bit more processing to the original, summarised below:

• The first output file now has a .txt extension. It is still comma delimited but we intend to throw it away once the dates are cleaned up, so the .csv extension is used for the final output.
• The FileSystemObject has a number of useful features for working with text files.
  • OpenTextFile is used to open the text files. If the file already exists it is opened; if not, the file is created and then opened. When you open a file you can define whether you will be reading, writing or appending text. If you write, you will completely overwrite the original file; however, you may want to output data to a log file, in which case you would append instead.
  • ReadLine and WriteLine are used to process text files, one line at a time. With ReadLine you can transfer the contents of each line into a string variable; once processed, that string can then be written out to the other text file using WriteLine.
  • The process loops through all of the lines until you get to the end of the input file. The AtEndOfStream property is used to define the end of the file.

If you press the second button on the form you will get this output:

The code is shown below:

Private Sub cmdSecond_Click()
''========================================================================
''Code for writing out a delimited text file, then removing trailing zeros
'' from date fields.
''Uses the FileSystemObject
''Created by Denis Wright, 2 July 2009
''========================================================================
    Dim oFSO As Object
    Dim oFS_TXT, oFS_CSV
    Dim strFile As String
    Dim strOutFile As String
    Dim strText As String
    Const STR_REPLACE = " 0:00:00"
    Const ForReading = 1
    Const ForWriting = 2
    'If you want to continually add to a log file you can use:
    'Const ForAppending = 8
    
    'Write out a TXT file to the same directory as the database, date-stamped
    'The field headers are written out with the file
    strFile = CurrentProject.Path & "\Orders_" & Format(Date, "yyyymmdd") & ".txt"
    DoCmd.TransferText acExportDelim, , "tblOrders", strFile, True
    
    'Change the file string to use a CSV extension; this will be used for writing
    'the final file
    strOutFile = Replace(strFile, ".txt", ".csv")
    
    'Now, process that text file to remove the trailing zeros in the date format
    'We use the Scripting runtime to read the txt file into a variable, line by line
    'Processed lines are then written out to the final CSV file
    'When processing is complete, the original txt file is deleted
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFS_TXT = oFSO.OpenTextFile(strFile, ForReading)
    Set oFS_CSV = oFSO.OpenTextFile(strOutFile, ForWriting)
    
    Do Until oFS_TXT.AtEndOfStream
        strText = oFS_TXT.ReadLine
        strText = Replace(strText, STR_REPLACE, "")
        oFS_CSV.WriteLine (strText)
    Loop
    
    'clean up references and delete the text file
    oFS_TXT.Close
    oFS_CSV.Close
    Set oFSO = Nothing
    Set oFS_TXT = Nothing
    Set oFS_CSV = Nothing
    Kill strFile
    
End Sub

Mission accomplished; the new CSV file has the desired formatting, and imports into the corporate system. This database has been tested in Access 2003 and 2007.

Extras in the download

On the menu form are two checkboxes that let you view the code, or the CSV file output, once the routine has run.

  Home | About | Links | Contact | Excel | Access