Write a seperate CSV file from each record in a table

[schneie1974]

Hello,

I did some basic VBA programming some 10 years back and do now need to solve just 1 problem.

I have the requirement to write EACH record from a table to its own CSV file with name of the file beeing combination of 2 fields

So let's say I have Table1 with 3 columns (Field1, Field2, Field3) with following content :

Field1 Field2 Field3
AA 1 ABC
AA 2 DEF
AA 3 GHI

I should get 3 files with names AA1.CSV, AA2.CSV and AA3.CSV and each file contains its respective row from the table.

I tried to do it with DAO Recordset, but I do not find a way to write only the current record from recordset while looping.

See below the code I was using, but issue is that code does succesfull creates the 3 CSV files as per above example, but in each file it writes ALL 3 ROWS instead only the respective ROW.

Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strFilename As String
    
    strSQL = "SELECT Field1, Field2 FROM Table1"
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset(strSQL)

    If Not ((rs.EOF) And (rs.BOF)) Then
        rs.MoveFirst
        strSQL = ""
        Do While Not (rs.EOF)
            strSQL = "SELECT * from Table1"
            strFilename = "c:\temp\" & rs!Field1 & "_" & rs!Field2 & ".csv"
            DoCmd.TransferText acExportFixed, "EXPORT2", Table1, strFilename
            rs.MoveNext
        Loop
    End If
    rs.Close
    Set rs = Nothing
    MsgBox "Exporting Done.." & vbCrLf & "Dir..  " & Left(strFilename, 13)

Hope you can help me here, maybe I need to approach totally different.

 

[Ranman256]

Correction...
DONT export TABLE,
export a query that only has 1 record,
(hopefully you have index ID to do this)

 

[Uncle Gizmo]

You have this line of code:- strSQL = "SELECT * from Table1"

That appears to reset the strSQL...

But I can't see the reason for, or where that is used in the following code. Could you please explain.

 

[plog]

To expland on Ran's advice:

DoCmd.TransferText acExportFixed, "EXPORT2", Table1, strFilename

That's the export line you are using. And what are you exporting? Table1. You need to change that line of code to export just what you want, not the whole table as you have told it to.

 

[schneie1974]

@plog : Yes I tried to change the DoCMD.TransferText command already in order to replace Table1 by anything else...and this is exactly my problem.

It seems the DoCMD.TransferText command requires a table or query and can not have any RecordSet or similar in it ?

Do you have any suggestion how this command should look like, or if it does not work with DoCmd.TransferText what else to be used ?

 

[plog]

Wow, I've been googling for 10 minutes and little luck. Here's what I found:

If you want to use DoCmd.TransferText you need to create a temporary query object and export that. So basically, you do as Ran suggested, compose an SQL string that limits your table to just the current record, save that as a temporary query and then use DoCmd.TransferText on that temporary query.

Otherwise, you need to use an ADO Recordset and write to a generic file.

 

[schneie1974]

I was also googling for hours already , and still after your hint with temporary table I am still not able to find the proper solution.

My Table1 has an ID, but while looping in the recordset, how can I select only 1 record to a temporary table which I can then use in the DoCmd.TransferText command ?

 

[plog]

Let's say your ID field in Table 1 is Field4. In your initial SQL string (the one you set the Record Set from), you add in Field4. Then within your loop you set your SQL to pull from Table1 only those records where Field4 equals the current value of Field4 in the recordset. Then you make that query a temproary query object and export it.

 

[schneie1974]

Hi, I tried to figure out myself how to read the current value of the field and how to store in temporary table or query within the recordset, but I failed.

Would somebody so kind to propose some coding which should work ?

Many thanks in advance

 

[vbaInet]

1. Create a query and include only this part of the SQL:

SELECT * FROM Table1

2. Use something like this:

    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim strSQL      As String
    Dim strFilename As String
    Dim intCount    As Long
    
    Const STR_QDF = "[COLOR="Blue"]Query Name[/COLOR]"
    
    Set db = CurrentDb
    strSQL = db.QueryDefs(STR_QDF).SQL & " "
    
    With rs
        Do While Not .EOF
            ' Change the SQL of the query
            db.QueryDefs(STR_QDF).SQL = strSQL & "WHERE IdField = " & !IdField
            strFilename = "c:\temp\" & !Field1 & "_" & !Field2 & ".csv"
            
            ' Export it
            DoCmd.TransferText acExportFixed, "EXPORT2", STR_QDF, strFilename
            DoEvents
            
            .MoveNext
            intCount = intCount + 1
        Loop
        
        .Close
    End With
    
    ' Reset the SQL and clean up
    db.QueryDefs(STR_QDF).SQL = strSQL
    Set rs = Nothing
    
    MsgBox intCount & " file(s) where exported!", _
           vbInformation + vbOKOnly, _
           "Export complete"
    
'    ' // Reset the SQL of the querydef in your error handler
'    db.QueryDefs(STR_QDF).SQL = strSQL
'    Set rs = Nothing
'    Set db = Nothing

 

[schneie1974]

Great this is working like I expected it to work, many thanks to Plog and vbaInet !!