Copying MySQL databases into CSV Format

Is there a way of converting the MySQL databases into CSV format without having to log in to cpanel?

I've currently written a script which writes to a file <file>.csv but I've found that this might not be the best way because of all the different quotation marks needed

If there's a way of converting the database from the front end then this would make things a lot tidier.

 

I'm not too knowledgeable on the terminology because I've taught myself most of this stuff. Is the client the thing where you connect to the database by inserting the code

   $dbh=mysql_connect 'localhost', '<username>', '<password>') 
   or die ('I cannot connect to the database because: ' . mysql_error());
   mysql_select_db ('<dbname>');

Code (markup):

If so then yes. Otherwards the only other place where I can view the databases is when I access it through cpanel.

It might actually be quicker to display the information in a table, then right click on it and convert it into an excel spreadsheet. You can convert it without any problems into CSV format in Excel.

 

Simply install PhpMyAdmin on your web server, log in, and click export. See www.phpmyadmin.net. By the way, what code do you have to export ytour data? It's not really that hard to make it work.

 

If you're using windows, you can use the MySQL control panel and easily export the data to a csv file. I don't know what's available on non-windows systems. You could also connect by odbc and import the data into excel as well.

 

I have no problems exporting it into CSV format but I'll let you know exactly what I'm doing.

With the eBay Turbo Lister you can import csv databases with the product information to list items. I have an online catalogue where I want to add these items to eBay through the Turbo Lister. I only want to add certain items at different times and use checkboxes to decide which items I want to add.

So in simple terms, I have a query results table of data which I want converted into a CSV database so I can import the data quickly into the Turbo Lister. I want to be able to do it from my own content management system without logging in to cpanel every time I want to add new items to the Turbo Lister.

Thanks exam but I already use phpmyAdmin.

Yes, I use Windows. Is the MySQL control panel the same thing that exam is talking about? Either way, it's probably still not going to be the most efficient way of doing things. But thanks anyway I'm going to eventually be adding hundreds and sometimes thousands of items every week so it has to be the most efficient method of adding items to eBay. No quick fixes this time lol.

 

What CMS are you using or is it something you scripted? The easiest way to do it would be to write a simple script that exports your table to CSV and saves it as a file. Then you simply run TurboLister (I don't know what that is, is it a script on your webserver or an app on your PC?) and import the saved file.

Your export script is as simple as:

/* assuming you're already connected to mysql */
$q = 'SELECT * FROM table WHERE 1';
$r = mysql_query($q);
$out = '';
while ($l = mysql_fetch_array($r)) {
    $out .= '"' . implode ('","',$l) . "\"\n";
}
$f = fopen ('current_data.csv','w');
fputs ($f, $out);
fclose ();

PHP:

And if you used addslashes() when inserting the data into mysql, just use stripslashes() when pulling it out.

 

You could also use

"SELECT column1, column2, column3 FROM tablename INTO OUTFILE '/tmp/somefile.csv' FIELDS TERMINATED BY ',';"

PHP:

Been awhile since I tried but I believe this will do what you are looking for.

 

I scripted the CMS myself. My main problem is that there are 90 fields to pass into the Turbo Lister of which only 11 I've calculated are different for each item. There is 1 difficult field to pass through which is the description field which carries a lot of quotes but your method may work as I've not learned the deal with addslashes and stripslashes before. What exactly are they and how are they useful. (I'll be reading up on them once I've replied but I always find it useful to hear what people say about functions from their own point of view)

You can download Turbo Lister from here
It's a free piece of software developed by eBay to help businesses or anyone to list items in bulk and it saves an unbelievable amount of time if you are adding a great deal of products at any 1 time.

Thank you for your help

That is also very good and could turn out to be very useful. Is there a way to end each entry with a newline when outputting in this way? Perhaps have the last field as /n?

Thank you to you both.

 

Yep do it like this.

SELECT * INTO OUTFILE '/somefile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM table WHERE column='value'"; 

PHP:

 

Great stuff Palespyder!

You wouldn't happen to know any tricks where I can just use 11 fields from the database and pass all the other 79 fields in between them? lol

 

Palespyder, your OUTFILE code is much better than my original idea. For the description field which could contain commas and screw things up, you may want to make some more ajustments.
Also you could do a select * or select just the 11 fields that are needed.

SELECT * INTO OUTFILE '/somefile.csv' 
FIELDS ESCAPED BY '\' 
FIELDS OPTIONALLY ENCLOSED BY '"'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
FROM table WHERE column='value'"; 

Code (markup):

Now it doesn't matter what is in the fields, and any double quotes (") will be escaped (written out as (\") ). I don't know how turbo lister deals with this the turbo lister csv faq does not appear to address this. You may also read up on the MySql Select statement

 

I'm not sure about the syntax (never had to do it), but you can create temporary tables and select into them. Just be sure to create the tmp table with default vals for the 79 fields you aren't going to modify. Should problable work. Or you could use the code I originally posted and in the while loop, insert the 11 values inbetween the default values instead of simply imploding the 11 values together.

 $out .= '"defval1","defval2","defval3","'.$l[0].',"defval4"'; // etc.

PHP:

 

You are exactly right exam, I didn't even consider the commas in a description field. I have not done this in a long time so I am trying to remember exactly how to do it all As far as just selecting the 11 fields I would suggest the same as exam.

 

Hmmm, I'm having a bit of trouble importing the items into the Turbo Lister surprise surprise although when I export eBay Listings to csv from the Turbo Lister there are no quotations around each field. Each field is seperated by a comma only. It also has the field names listed first before the actual values begin. It's particularly odd that there is no guide material provided by eBay for this. The instructions for importing CSV's are very limited and only show the table of values which need passed.

For now I'm going to try and create csv's identical to the exported csv files which the Turbo Lister creates to see whether this makes any difference.

Is there anyone else who uses eBay to list large amounts of products. The Turbo Lister is definitely worth getting if you do.

 

Ok I've been playing around with the import data for the Turbo Lister and I've discovered that all you need to do is list the header fields at the top and list the item information on new seperate lines. You can list the details in any order so I will be able to use Palespyders method for listing.

An example format that works for anyone viewing this thread is;

Site,Format,Currency,Description
3,1,3,Description of Item
3,1,3,Description of 2nd item

Code (markup):

Thanks guys for your help. I'll let you know how I get on.

 

I hope it works out for you Weirfire, I know Ebay can make folks alot of money

 

Indeed it can. It's for my client but I get a percentage of every sale

 

OK the next problem is what to do with commas in the description field. I have some Javascript which uses commas and also some description text which displays commas. Is there any way round this so that I can still use the commas. Would it work if I had something like

Field1~Field2~Field3
Field1,data~Field2,data~Field3,data

So instead of comma seperated values it's wavelength seperated values.

 

Well if that feed will work with the ebay system, yes, if not you have to escape any commas like exam said before.