Export table as multiple text files (CSV)

[psycoperl]

Good Evening,

I am working on a project that requires me to export data from our system into csv files of no more than 400 records in each file to be loaded into an external system.

I have a tempTable (temptblTemplate) where I have copied all the data that I need to export during the run of the job, this way I do not have to rerun the query and potentially get different results.

How can I do this easily?

Thanks

 

[Micron]

Easily? Maybe use the Transfertext function to output the results as a csv file which you can open in Excel. Then break your file into files with 400 records.
It can be done in code too, by looping through your table, getting 400 records and output as mentioned. However, you'd want to flag these records as 'Transported' (a Yes/No field that you probably don't have) and start the loop again, bypassing all that have been flagged, until you get another 400 and repeat as necessary. If it's a once or twice thing, I'd probably do the first. If it's going to be a repetitive exercise, I'd code it. Others may have a better idea, even if it still involves code.

 

[psycoperl]

Hi micron,

The manual method is what I am doing now, however we want to create an automated so that who ever is running the job we get the same results.

 

[Micron]

Assuming the table can have more than 400 rows each time, I guess the answer to your question is
- add the flag field to the table
- in code, start a counter at zero, create 2 recordsets (#1 is of the table, where flag=false)
- end process if there are no records for #1
- loop through #1 400 times or until end, whichever is first
- add max 400 records to #2 recordset and output via Transfer text
- update flag field for these records as true
- repeat code loop where counter starts at zero
You might want to consider if it's OK if a file has only a few records (even as few as 1), or add some logic to break x number of records into two files.
Not sure if all this meets your meaning of "easily".
P.S. after writing, I suppose it's doable with only 1 recordset if you just get TOP n on each pass using WHERE clause on the flag field.

 

[xenou]

Do you expect to have many records (over 100,000) when you do this?

 

[psycoperl]

Do you expect to have many records (over 100,000) when you do this?

No, Never. Max would be maybe 1500-2000 records to extract and export.

 

[xenou]

Was thinking that you could just write a script to manipulate the text file after it is created with all the records - that would be very simple. But went ahead and did it all with sql and a little vba in access. Used two temp tables because as far as i can see TransferText always exports an entire table.

a lot of assumptions here so it's just an example, lightly tested:

Sub foo()
Dim sql As String
Dim exportBatchNum As Long
Dim numRecordsToExport As Long
Dim numRecordsExported As Long
Dim i As Long

'--------------------------------------------------------------------------------------------------------------
'//Assumptions
' 1) There is a temp table that the records to export are in, and it has an autonumber field (called TempID)
'    The autonumber field is important for getting an accurate count - if this is changed, then you must have
'    a primary key in the query when you select TOP 400, because you do not want to have ties.  It also has
'    a field called ExportBatch to keep track of the batches.

' 2) There is a second temp table that is used by the TransferText function.  It is the same as the first,
'    but without the autonumber field or ExportBatch field (ie., just fields that actually need to be exported).

' 3) There is an export spec for the transfer text function (do an export to text on the table in Access using
'    the wizard and hit the "advanced" button to get to where you can give the spec a name and save it.)

' 4) Some kind of naming convention is used for the export files (so we can delete the old ones before starting)

' Execution steps:
' Assuming records are in 1st temp table already,
' * delete existing batch files (if any)
' * flag next 400 records in 1st Temp table
' * clear second temp table
' * insert next 400 records in 1st Temp table to 2nd Temp table
' * TransferText to move records to file from 2nd Temp table
' * Repeat for next 400 records as needed.
'--------------------------------------------------------------------------------------------------------------

'//Assuming records to be exported are in Table3 already and this is a temp table
numRecordsToExport = DCount("*", "Table3")

'//Delete existing files (don't want these getting confused with current batches)
CreateObject("Scripting.FileSystemObject").DeleteFile "C:\myTemp\ExportFile*.txt"

'//Just in case - be sure batches flag is null at start of routine.
sql = "UPDATE Table3 SET Table3.ExportBatch = Null"
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

i = 1
Do While (numRecordsExported < numRecordsToExport)
    
    DoCmd.SetWarnings False
     
    '//flag records for next batch in 1st Temp Table
    sql = "UPDATE Table3 SET ExportBatch = " & i
    sql = sql & " WHERE TempID IN (SELECT top 400 TempID FROM Table3 WHERE ExportBatch Is Null)"
    DoCmd.RunSQL sql
    
    '//Clear 2nd Temp Table
    sql = "DELETE Table4.* FROM Table4"
    DoCmd.RunSQL sql
    
    '//push records for next batch into 2nd Temp Table
    sql = "INSERT INTO Table4 (Field1, Field2)"
    sql = sql & " SELECT Field1, Field2 FROM Table3"
    sql = sql & " WHERE ExportBatch = " & i
    DoCmd.RunSQL sql
        
    '//get a count of records exported so far (for reporting to user and also for our loop condition)
    numRecordsExported = numRecordsExported + DCount("*", "Table4")
        
    '//Create text file(s)
    DoCmd.TransferText acExportDelim, "MySpec", "Table4", "C:\MyTemp\ExportFile" & i & ".txt", True
    
    i = i + 1
    
    DoCmd.SetWarnings True
    
Loop

MsgBox numRecordsToExport & " records exported in " & i - 1 & " batches."

End Sub

 

[xenou]

There's an error in my earlier code. In the msgbox line replace numRecordsToExport with numRecordsExported. Also the DeleteFile line needs to be wrapped in an error handler as it apparently errors if there is nothing to delete (as I did below).

For kicks, I did a second version using the approach of first exporting the whole table, then just breaking up the file into smaller files. It wouldn't be fast for very large files but is fine for smallish tables.

Sub bar()
Dim numRecordsExported As Long
Dim FSO As Scripting.FileSystemObject
Dim f(1) As Scripting.TextStream
Dim i As Long, j As Long
Dim baseFilePath As String
Dim firstLine As String


baseFilePath = "C:\myTemp\ExportFile.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

'//Delete existing files (don't want these getting confused with current batches)
On Error Resume Next
FSO.DeleteFile Replace(baseFilePath, ".txt", "*.txt")
On Error GoTo My_Exit

'//Create initial text file
DoCmd.SetWarnings False
DoCmd.TransferText acExportDelim, "MySpec2", "Table3", baseFilePath, True
DoCmd.SetWarnings True

'//Read first line (headers)
Set f(0) = FSO.OpenTextFile(baseFilePath, 1, False)
If Not f(0).AtEndOfStream Then
    firstLine = f(0).ReadLine
Else
    GoTo My_Exit
End If
    
'//Loop through data records
i = 0
Do While Not f(0).AtEndOfStream
    i = i + 1
    Set f(1) = FSO.CreateTextFile(Replace(baseFilePath, ".txt", i & ".txt"), True)
    f(1).WriteLine firstLine
    j = 0
    Do While j < 400
        If Not f(0).AtEndOfStream Then
            f(1).WriteLine f(0).ReadLine
            j = j + 1
            numRecordsExported = numRecordsExported + 1
        Else
            GoTo My_Exit
        End If
    Loop
Loop

My_Exit:
If numRecordsExported > 0 Then
    MsgBox numRecordsExported & " records exported in " & i & " files."
End If

On Error Resume Next
f(0).Close
f(1).Close
Set f(0) = Nothing
Set f(1) = Nothing
Set FSO = Nothing

Exit Sub

Err_Exit:
MsgBox Err.Description
GoTo My_Exit

End Sub