Export Oracle table (data) to 50000 records files (VBA conversion)

From: David M Higgs <david.higgs_at_aldasys.demon.co.uk>
Date: 13 Jan 2003 10:09:36 -0800
Message-ID: <d1e250c4.0301131009.22ac7635_at_posting.google.com>


Dear All

I have a table of employees (~320,000 records) that I need to export to text files in multiples of 50,000 records. (The reason: the destination system crashes when you try and load too much data in one go!!)
The routine below (VBA) works fine via Access and ODBC but I would love to have it as an SQL script.
Any takers? I've been trying for hours now and can't get it to work.

Thanks very much in advance for all help.

--
Regards,
David

Sub ExportTable()


'declare variables
Dim db As Database Dim rst As Recordset Dim sSQL As String Dim lngRecord As Long Dim iFileIndex As Integer Dim sFilename As String Set db = CurrentDb
'declare recordset
sSQL = "SELECT * FROM EMPLOYEES;" Set rst = db.OpenRecordset(sSQL) rst.MoveFirst iFileIndex = 1 On Error GoTo ExportSpodMart_Err While Not rst.EOF 'loop through all records sFilename = "I:\spod\spodmartdata\file-" & Format(iFileIndex, "00") & ".txt" Open sFilename For Output As #1 For lngRecord = 1 To 50000 Print #1, rst!FIRSTNAME & "|" & rst!LASTNAME & "|" & rst!TELEPHONE & "|" & rst!EMAIL rst.MoveNext Next lngRecord Close #1 'close file iFileIndex = iFileIndex + 1 'increment index Wend
'close recordset and release objects
rst.Close Set rst = Nothing Set db = Nothing Exit Sub ExportSpodMart_Err: 'crashed out - run out of records! Close #1
'close recordset and release objects
rst.Close Set rst = Nothing Set db = Nothing End Sub
Received on Mon Jan 13 2003 - 19:09:36 CET

Original text of this message