Exporting chuncks

[Scott]

I have a table of 36000+ parts. I need to export this in CSV files in
chuncks of 4000 records per file. I would like automate this some how to
dump record 1-4000 w/ headers to part1.csv. than 4001-8000 w/headers to
part2.csv .....etc.

I have added an auto number field to my part table so I can select my ranges
but I would like get the dump automated some how.

 

[Ken Sheridan]

Try something like this:

Const DESTINATIONFOLDER = _
"F:\SomeFolder\SomeSubFolder\"

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim strSQL As String
Dim n As Long, i As Integer

Set dbs = CurrentDb

For n = 1 To 40000 Step 4000

i = i + 1

strSQL = "SELECT * " & _
"FROM YourTable AS T1 " & _
"WHERE(SELECT COUNT(*) " & _
"FROM YourTable As T2 " & _
"WHERE T2.YourKey <= T1.YourKey) " & _
"BETWEEN " & n & " AND " & n + 3999

Set qdf = dbs.CreateQueryDef("qryTemp", strSQL)

DoCmd.TransferText acExportDelim, , "qryTemp", _
DESTINATIONFOLDER & "part" & i & ".txt", True

dbs.QueryDefs.Delete "qryTemp"

Next n

Determine the ranges by means of a subquery as above, not by the actual
values of the autonumber column (YourKey in the above example). An
autonumber only guarantees unique values not an unbroken sequence. While
you've may well have got the latter its not a good idea in principle to
assume a continuous sequence. With a subquery its irrelevant if the sequence
is broken.

Ken Sheridan
Stafford, England

 

[Scott]

Ken,
Thanks for getting back to me. I built a form and added this code to a
button. I took out the add temp Query and delete temp query as I have a
query built that I want to use and keep [SC - PARTMASTER Export].

If I run this code I get the entire table output to every file> file1.txt,
file 2.txt...... it keeps loopeing and making files until I break the loop.
I am pretty sure that it woudl run until the loop reached 40,000.

It seems to work this way except for the Query record count. I know you
said the table autonumber is not a good idea to use and I agree but how do I
work around it? Also, what do you thinkg is wrong with my loop?


Private Sub CMD_SCParts_Click()

Const DESTINATIONFOLDER = "C:\Data Conversion\Output\SCPartIn\"

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim strSQL As String
Dim n As Long, i As Integer

Set dbs = CurrentDb

For n = 1 To 40000 Step 4000

i = i + 1

strSQL = "SELECT * " & _
"FROM (SC - PARTMASTER Export) AS T1 " & _
"WHERE(SELECT COUNT(*) " & _
"FROM (SC - PARTMASTER Export) As T2 " & _
"WHERE T2.AutoNumber <= T1.AutoNumber) " & _
"BETWEEN " & n & " AND " & n + 3999

DoCmd.TransferText acExportDelim, , "SC - PARTMASTER Export",
DESTINATIONFOLDER & "part" & i & ".csv", True

Next

 

[Ken Sheridan]

You must include the code to create, and subsequently delete the temporary
query, qryTemp, which will return 4000 rows from the original query's result
set at each iteration of the loop. As you've written it the SQL statement is
doing nothing as you are simply exporting the complete result set of the
original query each time.

Also I notice that you've used parentheses round the table name, (SC -
PARTMASTER Export), rather than square brackets, [SC - PARTMASTER Export].
So it should be:

Private Sub CMD_SCParts_Click()

Const DESTINATIONFOLDER = "C:\Data Conversion\Output\SCPartIn\"

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim strSQL As String
Dim n As Long, i As Integer

Set dbs = CurrentDb

For n = 1 To 40000 Step 4000

i = i + 1

strSQL = "SELECT * " & _
"FROM [SC - PARTMASTER Export] AS T1 " & _
"WHERE(SELECT COUNT(*) " & _
"FROM [SC - PARTMASTER Export] As T2 " & _
"WHERE T2.AutoNumber <= T1.AutoNumber) " & _
"BETWEEN " & n & " AND " & n + 3999

Set qdf = dbs.CreateQueryDef("qryTemp", strSQL)

DoCmd.TransferText acExportDelim, , "qryTemp", _
DESTINATIONFOLDER & "part" & i & ".txt", True

dbs.QueryDefs.Delete "qryTemp"

Next n

End Sub

Ken Sheridan
Stafford, England

 

[Scott]

Ken,

Thank for the help. After much testing the code works great. The problem
is when I have my table full of 39,000+ records it takes over 10 min to make
one file of 4000 records. I droped it down to 100 records from the source
table and droped the count/step to 20 records per file. This ran 5 files of
20 records in 15 seconds.

Is there any other way to filter "X" # of records other than useing the my
auto number field? I just need to get approx 2000 to 4000 records per file?

 

[John W. Vinson]

Ken,

Thank for the help. After much testing the code works great. The problem
is when I have my table full of 39,000+ records it takes over 10 min to make
one file of 4000 records. I droped it down to 100 records from the source
table and droped the count/step to 20 records per file. This ran 5 files of
20 records in 15 seconds.

Is there any other way to filter "X" # of records other than useing the my
auto number field? I just need to get approx 2000 to 4000 records per file?

If it's only an *approximate* value that you need, just use a VBA loop

Dim ID As Integer
Dim strSQL As String
For Id = 0 to 40000 STEP 2000
strSQL = "SELECT * FROM tablename WHERE ID >= " & ID _
& " AND ID < " & Id + 2000

and use this SQL as the basis for your export. If you have a fresh-filled,
indexed autonumber field it'll work just fine.

 

[Ken Sheridan]

I'm surprised at the poor performance. I've only been able to test it
against a table or around 6500 rows, but with that its creating 10 files in
around 20 seconds. This is using a Windows Vista system with an Intel Core2
Duo 2.33.Ghz processor. The files are being created in a folder on the local
drive

As the values of an autonumber column are not reliably sequential it would
not be prudent to rely on the actual values of this column to partition the
table as you could well get either gaps or overlaps between the sets.

One alternative which springs to mind would be to assign sequential numbers
to a 'counter' column of long integer data type by iterating through a
recordset of the complete table and updating the column incrementally. You
could then loop through the table in the same way as with my code , but
instead of using a subquery when creating qryTemp each time, restrict the
rows to where the 'counter' column is between 1 and 4000, 4001 and 8000 etc.,
so the code to build the query would be:

strSQL = "SELECT * " & _
"FROM [SC - PARTMASTER Export] " & _
"WHERE [Counter] & _
"BETWEEN " & n & " AND " & n + 3999

Another would be to write the data to each file line by line following an
Open <path> For Ouput statement, starting a new file after every 4,000 rows.

Ken Sheridan
Stafford, England

 

[Scott]

The following code works well for what I needed. The only issue I ran into
was the autonumber I put, now exports to my output file which spoils the
format. I was looking around trying to fix this and found some post
regarding a row number or SELECT (SELECT Count(*) FROM TableName.

If add this to my SQLstr, can I use it to count off of? Does it build a row
number into the data set?

Again, I really apreciate the help. This is helping my automation process
for this DB project incrediably.



Private Sub Command12_Click()

Const DESTINATIONFOLDER = "C:\Data Conversion\Output\SCPartIn\"

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim strSQL As String
Dim n As Long, i As Integer

Set dbs = CurrentDb

For n = 1 To 40000 Step 2000

i = i + 1

strSQL = "SELECT * FROM [SC - PARTMASTER] WHERE [AutoNumber]>= " & n
& " AND [AutoNumber] < " & n + 2000

Set qdf = dbs.CreateQueryDef("qryTemp", strSQL)

DoCmd.TransferText acExportDelim, , "qryTemp", _
DESTINATIONFOLDER & "part" & i & ".csv", True

dbs.QueryDefs.Delete "qryTemp"

Next n

End Sub

 

[John W. Vinson]

The following code works well for what I needed. The only issue I ran into
was the autonumber I put, now exports to my output file which spoils the
format.

Well, then just don't export it!

Rather than using

strSQL = "SELECT * FROM [SC - PARTMASTER] WHERE [AutoNumber]>= " & n
& " AND [AutoNumber] < " & n + 2000


replace the * with an explicit list of all the fields other than the
autonumber.

 

[Scott]

LOL,

I knew that but some reason it did not click.

Thanks.

The following code works well for what I needed. The only issue I ran into
was the autonumber I put, now exports to my output file which spoils the
format.

Well, then just don't export it!

Rather than using

strSQL = "SELECT * FROM [SC - PARTMASTER] WHERE [AutoNumber]>= " & n
& " AND [AutoNumber] < " & n + 2000


replace the * with an explicit list of all the fields other than the
autonumber.