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.
- OpenTextFile is used to open the text files. If 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.
