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.
The sample database shows the original code and one of several ways to automate the cleanup.
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
The second button demonstrates the clean-up method. It adds a bit more processing to the original, summarised below:
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.
On the menu form are two checkboxes that let you view the code, or the CSV file output, once the routine has run.