Access Table to text file (CSV, Tab, etc). Is there an easy way to do this?

[Irene]

Hi,

I have an MS Access Database with 1 Table containing about 2 million records
in Unicode (diferent languages).

I would like to export the Table to a Text file (CSV, Tab, etc.)

Access dows only export about 65,000 at 1 time.
Almost an imposible job.

I have tried to write different Visual Basic Code to export this Table, but
the Text file was rubbish.

Does anyone knows of an easy way to perform this task?
Or a Freeware program to do this?

This only need to be done once.

TIA

Irene

[Allen Browne]

Re: Access Table to text file (CSV, Tab, etc). Is there an easy way to do this?

Are you sure the file is limited to 64k records?

I just tried exporting a table with 150k records to a CSV file in A2007. All
rows exported.

If you are using an earlier version, did you open the CSV with Excel? If so,
the problem may be with the number of rows Excel can display rather than the
number of rows that Access exported.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Irene" <irene@hotmail. comwrote in message
news:48732c93$0 $25952$6e1ede2f @read.cnntp.org ...

Hi,
>
I have an MS Access Database with 1 Table containing about 2 million
records
in Unicode (diferent languages).
>
I would like to export the Table to a Text file (CSV, Tab, etc.)
>
Access dows only export about 65,000 at 1 time.
Almost an imposible job.
>
I have tried to write different Visual Basic Code to export this Table,
but
the Text file was rubbish.
>
Does anyone knows of an easy way to perform this task?
Or a Freeware program to do this?

[Irene]

Re: Access Table to text file (CSV, Tab, etc). Is there an easy way to do this?

"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:48735f26$0 $7192$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

Are you sure the file is limited to 64k records?
>
I just tried exporting a table with 150k records to a CSV file in A2007.
All rows exported.

I have tried again and got the following error message:

"You selected more records than can be copied onto the clipboard at one
time.
Divide the records into two or more groups, and the copy and paste one group
at the time.
The maximum number of records you can paste at one time is approximately
65,000."

(I have MS Office 2003 installed.)

If you are using an earlier version, did you open the CSV with Excel? If
so, the problem may be with the number of rows Excel can display rather
than the number of rows that Access exported.

I think (but I'm not sure) that I made the initial Table importing an MS
Excell worksheet due to the fact that my file was passing the limit of 65535
entries.

Any valuable solutions in sight?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Irene" <irene@hotmail. comwrote in message
news:48732c93$0 $25952$6e1ede2f @read.cnntp.org ...

>Hi,
>>
>I have an MS Access Database with 1 Table containing about 2 million
>records
>in Unicode (diferent languages).
>>
>I would like to export the Table to a Text file (CSV, Tab, etc.)
>>
>Access dows only export about 65,000 at 1 time.
>Almost an imposible job.
>>
>I have tried to write different Visual Basic Code to export this Table,
>but
>the Text file was rubbish.
>>
>Does anyone knows of an easy way to perform this task?
>Or a Freeware program to do this?

>

[Allen Browne]

Re: Access Table to text file (CSV, Tab, etc). Is there an easy way to do this?

Oh: you tried to copy them to clipboard.

I assumed you were using TransferText in code or a macro.
Could you add a button to your form, and create a macro with TransferText?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Irene" <irene@hotmail. comwrote in message
news:48737286$0 $25951$6e1ede2f @read.cnntp.org ...

"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:48735f26$0 $7192$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

>Are you sure the file is limited to 64k records?
>>
>I just tried exporting a table with 150k records to a CSV file in A2007.
>All rows exported.

>
I have tried again and got the following error message:
>
"You selected more records than can be copied onto the clipboard at one
time.
Divide the records into two or more groups, and the copy and paste one
group at the time.
The maximum number of records you can paste at one time is approximately
65,000."
>
(I have MS Office 2003 installed.)
>
>

>If you are using an earlier version, did you open the CSV with Excel? If
>so, the problem may be with the number of rows Excel can display rather
>than the number of rows that Access exported.

>
I think (but I'm not sure) that I made the initial Table importing an MS
Excell worksheet due to the fact that my file was passing the limit of
65535 entries.

[KC-Mass]

Re: Access Table to text file (CSV, Tab, etc). Is there an easy way to do this?

Irene

If you are only doing this once why bother with code? Just do an export of
the file to a text file.
Highlight the table in the objects panel. Select "File", "Export" on the
main menu. On the panel that pops up change the "save as type" to "Text";
give file a name and click "Export". Done!


"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:48737338$0 $7168$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

Oh: you tried to copy them to clipboard.
>
I assumed you were using TransferText in code or a macro.
Could you add a button to your form, and create a macro with TransferText?
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Irene" <irene@hotmail. comwrote in message
news:48737286$0 $25951$6e1ede2f @read.cnntp.org ...

>"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
>news:48735f26$ 0$7192$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...

>>Are you sure the file is limited to 64k records?
>>>
>>I just tried exporting a table with 150k records to a CSV file in A2007.
>>All rows exported.

>>
>I have tried again and got the following error message:
>>
>"You selected more records than can be copied onto the clipboard at one
>time.
>Divide the records into two or more groups, and the copy and paste one
>group at the time.
>The maximum number of records you can paste at one time is approximately
>65,000."
>>
>(I have MS Office 2003 installed.)
>>
>>

>>If you are using an earlier version, did you open the CSV with Excel? If
>>so, the problem may be with the number of rows Excel can display rather
>>than the number of rows that Access exported.

>>
>I think (but I'm not sure) that I made the initial Table importing an MS
>Excell worksheet due to the fact that my file was passing the limit of
>65535 entries.

>

[Irene]

Re: Access Table to text file (CSV, Tab, etc). Is there an easy way to do this?

Read my response to Allen Browne


"KC-Mass" <connearneyATco mcastDOTnetwrot e in message
news:8e6dnVSIY5 RL5-7VnZ2dnUVZ_hzin Z2d@comcast.com ...

Irene
>
If you are only doing this once why bother with code? Just do an export
of the file to a text file.
Highlight the table in the objects panel. Select "File", "Export" on the
main menu. On the panel that pops up change the "save as type" to "Text";
give file a name and click "Export". Done!
>
>
"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:48737338$0 $7168$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

>Oh: you tried to copy them to clipboard.
>>
>I assumed you were using TransferText in code or a macro.
>Could you add a button to your form, and create a macro with
>TransferText ?
>>
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia
>Tips for Access users - http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
>>
>"Irene" <irene@hotmail. comwrote in message
>news:48737286$ 0$25951$6e1ede2 f@read.cnntp.or g...

>>"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
>>news:48735f26 $0$7192$5a62ac2 2@per-qv1-newsreader-01.iinet.net.au ...
>>>Are you sure the file is limited to 64k records?
>>>>
>>>I just tried exporting a table with 150k records to a CSV file in
>>>A2007. All rows exported.
>>>
>>I have tried again and got the following error message:
>>>
>>"You selected more records than can be copied onto the clipboard at one
>>time.
>>Divide the records into two or more groups, and the copy and paste one
>>group at the time.
>>The maximum number of records you can paste at one time is approximately
>>65,000."
>>>
>>(I have MS Office 2003 installed.)
>>>
>>>
>>>If you are using an earlier version, did you open the CSV with Excel?
>>>If so, the problem may be with the number of rows Excel can display
>>>rather than the number of rows that Access exported.
>>>
>>I think (but I'm not sure) that I made the initial Table importing an MS
>>Excell worksheet due to the fact that my file was passing the limit of
>>65535 entries.

>>

>
>

[Irene]

Re: Access Table to text file (CSV, Tab, etc). Is there an easy way to do this?

"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:48737338$0 $7168$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

Oh: you tried to copy them to clipboard.

No, I didn't.

I did click: "Menu -File -Export -Text files (*.txt, *.csv, *.tab,
*.asc) -Save Formatted -Export All

A Message Window opened with 4 choices, Windows (default), MS-DOS, Unicode,
Unicode (UTF-8).
I did try: Windows (default) and Unicode.

Then another Message Window appears as described before.

"You selected more records than can be copied onto the clipboard at one
time.
Divide the records into two or more groups, and the copy and paste one group
at the time.
The maximum number of records you can paste at one time is approximately
65,000."

Clicking OK, exports exactly 65534 records.

I did also try the solution "Save as" as proposed by KC-Mass, with the same
results.

I assumed you were using TransferText in code or a macro.

Never heard of "TransferTe xt" Macro.

Could you add a button to your form, and create a macro with TransferText?

Can you clarify please?

Do I need first to create a form, add a button named "TransferTe xt", and if
the Button is selected, activate a Macro "TransferText". .?
Where is this Macro?

I run:

Microsoft Ofice Access 2003 (11.5614.5606)
Part of Microsoft Office Professional Edition 2003
Microsoft Windows XP Pack 3

Irene

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Irene" <irene@hotmail. comwrote in message
news:48737286$0 $25951$6e1ede2f @read.cnntp.org ...

>"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
>news:48735f26$ 0$7192$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...

>>Are you sure the file is limited to 64k records?
>>>
>>I just tried exporting a table with 150k records to a CSV file in A2007.
>>All rows exported.

>>
>I have tried again and got the following error message:
>>
>"You selected more records than can be copied onto the clipboard at one
>time.
>Divide the records into two or more groups, and the copy and paste one
>group at the time.
>The maximum number of records you can paste at one time is approximately
>65,000."
>>
>(I have MS Office 2003 installed.)
>>
>>

>>If you are using an earlier version, did you open the CSV with Excel? If
>>so, the problem may be with the number of rows Excel can display rather
>>than the number of rows that Access exported.

>>
>I think (but I'm not sure) that I made the initial Table importing an MS
>Excell worksheet due to the fact that my file was passing the limit of
>65535 entries.

>

[lyle fairfield]

Re: Access Table to text file (CSV, Tab, etc). Is there an easy way to do this?

Runnung this code

-----------
Private Sub BlahBlah()
Dim Recordset As ADODB.Recordset
Dim Buffer$
Dim FileNumber%
Set Recordset = CurrentProject. Connection.Exec ute("SELECT * FROM
Employees")
Buffer = Recordset.GetSt ring(adClipStri ng, , vbTab, vbNewLine)
FileNumber = FreeFile
Open "temp.txt" For Binary As #FileNumber
Put #FileNumber, , Buffer
Close #FileNumber
End Sub
-----------

creates file "temp.txt"

in Northwoods the contents of temp.txt are

-----------
1 Northwind Traders Freehafer Nancy nancy@northwind traders.com
Sales Representative (123)555-0100 (123)555-0102 (123)555-
0103 123 1st Avenue Seattle WA 99999 USA
#http://northwindtraders.com#
2 Northwind Traders Cencini Andrew andrew@northwin dtraders.com
Vice President, Sales (123)555-0100 (123)555-0102 (123)555-
0103 123 2nd Avenue Bellevue WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/# Joined the
company as a sales representative, was promoted to sales manager and was
then named vice president of sales.
3 Northwind Traders Kotas Jan jan@northwindtr aders.com Sales
Representative (123)555-0100 (123)555-0102 (123)555-0103 123 3rd
Avenue Redmond WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/# Was hired
as a sales associate and was promoted to sales representative.
4 Northwind Traders Sergienko Mariya mariya@northwin dtraders.com
Sales Representative (123)555-0100 (123)555-0102 (123)555-
0103 123 4th Avenue Kirkland WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/#
5 Northwind Traders Thorpe Steven steven@northwin dtraders.com
Sales Manager (123)555-0100 (123)555-0102 (123)555-0103 123
5th Avenue Seattle WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/# Joined the
company as a sales representative and was promoted to sales manager.
Fluent in French.
6 Northwind Traders Neipper Michael michael@northwi ndtraders.com
Sales Representative (123)555-0100 (123)555-0102 (123)555-
0103 123 6th Avenue Redmond WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/# Fluent in
Japanese and can read and write French, Portuguese, and Spanish.
7 Northwind Traders Zare Robert robert@northwin dtraders.com Sales
Representative (123)555-0100 (123)555-0102 (123)555-0103 123 7th
Avenue Seattle WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/#
8 Northwind Traders Giussani Laura laura@northwind traders.com
Sales Coordinator (123)555-0100 (123)555-0102 (123)555-0103
123 8th Avenue Redmond WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/# Reads and
writes French.
9 Northwind Traders Hellung-Larsen Anne anne@northwindt raders.com
Sales Representative (123)555-0100 (123)555-0102 (123)555-
0103 123 9th Avenue Seattle WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/# Fluent in
French and German.
-----------

One would change the field delimiter to a comma by modifying

Buffer = Recordset.GetSt ring(adClipStri ng, , vbTab, vbNewLine)
to
Buffer = Recordset.GetSt ring(adClipStri ng, , ",", vbNewLine)

Would this do two million records? I don't know. If not, it could be
modified to add 60000 (3%) at a time to temp.txt.

Access 2003 should have a default ADO reference unless some genius
removed it.


"Irene" <irene@hotmail. comwrote in
news:48732c93$0 $25952$6e1ede2f @read.cnntp.org :

Hi,
>
I have an MS Access Database with 1 Table containing about 2 million
records in Unicode (diferent languages).
>
I would like to export the Table to a Text file (CSV, Tab, etc.)
>
Access dows only export about 65,000 at 1 time.
Almost an imposible job.
>
I have tried to write different Visual Basic Code to export this
Table, but the Text file was rubbish.
>
Does anyone knows of an easy way to perform this task?
Or a Freeware program to do this?
>
This only need to be done once.
>
TIA
>
Irene

[Irene]

Re: Access Table to text file (CSV, Tab, etc). Is there an easy way to do this?

Found the magic trick.

Your solution made me try to do a "Select All" before selecting to "Export"
or to "Save as".
And Bingo, all the records have been exported to a file.

Apparently, if one clicks on Export without doing a "Select All", MS Access
exports only 65534 records.
Even if you click on "Export All".

Thank you all for you help.


"KC-Mass" <connearneyATco mcastDOTnetwrot e in message
news:8e6dnVSIY5 RL5-7VnZ2dnUVZ_hzin Z2d@comcast.com ...

Irene
>
If you are only doing this once why bother with code? Just do an export
of the file to a text file.
Highlight the table in the objects panel. Select "File", "Export" on the
main menu. On the panel that pops up change the "save as type" to "Text";
give file a name and click "Export". Done!
>
>
"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:48737338$0 $7168$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

>Oh: you tried to copy them to clipboard.
>>
>I assumed you were using TransferText in code or a macro.
>Could you add a button to your form, and create a macro with
>TransferText ?
>>
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia
>Tips for Access users - http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
>>
>"Irene" <irene@hotmail. comwrote in message
>news:48737286$ 0$25951$6e1ede2 f@read.cnntp.or g...

>>"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
>>news:48735f26 $0$7192$5a62ac2 2@per-qv1-newsreader-01.iinet.net.au ...
>>>Are you sure the file is limited to 64k records?
>>>>
>>>I just tried exporting a table with 150k records to a CSV file in
>>>A2007. All rows exported.
>>>
>>I have tried again and got the following error message:
>>>
>>"You selected more records than can be copied onto the clipboard at one
>>time.
>>Divide the records into two or more groups, and the copy and paste one
>>group at the time.
>>The maximum number of records you can paste at one time is approximately
>>65,000."
>>>
>>(I have MS Office 2003 installed.)
>>>
>>>
>>>If you are using an earlier version, did you open the CSV with Excel?
>>>If so, the problem may be with the number of rows Excel can display
>>>rather than the number of rows that Access exported.
>>>
>>I think (but I'm not sure) that I made the initial Table importing an MS
>>Excell worksheet due to the fact that my file was passing the limit of
>>65535 entries.

>>

>
>