Solved

Export 200,000 row table to CSV in Access

Posted on 2007-04-05
23
693 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
  • 8
  • 8
  • 6
  • +1
23 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
not as a csv file.
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
Comment Utility
DoCmd.TransferText acExportDelim, ", "queryname", "c:\filename.csv", False, ""
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


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

Author Comment

by:DrDamnit
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
select the Export Delimited...(forgot that detail)

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

Author Comment

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

Expert Comment

by:Rey Obrero
Comment Utility
tools>option >View tab

check Hidden objects then click OK
0
 
LVL 32

Author Comment

by:DrDamnit
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
>>Must.....find.......way..........to.......export......Microsoft..........Product..........to. ..........Open Source!

lol

Jim Carrey much?
0
 
LVL 32

Author Comment

by:DrDamnit
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
Any luck on this?
0
 
LVL 1

Expert Comment

by:krisgrp
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility

same here....
                       http:#a18860535
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now