Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export 200,000 row table to CSV in Access

Posted on 2007-04-05
23
Medium Priority
?
721 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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