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

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

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...

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

"Allen Browne" <AllenBrowne@SeeSig.Invalidwrote in message
news:48735f26$0$7192$5a62ac22@per-qv1-newsreader-01.iinet.net.au...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.)

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?

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...

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@SeeSig.Invalidwrote in message
news:48737338$0$7168$5a62ac22@per-qv1-newsreader-01.iinet.net.au...

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" <connearneyATcomcastDOTnetwrote in message
news:8e6dnVSIY5RL5-7VnZ2dnUVZ_hzinZ2d@comcast.com...

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

"Allen Browne" <AllenBrowne@SeeSig.Invalidwrote in message
news:48737338$0$7168$5a62ac22@per-qv1-newsreader-01.iinet.net.au...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.
Never heard of "TransferText" Macro.
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

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.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 nancy@northwindtraders.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@northwindtraders.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@northwindtraders.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@northwindtraders.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@northwindtraders.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@northwindtraders.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@northwindtraders.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@northwindtraders.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@northwindtraders.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.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" <irene@hotmail.comwrote in
news:48732c93$0$25952$6e1ede2f@read.cnntp.org:

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" <connearneyATcomcastDOTnetwrote in message
news:8e6dnVSIY5RL5-7VnZ2dnUVZ_hzinZ2d@comcast.com...