• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

Export 200,000 row table to CSV in Access

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
DrDamnit
Asked:
DrDamnit
  • 8
  • 8
  • 6
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
not as a csv file.
0
 
jefftwilleyCommented:
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
 
jefftwilleyCommented:
DoCmd.TransferText acExportDelim, ", "queryname", "c:\filename.csv", False, ""
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:


DoCmd.TransferText acExportDelim, , "NameOftableOrQuery", "C:\testX.csv", True
0
 
DrDamnitAuthor Commented:
I am NOT an Access guy. You have to spoon feedme this solution. I don't know where to put those commands :-(
0
 
jefftwilleyCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
jefftwilleyCommented:
select the Export Delimited...(forgot that detail)

Let us know if you run into any issues.
0
 
DrDamnitAuthor Commented:
This d*** database appears to have NO tables, only queries. How do I find what seem to be hidden tables?
0
 
Rey Obrero (Capricorn1)Commented:
tools>option >View tab

check Hidden objects then click OK
0
 
DrDamnitAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
DrDamnitAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
jefftwilleyCommented:
>>Must.....find.......way..........to.......export......Microsoft..........Product..........to. ..........Open Source!

lol

Jim Carrey much?
0
 
DrDamnitAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
jefftwilleyCommented:
Any luck on this?
0
 
krisgrpCommented:
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
 
jefftwilleyCommented:
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
 
DrDamnitAuthor Commented:
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
 
jefftwilleyCommented:
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
 
Rey Obrero (Capricorn1)Commented:

same here....
                       http:#a18860535
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 8
  • 8
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now