?
Solved

Export 200,000 row table to CSV in Access

Posted on 2007-04-05
23
Medium Priority
?
720 Views
Last Modified: 2008-02-01
I have an Access database table with 203,000 rows. My goal is to move it into MySQL so I have to export it to CSV to import into MySQL. Access says I have to do this 65,000 rows at a time. How do I do that?
0
Comment
Question by:DrDamnit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 6
  • +1
23 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18858215
not as a csv file.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18858223
use the transfertext method to output to a CSV file

DoCmd.TransferText acExportDelim, , "queryname", "FileNameanPath", False, ""

change FAlse to True if you have column headings.
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18858233
DoCmd.TransferText acExportDelim, ", "queryname", "c:\filename.csv", False, ""
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18858293


DoCmd.TransferText acExportDelim, , "NameOftableOrQuery", "C:\testX.csv", True
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 18858451
I am NOT an Access guy. You have to spoon feedme this solution. I don't know where to put those commands :-(
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18858512
lol...
you can do this by creating a macro

so open your DB, go to the MAcros tab

in the first line, select TransferText

at the bottom, you'll see a series of entry boxes.

enter the name of your query

enter the name of the output file, remember to put the CSV extension on it

tell it whether to include the first row as headings.

save this...

then run it.

Voila!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18858525
create a form, place a command button
select the command button , hit F4
click the EVENT tab
select the On Click
click the drop down arrow, select Event Procedure
click the (...) beside the drop down arrow

yyou will see
Private Sub Command0_Click()
' place the codes here as in below

End Sub


Private Sub Command0_Click()

DoCmd.TransferText acExportDelim, , "NameOftableOrQuery", "C:\testX.csv", True


End Sub

you have to change {NameOftableOrQuery } to actual name of table or query
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18858527
select the Export Delimited...(forgot that detail)

Let us know if you run into any issues.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 18859113
This d*** database appears to have NO tables, only queries. How do I find what seem to be hidden tables?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18859122
tools>option >View tab

check Hidden objects then click OK
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 18859184
ok, figured out how to unhide the tables, and have now realized that I actually have to export a report not a table. Can I just put the report's name in the macro instead of the query?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18859206
no,
 find the record source of the report and use it for your export, it could be a query

open report in design view, hit F4  and look for the record source from the property sheet
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 18859250
the query combines several tables into one. each table by itself is useless. I have to be able to export the query to CSV. Must.....find.......way..........to.......export......Microsoft..........Product..........to. ..........Open Source!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18859301
DrDamnit,
Save that query and give it a name , use the name you have given for the export
. just hope no forms parameter input in that query.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18859315
>>Must.....find.......way..........to.......export......Microsoft..........Product..........to. ..........Open Source!

lol

Jim Carrey much?
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 18860393
OK, I can't do this from a form, the only thing that I can get to is the datasheet view. I need to get what I see into a CSV. Can't run macros from Tools>Macro> run because the one I wrote int he VB Editor is not available.

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18860535
can you create macro?
if you can, you can run it from a batch file

C:\Program Files\Microsoft Office\OFFICE11\msaccess C:\Folder1\Databasename.mdb / x  macroName
exit
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18861299
Any luck on this?
0
 
LVL 1

Expert Comment

by:krisgrp
ID: 18862387
Hey.
U can directly convert your access database to mysql database.
there are tools to convert from Access to mysql.
http://www.bullzip.com/products/a2m/info.php

or u can export the mdb database to CSV and then import that CSV to mysql.
with the tools like sqlyog.with this u can easily export/import ur database to/from CSV.
www.webyog.com

these are very useful tools.

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18862410
create a new blank database. Use the File/get external data/import or Link tables to bring in your tables..or if you can see the query that's built for your report, bring it in.

Then you have full control over it.

If it won't help you, and the Bat file that Capricorn suggested doesn't work, then prepare to cut an paste into a spreadsheet...from there you can save as .csv. Might take a bit of time, but it will get you there.

J
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 18873246
OK, here's the issue:

the person who created tihs database has 5 tables each with a single column and XXX rows. Then they run a query to put everything together. It is uber-relational to the point of being unwieldy and difficult to work with.

So, how do I run a macro that will export the data from the result set of a query?
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 2000 total points
ID: 18873274
That's what the .bat file does.

open up your notepad

paste in this

"C:\Program Files\Microsoft Office\OFFICE11\msaccess" "C:\Folder1\Databasename.mdb" / x  macroName

where macroname is the name of the Macro you created (instructions were given for this up the thread a few posts)

save the notepad file as runmacro.bat

now when you double-click on it, it will run the batch file.

Things to consider..
your msAccess application resides in the \Office11 directory

your database doesn't require a password.

Questions?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19140886

same here....
                       http:#a18860535
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question