Export To Excel with more than 65,000 records

D

Dave McDonald

I want to export Access tables to Excel, however if there
are too many records, I want to parse the data into
multiple .CSV files for import into Excel.
Currently, I have to export the entire dataset into a CSV
file, then use Notepad to cut and Paste 50,000 records at
a time into other files.
File names should end with "_01.CSV"..."_09.CSV" etc.
depending on the number of files needed to capture all
the records.
Suggestions would be appreciated.
Dave
 
J

John Nurick

There are lots of ways of doing this. Here's one where the coding is
straightforward. It opens a recordset, and then counts the records as it
exports them. This is untested air code, but should give you the idea.


Sub ExportChunks(Source As String, _
FolderPath As String, BaseName As String)

Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim j As Long
Dim lngChunk As Long
Dim lngChunkCount As Long
Dim strTarget As String
Dim strLine As String

Const MAX_CHUNK = 65536
Const STD_CHUNK = 50000
Const DELIM = Chr(9) 'tab-delimited; change

' argument checking and error trapping omitted

'Get ready
Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset(Source, dbOpenForwardOnly)
rsR.MoveLast 'ensure we get full record count
If rsR.RecordCount = 0 Then
MsgBox "No records to export", vbOKOnly + vbInformation
Exit Sub
ElseIf rsR.RecordCount <= MAX_CHUNK Then
lngChunk = MAX_CHUNK
Else
lngChunk = STD_CHUNK
End If

rsR.MoveFirst
lngChunkCount = 0
Do Until rsR.EOF 'Outer loop: once per file
'Open output file
j = 0
lngChunkCount = lngChunkCount + 1
strTarget = FolderPath & "\" & BaseName _
& Format(lngChunkCount, "00") & ".txt"
lngFN = FreeFile()
Open strTarget For Output As #lngFN

Do Until (j = lngChunk) Or rsR.EOF
'inner loop: once per record
'assemble fields into string
strLine = ""
For Each fldF In rsR.Fields
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next
Print #lngFN, strLine
j = j + 1
Loop 'inner
Close #lngFN
Loop 'outer

rsR.Close
Set rsR = Nothing
Set dbD = Nothing
End Sub

This assumes tab-delimited output files. If you need CSV, change DELIM
to a comma and change the For...Next construct to something like this to
put quotes round the fields that need them because they contain a comma:

For Each fldF In rsR.Fields
If InStr(FldF.Value, ",") Then
strField = Chr(34) & fldF.Value & Chr(34)
Else
strField = fldF.Value
End If
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next

Again, there are more elegant ways of doing this.

Have fun!

Yes..VBA code is the way I was thinking. Here are my
general thoughts..the specifics I am not sure about!
1. For each table in the database selected via a path,
count the number of records. If less than 65K records,
then export to CSV file in selected path.
2. If more than 65k records, then Itters=Int
(RecCount/50000) equals the number of files that will be
created.
3. Loop through - for I = 1 to Itters
open filename&Itters&".csv"
Write# 50,000 records
close the file
loop

Users should be able to select the pathname of the
database to dump all the data from, and the path where the
files are to go.
it would be nice, though not necessary to have a progress
bar based on files / records being dumped and a summary at
the end (captured in a txt file) that tells the name of
the table, and all associated .csv files that table got
dumped into.

MY MAIN PROBLEM IS: How to select a recordset, write /
output to a file, close the recordset, etc.

Thanks for any help you can provide.
Dave McDonald
AIM=DaveMcTKD
(e-mail address removed)
-----Original Message-----
Hi Dave,

If you don't mind a little manual intervention and don't want to get
into writing VBA code, the simplest approach is probably to download
UnxUtils.zip from http://unxutils.sourceforge.net/ and use the "split"
utility to carve your big .csv file into handy numbered chunks.

You can pass a command to "split" from within Access VBA using either
the Shell() statement or the "Shell-and-wait" code at
http://www.mvps.org/access/api/api0004.htm

Otherwise, you need to write VBA code that decides how to split up the
data and exports each chunk with an appropriate name. Post back here if
you decide to go that way and need help.



I want to export Access tables to Excel, however if there
are too many records, I want to parse the data into
multiple .CSV files for import into Excel.
Currently, I have to export the entire dataset into a CSV
file, then use Notepad to cut and Paste 50,000 records at
a time into other files.
File names should end with "_01.CSV"..."_09.CSV" etc.
depending on the number of files needed to capture all
the records.
Suggestions would be appreciated.
Dave

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
D

David McDonald

FANTASTIC!
THANK YOU !!!!!!!

-----Original Message-----
There are lots of ways of doing this. Here's one where the coding is
straightforward. It opens a recordset, and then counts the records as it
exports them. This is untested air code, but should give you the idea.


Sub ExportChunks(Source As String, _
FolderPath As String, BaseName As String)

Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim j As Long
Dim lngChunk As Long
Dim lngChunkCount As Long
Dim strTarget As String
Dim strLine As String

Const MAX_CHUNK = 65536
Const STD_CHUNK = 50000
Const DELIM = Chr(9) 'tab-delimited; change

' argument checking and error trapping omitted

'Get ready
Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset(Source, dbOpenForwardOnly)
rsR.MoveLast 'ensure we get full record count
If rsR.RecordCount = 0 Then
MsgBox "No records to export", vbOKOnly + vbInformation
Exit Sub
ElseIf rsR.RecordCount <= MAX_CHUNK Then
lngChunk = MAX_CHUNK
Else
lngChunk = STD_CHUNK
End If

rsR.MoveFirst
lngChunkCount = 0
Do Until rsR.EOF 'Outer loop: once per file
'Open output file
j = 0
lngChunkCount = lngChunkCount + 1
strTarget = FolderPath & "\" & BaseName _
& Format(lngChunkCount, "00") & ".txt"
lngFN = FreeFile()
Open strTarget For Output As #lngFN

Do Until (j = lngChunk) Or rsR.EOF
'inner loop: once per record
'assemble fields into string
strLine = ""
For Each fldF In rsR.Fields
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next
Print #lngFN, strLine
j = j + 1
Loop 'inner
Close #lngFN
Loop 'outer

rsR.Close
Set rsR = Nothing
Set dbD = Nothing
End Sub

This assumes tab-delimited output files. If you need CSV, change DELIM
to a comma and change the For...Next construct to something like this to
put quotes round the fields that need them because they contain a comma:

For Each fldF In rsR.Fields
If InStr(FldF.Value, ",") Then
strField = Chr(34) & fldF.Value & Chr(34)
Else
strField = fldF.Value
End If
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next

Again, there are more elegant ways of doing this.

Have fun!

Yes..VBA code is the way I was thinking. Here are my
general thoughts..the specifics I am not sure about!
1. For each table in the database selected via a path,
count the number of records. If less than 65K records,
then export to CSV file in selected path.
2. If more than 65k records, then Itters=Int
(RecCount/50000) equals the number of files that will be
created.
3. Loop through - for I = 1 to Itters
open filename&Itters&".csv"
Write# 50,000 records
close the file
loop

Users should be able to select the pathname of the
database to dump all the data from, and the path where the
files are to go.
it would be nice, though not necessary to have a progress
bar based on files / records being dumped and a summary at
the end (captured in a txt file) that tells the name of
the table, and all associated .csv files that table got
dumped into.

MY MAIN PROBLEM IS: How to select a recordset, write /
output to a file, close the recordset, etc.

Thanks for any help you can provide.
Dave McDonald
AIM=DaveMcTKD
(e-mail address removed)
-----Original Message-----
Hi Dave,

If you don't mind a little manual intervention and
don't
want to get
into writing VBA code, the simplest approach is
probably
to download
UnxUtils.zip from http://unxutils.sourceforge.net/ and use the "split"
utility to carve your big .csv file into handy
numbered
chunks.
You can pass a command to "split" from within Access
VBA
using either
the Shell() statement or the "Shell-and-wait" code at
http://www.mvps.org/access/api/api0004.htm

Otherwise, you need to write VBA code that decides how
to
split up the
data and exports each chunk with an appropriate name. Post back here if
you decide to go that way and need help.



On Mon, 21 Jul 2003 13:39:32 -0700, "Dave McDonald"

I want to export Access tables to Excel, however if there
are too many records, I want to parse the data into
multiple .CSV files for import into Excel.
Currently, I have to export the entire dataset into a CSV
file, then use Notepad to cut and Paste 50,000
records
at
a time into other files.
File names should end with "_01.CSV"..."_09.CSV" etc.
depending on the number of files needed to capture all
the records.
Suggestions would be appreciated.
Dave

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
 
J

Joe Fallon

Who needs elegant?
Nice job.

Above and beyond the call of duty I would say!
--
Joe Fallon
Access MVP



John Nurick said:
There are lots of ways of doing this. Here's one where the coding is
straightforward. It opens a recordset, and then counts the records as it
exports them. This is untested air code, but should give you the idea.


Sub ExportChunks(Source As String, _
FolderPath As String, BaseName As String)

Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim j As Long
Dim lngChunk As Long
Dim lngChunkCount As Long
Dim strTarget As String
Dim strLine As String

Const MAX_CHUNK = 65536
Const STD_CHUNK = 50000
Const DELIM = Chr(9) 'tab-delimited; change

' argument checking and error trapping omitted

'Get ready
Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset(Source, dbOpenForwardOnly)
rsR.MoveLast 'ensure we get full record count
If rsR.RecordCount = 0 Then
MsgBox "No records to export", vbOKOnly + vbInformation
Exit Sub
ElseIf rsR.RecordCount <= MAX_CHUNK Then
lngChunk = MAX_CHUNK
Else
lngChunk = STD_CHUNK
End If

rsR.MoveFirst
lngChunkCount = 0
Do Until rsR.EOF 'Outer loop: once per file
'Open output file
j = 0
lngChunkCount = lngChunkCount + 1
strTarget = FolderPath & "\" & BaseName _
& Format(lngChunkCount, "00") & ".txt"
lngFN = FreeFile()
Open strTarget For Output As #lngFN

Do Until (j = lngChunk) Or rsR.EOF
'inner loop: once per record
'assemble fields into string
strLine = ""
For Each fldF In rsR.Fields
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next
Print #lngFN, strLine
j = j + 1
Loop 'inner
Close #lngFN
Loop 'outer

rsR.Close
Set rsR = Nothing
Set dbD = Nothing
End Sub

This assumes tab-delimited output files. If you need CSV, change DELIM
to a comma and change the For...Next construct to something like this to
put quotes round the fields that need them because they contain a comma:

For Each fldF In rsR.Fields
If InStr(FldF.Value, ",") Then
strField = Chr(34) & fldF.Value & Chr(34)
Else
strField = fldF.Value
End If
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next

Again, there are more elegant ways of doing this.

Have fun!

Yes..VBA code is the way I was thinking. Here are my
general thoughts..the specifics I am not sure about!
1. For each table in the database selected via a path,
count the number of records. If less than 65K records,
then export to CSV file in selected path.
2. If more than 65k records, then Itters=Int
(RecCount/50000) equals the number of files that will be
created.
3. Loop through - for I = 1 to Itters
open filename&Itters&".csv"
Write# 50,000 records
close the file
loop

Users should be able to select the pathname of the
database to dump all the data from, and the path where the
files are to go.
it would be nice, though not necessary to have a progress
bar based on files / records being dumped and a summary at
the end (captured in a txt file) that tells the name of
the table, and all associated .csv files that table got
dumped into.

MY MAIN PROBLEM IS: How to select a recordset, write /
output to a file, close the recordset, etc.

Thanks for any help you can provide.
Dave McDonald
AIM=DaveMcTKD
(e-mail address removed)
-----Original Message-----
Hi Dave,

If you don't mind a little manual intervention and don't want to get
into writing VBA code, the simplest approach is probably to download
UnxUtils.zip from http://unxutils.sourceforge.net/ and use the "split"
utility to carve your big .csv file into handy numbered chunks.

You can pass a command to "split" from within Access VBA using either
the Shell() statement or the "Shell-and-wait" code at
http://www.mvps.org/access/api/api0004.htm

Otherwise, you need to write VBA code that decides how to split up the
data and exports each chunk with an appropriate name. Post back here if
you decide to go that way and need help.



On Mon, 21 Jul 2003 13:39:32 -0700, "Dave McDonald"

I want to export Access tables to Excel, however if there
are too many records, I want to parse the data into
multiple .CSV files for import into Excel.
Currently, I have to export the entire dataset into a CSV
file, then use Notepad to cut and Paste 50,000 records at
a time into other files.
File names should end with "_01.CSV"..."_09.CSV" etc.
depending on the number of files needed to capture all
the records.
Suggestions would be appreciated.
Dave

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top