Solved

Export 200,000 row table to CSV in Access

Posted on 2007-04-05
23
696 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
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
 
LVL 119

Expert Comment

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

Expert Comment

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

Expert Comment

by:Rey Obrero
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 119

Expert Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

by:Rey Obrero
ID: 19140886

same here....
                       http:#a18860535
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 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

21 Experts available now in Live!

Get 1:1 Help Now