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]

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" <ir***@hotmail.comwrote in message
news:48***********************@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]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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" <ir***@hotmail.comwrote in message
news:48***********************@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]

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" <ir***@hotmail.comwrote in message
news:48***********************@read.cnntp.org...

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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]

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" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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" <ir***@hotmail.comwrote in message
news:48***********************@read.cnntp.org...

>"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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]

Read my response to Allen Browne
"KC-Mass" <connearneyATcomcastDOTnetwrote in message
news:8e******************************@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" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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" <ir***@hotmail.comwrote in message
news:48***********************@read.cnntp.org.. .

>>"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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 "TransferText" 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 "TransferText", 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" <ir***@hotmail.comwrote in message
news:48***********************@read.cnntp.org...

>"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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]

Runnung this code

-----------
Private Sub BlahBlah()
Dim Recordset As ADODB.Recordset
Dim Buffer$
Dim FileNumber%
Set Recordset = CurrentProject.Connection.Execute("SELECT * FROM
Employees")
Buffer = Recordset.GetString(adClipString, , 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 na***@northwindtraders.com
Sales Representative [phone] [phone] (123)555-
0103 123 1st Avenue Seattle WA 99999 USA
#http://northwindtraders.com#
2 Northwind Traders Cencini Andrew an****@northwindtraders.com
Vice President, Sales [phone] [phone] (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 ja*@northwindtraders.com Sales
Representative [phone] [phone] [phone] 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 ma****@northwindtraders.com
Sales Representative [phone] [phone] (123)555-
0103 123 4th Avenue Kirkland WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/#
5 Northwind Traders Thorpe Steven st****@northwindtraders.com
Sales Manager [phone] [phone] [phone] 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 mi*****@northwindtraders.com
Sales Representative [phone] [phone] (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 ro****@northwindtraders.com Sales
Representative [phone] [phone] [phone] 123 7th
Avenue Seattle WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/#
8 Northwind Traders Giussani Laura la***@northwindtraders.com
Sales Coordinator [phone] [phone] [phone]
123 8th Avenue Redmond WA 99999 USA
http://northwindtraders.com#http://n...dtraders.com/# Reads and
writes French.
9 Northwind Traders Hellung-Larsen Anne an**@northwindtraders.com
Sales Representative [phone] [phone] (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.GetString(adClipString, , vbTab, vbNewLine)
to
Buffer = Recordset.GetString(adClipString, , ",", 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" <ir***@hotmail.comwrote in
news:48***********************@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]

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" <connearneyATcomcastDOTnetwrote in message
news:8e******************************@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" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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" <ir***@hotmail.comwrote in message
news:48***********************@read.cnntp.org.. .

>>"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:48**********************@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.