Create csv file for every 1000 rows of data

[CharlesWhiteman]

I'm creating a utility which looks at a windows folder structure and contained documents which will be used to import the documents into a document management system using a csv file to pick up references and the respective location of each document.

All of that I can successfully do.

What I'm wondering about is that I want to limit the number of imports preferring to split into multiple batches.

I currently have a command button which triggers the creation of a csv file based on an export specification attached to a query.

What would be the best way to get the app to create a xsv file for say every 1000 rows of data?

Any pointed much appriciated.

 

[vbaInet]

I haven't tested this much but here's something:

Public Function MakeCSVInterval()
    Dim iMax As Long, i As Long, iInterval As Long, qdf As DAO.QueryDef
    
    iMax = DCount("*", "[COLOR=Navy]TableName[/COLOR]")
    iInterval = [COLOR=Red][B]1000[/B][/COLOR]

    For i = 1 To iMax
        Set qdf = CurrentDb.QueryDefs("[COLOR=Navy]QueryCopy[/COLOR]")
        
        If (i + iInterval) >= iMax Then
            iMax = iMax + iInterval
        End If

        qdf.SQL = "SELECT [COLOR=Navy]TableName[/COLOR].* " & _
                  "FROM [COLOR=Navy]TableName[/COLOR] " & _
                  "WHERE Replace(Partition([[COLOR=Navy]ID_Field[/COLOR]], 1, " & iMax & ", " & iInterval & "), ' ', '') = '" & i & ":" & (i + iInterval - 1) & "';"
        
        Set qdf = Nothing
        
        DoCmd.TransferText acExportDelim, , "[COLOR=Navy]QueryCopy[/COLOR]", "[COLOR=Navy]C:\Documents and Settings\charles\MyImports\T[/COLOR]" & i & ".csv"

        i = i + iInterval - 1
        
        DoEvents
    Next
End Function

For this to work you must create a query that will be amended everytime it goes through the loop. A blank query set as SELECT; will do. Substitute "QueryCopy" for the name of the query you just created. Everything else highlighted needs to substituted for the appropriate values too.

 

[CharlesWhiteman]

Hi, and thanks for the code sample which I've tweeked and almost works. The only issue at the moment is that Access says that it cant find "Test1.csv" - I know the file is there as I created it after it said it couldnt find it the first time.

Its a runtime error 3011 "Access couldnt find the object"

Here's the code:

Dim iMax As Long, i As Long, iInterval As Long, qdf As DAO.QueryDef
    
    iMax = DCount("*", "Files")
    iInterval = 1000
    For i = 1 To iMax
        Set qdf = CurrentDb.QueryDefs("QueryCopy")
        
        If (i + iInterval) >= iMax Then
            iMax = iMax + iInterval
        End If
        qdf.SQL = "SELECT Files.* " & _
                  "FROM Files " & _
                  "WHERE Replace(Partition([FileID], 1, " & iMax & ", " & iInterval & "), ' ', '') = '" & i & ":" & (i + iInterval - 1) & "';"
        
        Set qdf = Nothing
        DoCmd.TransferText acExportDelim, "SingleViewExportSpecification", "QueryCopy", "C:\Demo's\SingleView Link Manager\Test" & i & ".csv"

        i = i + iInterval - 1
        
        DoEvents
    Next

 

[vbaInet]

Two things:

1. Test it without the Spec for now
2. Don't create Test1 file.

 

[CharlesWhiteman]

Thanks, I did get it working and then it stopped working!

Now have got it working again and it works as expected by the code.

Thanks soo much for your kind assistance. I will have to figure it out and understand it better but this is a great start.

 

[vbaInet]

You're welcome.

Just to mention that the records returned will be affected if whilst you're running this routine a user adds or deletes a record.

 

[CharlesWhiteman]

My app basically looks at a windows folder, containing directories and files, it then creates a list of all files into TblFiles with fields FileID (AutoNumber), FName, FPath

I then use a qury/module to create a query which essentiall gives me a table but with the file path split into values taken from parsing the path and creating a column at each slash mark:

Example:

Raw List Data

1 041202tsDCIR.doc C:\Test Top Level\TestDataToImport\AB2222\preSingleview\

Becomes

AB2222 preSingleview   C:\Test Top Level\TestDataToImport\AB2222\preSingleview\041202tsDCIR.doc

Its this processed data which I need to use as I use the folder names to uniquely reference each document.

Also, it needs to be a csv file. The files produced by this code creates an Excel csv file - I would need to test to see if I can use this.

I'm guessing that the export spec has something to do with that.

 

[vbaInet]

What do you mean by an Excel csv? Does it do this Filename.csv.xls? I would doubt that. It is a csv file but the default program that opens it is Excel. If you open it in Notepad you will see it as comma separated.

 

[CharlesWhiteman]

you are quite right in that yes it is a comma delimited file but what I get is:

1323,"141102tsDCIR.doc","C:\Test Top Level\TestDataToImport\AB2222\preSingleview\","C:","Test Top Level","TestDataToImport","AB2222","preSingleview",,,"C:\Test Top Level\TestDataToImport\AB2222\preSingleview\141102tsDCIR.doc"

Whereas I need to output it without the "" marks.

Do you think its possible to use an import specification with the routine?

 

[vbaInet]

Do you think its possible to use an import specification with the routine?

That's right, it is. During the Export spec, there's an Advanced option I think is called Text Qualifier. You need to change that to No or None.

 

[CharlesWhiteman]

It sure does! But it looks as though (I havent fully tested) but it looks as though one has to create the export spec against the query (in access 2010).

Now I will have develop from scratch to fully understand the process but this looks very good. you are right about other users potentially being able to change data by having access. In this case its just being used as a stand-alone single use runtime utility.

 

[vbaInet]

It sure does! But it looks as though (I havent fully tested) but it looks as though one has to create the export spec against the query (in access 2010).

Yup! Create the Export Spec based on that query, without the Replace(Partition(...)) field. Then you should be able to use that spec in the code.

Now I will have develop from scratch to fully understand the process but this looks very good. you are right about other users potentially being able to change data by having access. In this case its just being used as a stand-alone single use runtime utility.

You can always run this routine based on a copy of the table but copying the table alone will be a tedious task so it's not really worth it.