export to text but only certain number of records

S

secondangel

Registered User.
Local time
Today, 23:47
Joined
Nov 6, 2008
Messages
52
Hiya

Is it possible to set the transfer text so it only exports say 10,000 lines in the table to a file and then does the next 10,000 to a diff file.

im presuming it will involve looping through a recordset and using a 'temp table' and a integer flag counter. each record read and copied increments the counter...............

however if someone knows a set fucntion that exists id be gratfeul.

i know its just a text file but its very long and its used as an uploader info file and becuase its so large it always timesout the server. i have tried with smaller files and it works so im figuring best option is to chop up my file into several that can be uploaded seperately

Thanks
 
You could write a function to do it in one fail swoop. It would work like this

Step 1: Create a recordset based on the underlying query you currently have
Step 2: Count the number of records in the underlying query
Step 3: Open a a text file for output as #n
Step 4: Step through the recordset 1 row at a time writing the record to the text file, adding 1 to a counter after each write. then use the .MoveNext command to move to the next record in the record set

Step 5: contine step 4 until counter reaches 10,000 upon which you close the text file (Close #n)

Step 6:Reset the counter back to 1
Step 7:repeat steps 3 to 6

Where is the number of the next freefile

This should then give you a full set of data split into n files depending on the original recoud count.


David
 
ok cool, seems as i thought.

im just unsure on opening and writing to the text file then closign the text file. i normally just use docmd.transfertext to dump the whole table/query
 
Are you going to be doing this on a regular basis or is it a one off routine.
 
Here is a solution:

Code: 
Public Function SplitTableOrQuery(TableOrQueryName As String, MaxRows As Long)
Dim Rs As DAO.Recordset

Dim fileNum As Long
Dim TxtStr As String
Dim nIndex As Long
Dim StrPath As String
Dim nFile As Long

StrPath = CurrentProject.Path

Set Rs = CurrentDb.OpenRecordset(TableOrQueryName)

If Not Rs.EOF And Not Rs.BOF Then
    'Calculate the number of text files required to complete task
    Rs.MoveLast
    
    nFile = Int(Rs.RecordCount / MaxRows) + 1
    fileNum = 1
    Rs.MoveFirst
    
    'Open the first instance of the text file
    Open StrPath & "\" & TableOrQueryName & "_" & CStr(fileNum) & ".txt" For Output As #fileNum
    
   Do Until Rs.EOF
    
    For n = 0 To Rs.Fields.Count - 1
    
        TxtStr = TxtStr & CStr(Nz(Rs(n), "")) & ","
        
    Next
    'Drop the last comma
    TxtStr = Left(TxtStr, Len(TxtStr) - 2)
    
    Print #fileNum, TxtStr
    TxtStr = ""
    
    nIndex = nIndex + 1
    Rs.MoveNext
    If nIndex > MaxRows Then
        nIndex = 0
        Close #fileNum
        fileNum = fileNum + 1
        Open StrPath & "\" & TableOrQueryName & "_" & CStr(fileNum) & ".txt" For Output As #fileNum
    End If
   Loop
   Rs.Close
End If
Set Rs = Nothing
Close #fileNum

 
End Function

How it works

Call the function form a command button as such

Call SplitTableOrQuery("YourTableOrQueryName",10000)

This will create n number of files named YourTableOrQueryName_n where n is the file sequence number.

the files will be stored in the current project path.

Give it a whizz

David
 
works like a dream - you sir are very clever and im very grateful.

all i had to do was change teh value from 2 to 1 in the drop last comma box as it chopped the last character off, but now its perfect

thanks a lot muchly
 
Glad to be of help.

What it does not do is check for the existance of a file before creating the file. You may need to use a dir() command to test for its existance and kill it it it already exists. What was created today will still be there tomorrow.

Other than that you could automatically copy it to another destination (on the server) then kill it there and then.

If you go down that route make sure the file has copied successfully before you kill the local copy.


David
 
DCrake said:
Here is a solution:

Code: 
Public Function SplitTableOrQuery(TableOrQueryName As String, MaxRows As Long)
Dim Rs As DAO.Recordset

Dim fileNum As Long
Dim TxtStr As String
Dim nIndex As Long
Dim StrPath As String
Dim nFile As Long

StrPath = CurrentProject.Path

Set Rs = CurrentDb.OpenRecordset(TableOrQueryName)

If Not Rs.EOF And Not Rs.BOF Then
    'Calculate the number of text files required to complete task
    Rs.MoveLast
    
    nFile = Int(Rs.RecordCount / MaxRows) + 1
    fileNum = 1
    Rs.MoveFirst
    
    'Open the first instance of the text file
    Open StrPath & "\" & TableOrQueryName & "_" & CStr(fileNum) & ".txt" For Output As #fileNum
    
   Do Until Rs.EOF
    
    For n = 0 To Rs.Fields.Count - 1
    
        TxtStr = TxtStr & CStr(Nz(Rs(n), "")) & ","
        
    Next
    'Drop the last comma
    TxtStr = Left(TxtStr, Len(TxtStr) - 2)
    
    Print #fileNum, TxtStr
    TxtStr = ""
    
    nIndex = nIndex + 1
    Rs.MoveNext
    If nIndex > MaxRows Then
        nIndex = 0
        Close #fileNum
        fileNum = fileNum + 1
        Open StrPath & "\" & TableOrQueryName & "_" & CStr(fileNum) & ".txt" For Output As #fileNum
    End If
   Loop
   Rs.Close
End If
Set Rs = Nothing
Close #fileNum

 
End Function
How it works

Call the function form a command button as such

Call SplitTableOrQuery("YourTableOrQueryName",10000)

This will create n number of files named YourTableOrQueryName_n where n is the file sequence number.

the files will be stored in the current project path.

Give it a whizz

David
Click to expand...

Hi.

I had a similar need. Well actually my problem is I have a recordset with more than 750K records which I want to export into Excel to send to a customer. Excel 2007 can take that but for some reason when I try to export from Access it says that the clipboard cannot take more than 65K records so I could manually break down the recordset but that would be a pain and I tried using your code. I'm not a programmer (well I was 25 years ago before the days of Windows - give me assembly code on a 6502 any day) and when I put your code into a module and then run it from a macro the first thing I get is a Compile Error: user-defined type not defined - in reference ro Dim Rs as DAO.Recordset.

Excuse my ignorance - I'm sure it's simple but no idea how to continue.

Thanks

economyman
 
Your missing a reference, try doing a search on the forum...

If you need to send such a big list, consider using a CSV instead...
 
Economyman

You need to go to modules, tools & references and add Microsoft DAO object library.

Started before mailmans post finshed after. Edited later still.
 
DCrake said:
Economyman

You need to go to modules, tools & references and add Microsoft DAO object library.

Started before mailmans post finshed after. Edited later still.
Click to expand...

That works now - big thanks.

Now I am stuck on:
Set Rs = CurrentDb.OpenRecordset(TableOrQueryName)
- I get an Invalid Operation error on this line of code.

Any ideas?

Thank you

economyman
 
Set Rs = CurrentDb.OpenRecordset(TableOrQueryName)

Have you changed this to be your table of query name?

David
 
DCrake said:
Set Rs = CurrentDb.OpenRecordset(TableOrQueryName)

Have you changed this to be your table of query name?

David
Click to expand...

When I did that it gives me another error that it cannot find the table or query. I am using a select query for the recordset and have checked the spelling, etc.
 
Send me the whole code you are using.

David
 
I call the function from a macro using Run Code and the argument:
SplitTableOrQuery("May09Export",65000)

Then I have a module as follows:

Public Function SplitTableOrQuery(TableOrQueryName As String, MaxRows As Long)
Dim Rs As DAO.Recordset

Dim fileNum As Long
Dim TxtStr As String
Dim nIndex As Long
Dim StrPath As String
Dim nFile As Long

StrPath = CurrentProject.Path

Set Rs = CurrentDb.OpenRecordset(May09Export)

If Not Rs.EOF And Not Rs.BOF Then
'Calculate the number of text files required to complete task
Rs.MoveLast

nFile = Int(Rs.RecordCount / MaxRows) + 1
fileNum = 1
Rs.MoveFirst

'Open the first instance of the text file
Open StrPath & "\" & TableOrQueryName & "_" & CStr(fileNum) & ".txt" For Output As #fileNum

Do Until Rs.EOF

For n = 0 To Rs.Fields.Count - 1

TxtStr = TxtStr & CStr(Nz(Rs(n), "")) & ","

Next
'Drop the last comma
TxtStr = Left(TxtStr, Len(TxtStr) - 2)

Print #fileNum, TxtStr
TxtStr = ""

nIndex = nIndex + 1
Rs.MoveNext
If nIndex > MaxRows Then
nIndex = 0
Close #fileNum
fileNum = fileNum + 1
Open StrPath & "\" & TableOrQueryName & "_" & CStr(fileNum) & ".txt" For Output As #fileNum
End If
Loop
Rs.Close
End If
Set Rs = Nothing
Close #fileNum


End Function
 
Let me guess, you have created a new module and placed the function inside the new module. You have then saved the module with the same name as the function. Modules and function cannot have the same name. Access won't stop you from doing so but will tell you you have errors when you try to run something.

Other than that I don't see anything wrong with the code. Let me know.

David
 
DCrake said:
Let me guess, you have created a new module and placed the function inside the new module. You have then saved the module with the same name as the function. Modules and function cannot have the same name. Access won't stop you from doing so but will tell you you have errors when you try to run something.

Other than that I don't see anything wrong with the code. Let me know.

David
Click to expand...

Actually, the module is saved just as Module1 and the function as far as I can tell is called SplitTableOrQuery and comes under the General section so I don't know what else I could be doing wrong.
 
Ok then, lets try something else.
Design Module1 and place a breakpoint on the Function

SplitTableOrQuery

Go into the immediate window (Ctrl+G) and type in

?SplitTableOrQuery("May09Export",65000)

When the yellow bar highlights the first line press F8 to step forward though the function and report back what happens.

David
 
Set Rs = CurrentDb.OpenRecordset(May09Export)

Should be quoted if May09Export is a query or table name:

Set Rs = CurrentDb.OpenRecordset("May09Export")
 
You must log in or register to reply here.

Similar threads

A
Missing data when exporting from excel to Access
Replies
11
Views
627
Isaac
Isaac
Y
Import-export permit tracking system
Replies
13
Views
887
mike60smart
mike60smart
R
Query is fast to run, but very slow to export results
Replies
14
Views
1,157
Rudolph583
R
Rodeo Clown
Access import function converts text field to scientific notation
Replies
9
Views
925
cheekybuddha
cheekybuddha
Papa_Bear1
Access won't "let go" of a linked text file.
2
Replies
34
Views
1,813
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom