Copying MySQL databases into CSV Format

[Weirfire]

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 :confused:

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

[digitalpoint]

Do you have access to a MySQL client that can connect to the DB server (locally or remotely), like the shell client, or a GUI one? That's going to be the easiest way.

[Weirfire]

Do you have access to a MySQL client that can connect to the DB server (locally or remotely), like the shell client, or a GUI one? That's going to be the easiest way.

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>');


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.

[exam]

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.

[mopacfan]

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.

[Weirfire]

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.

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.
Thanks exam but I already use phpmyAdmin.

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.
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.

[exam]

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 (); And if you used addslashes() when inserting the data into mysql, just use stripslashes() when pulling it out.

[palespyder]

You could also use

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


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

[Weirfire]

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 (); And if you used addslashes() when inserting the data into mysql, just use stripslashes() when pulling it out.

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 (http://pages.ebay.co.uk/turbo_lister/)
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 :)

You could also use


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




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

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. :)

[palespyder]

Yep do it like this.

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

[Weirfire]

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

[exam]

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'";
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 (http://pages.ebay.co.uk/turbo_lister/csvfaq.html) does not appear to address this. You may also read up on the MySql Select statement (http://dev.mysql.com/doc/mysql/en/select.html)

[exam]

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
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.

[palespyder]

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.

[Weirfire]

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.

[Weirfire]

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



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

[palespyder]

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

[Weirfire]

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 :)

[Weirfire]

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. :confused:

[palespyder]

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

[Weirfire]

Nope it doesn't work :( hehe

Worth a try. I've written a script to exchange commas for the HTML character code;


<?php

function RemoveComma($code){

$Length = strlen($code);

print "<tr><td><textarea rows='5' cols='70'>";
for($i=0; $i<$Length; $i++){



if($code{$i}==","){
$code{$i}="&";$i++;$code{$i}="#";$i++;$code{$i}="4";$i++;$code{$i}="4";
}

if($code{$i}=="\n"){
$code{$i}=="";}

print $code{$i};

}

print "</textarea></td></tr>";
Return $code;

}

?>


I was also trying to exchange the newline for a space but neither solution works properly. The exchange function just writes on top of the current description as I would have to move everything along first.

[Weirfire]

<?php

function RemoveComma($code){

$Length = strlen($code);

print "<tr><td><textarea rows='5' cols='70'>";
for($i=0; $i<$Length; $i++){



if($code{$i}==","){
$Carry = $Length - $i;

$codeA = substr($code, 0, $i);
$codeB = ",";
$codeC = substr($code, $i+1, $Carry);
$i=$i+5;
$code = $codeA.$codeB.$codeC;
}

if($code{$i}=="\n"){
$code{$i}=="";}

print $code{$i};

}

print "</textarea></td></tr>";
Return $code;

}

?>

Managed to fix the comma problem. Just the newline problem then we're all set :)

Any ideas how to remove new lines?

[palespyder]

Wouldn't it be easier to do this?


function RemoveComma($code){

str_replace(",", ",", $code);
str_replace("\n", "", $code);


}

[Weirfire]

Do I need to say $code = str_replace(",", ",", $code); ?

Reason I'm asking is that I tried it the way you said and nothing has happened to the code.

[palespyder]

Yeah sorry about that, do that and echo $code back out and see if it worked.

[Weirfire]

Ok I did need to assign the str_replace to $code but I still can't get the newline replaced. I've tried replacing \n and & #12;
I still want to keep the newline when the item is displayed on eBay but the csv doesn't like newlines.

[mopacfan]

Not knowing anything about php, wouldn't it just be easier to write a sql query for what you want?

Select field1, field4, field10, field11, field17 From SomeTable Where ...

And then export the results to a csv file?

[palespyder]

WeirFire, can you post what the output of $code is, I only need one line or so.

[Weirfire]

Thanks for your input mopacfan but what I want to be able to do is select the items which I want to list on eBay by checkboxes on my content management system and then just use the csv which I've written to, to import into the Turbo Lister. It's too much hassle to login to cpanel every time, write a query then export the query table as a csv.

I'm nearly finished doing it this way actually and only need the newline replaced. :)

[Weirfire]

Here's a sample code before any replacements :)


<table width='100%' cellpadding='5' cellspacing='0' border='0' style='background: #6784FF'><tr><td valign ='center' align='center' background='http://www.thepresentshop.co.uk/images/bgpres.jpg' width='250' height='350'><img src='http://www.thepresentshop.co.uk/images/Big/27000.jpg'></td><td valign='top'><table width='100%'><tr><td style='background: #216226; color: white' align='center'><span style='font-size: 150%'>Charles Rennie Macintosh Necklace - P270</span><br><span style='font-size:130%'>Ortak</span></td></tr><tr><td style='font-size: 120%'>Born in Glasgow, Charles Rennie Mackintosh trained as an architect and studied at evening classes in the Glasgow School of Art. At the turn of the century he became one of the forerunners of the distinctive 'Glasgow' style of Art Nouveau. For twenty years he worked as an architect/designer in Glasgow, where all of his best-known work was created and much of it still remains, yet he left the city under-appreciated and died in London in relative obscurity.
18" Chain

Dimensions: 9x24 mm
</td></tr><tr><td><hr></td></tr><tr><td style='font-size: 70%'></td></tr><tr><td><hr></td></tr><tr><td>All of <a href='http://www.thepresentshop.co.uk' style='color: white'>The Present Shops</a> items are brand new, sealed in their original containers.</td></tr><tr><td><hr></td></tr><tr><td><table style='background: #E0E0E0' border='1'><tr><td style='font-size: 120%' colspan='4'><h2 align='center'>Postage Costs</h2></td><td rowspan='4' align='center' width='250'><strong>All products over £5.00 will be sent recorded first class. Our aim is to dispatch winning bidders items within 2 days of purchase.</strong></td></tr><tr><th>Within the UK</th><th>Within Europe</th><th>Intl Zone 1</th><th>Intl Zone 2</tr><tr><td align='center'>1.49 GBP</td><td align='center'>2.79 GBP</td><td align='center'>2.99 GBP</td><td align='center'>3.49 GBP</td></tr><tr><td colspan='4'><p align='center'>If you live outside the UK then you may wish to use the <a href='http://www.xe.com/ucc/'>Currency converter</a> to find out how much the postage will cost.</p></td></tr></table></td></tr><tr><td style='color: yellow'><br><strong>Please visit <a href='http://www.thepresentshop.co.uk/' style='color:white'>The Present Shop's</a> website to buy many more items like this 1. Registration on our site will entitle you to a 5% discount on all products.</strong><br> </td></tr><tr><td><table><tr><td><h2>Happy Bidding</h2></td><td> <img src='http://www.thepresentshop.co.uk/images/Smilie.jpg'></td></tr></table></td></tr></table></td></tr></table></td></tr></table>

[Weirfire]

I have to go catch a plane now but I'll be back on later tonight. :)

[palespyder]

I am going to give you one more thing to try here, try the following:

$code = preg_replace("/(\r\n|\n|\r)/", "", $code);


Let me know if this fixes your newline problem.

[exam]

And if you wanted to replace the newline with a space (I think I read that above- but I was just scanning) do this. $code = preg_replace("/(\r\n|\n|\r)/", " ", $code);
$code = preg_replace("/(\r\n|\n|\r)/", "<BR />", $code);

[Weirfire]

Great work guys. Thanks for all your help. Just little bits and bobs to tidy up now but those little commands have done the trick :)

[palespyder]

Glad we could help ;)

[Weirfire]

:) Another little problem....

I'm using fopen($filename, 'w')) to clear the file before I begin writing to it but it seems to stop writing actual new lines to the file now. All it does is makes those little square things  that represent new lines.

I'll have a play around with it and see if I can sort it.

[palespyder]

Disregard what I wrote before, can you post the code that writes to the file for you?

[Weirfire]

if (is_writable($filename)) {

// In our example we're opening $filename in append mode.
// The file pointer is at the bottom of the file hence
// that's where $somecontent will go when we fwrite() it.
if (!$handle = fopen($filename, 'w')) {
echo "Cannot open file ($filename)";
exit;
}

// Write $somecontent to our opened file.
if (fwrite($handle, $Preamble) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}

fclose($handle);

} else {
echo "The file $filename is not writable";
}


That's the first bit that writes to the file. The second part uses "a" instead of "w"

[Weirfire]

I think I had to write to the file \n\r instead of just \n. It seems to be doing something now!

[palespyder]

Sweet, If you have anymore issues let me know ;)

[Weirfire]

It's really bizarre what it's doing now! It's creating a carriage return on only the first /r but the second /r it just displays the square!

It's not looking like it's going to be one of my good days lol.

[palespyder]

When I am doing something that requires newlines I always do \r\n so if I needed two I would do \r\n\r\n and that seems to work for me, not sure if it will help you or not.

[Weirfire]

Thanks PS. Worked perfectly. How long have you been PHP coding for? I did Java for 3 years at uni as well as C and C++ and I'm trying to convert my knowledge into PHP but there's a lot of little tricks you can do with PHP that I've still to learn.

[palespyder]

I have been doing PHP professionally (day job) for 1 1/2 years, as a hobby for close to 3 I think. The little tricks come as you program more and more. The first language I learned was C, then went to VBScript for ASP pages, then I found PHP and have never looked back ;) I still do some C to keep up on it.

I am told Java and PHP are "alot" alike, but, I have never even looked at Java.

[exam]

When I am doing something that requires newlines I always do \r\n so if I needed two I would do \r\n\r\n and that seems to work for me, not sure if it will help you or not.
Just to throw in my $0.02 at the end of this conversation, \r\n (in that order) is the linebreak char for windows, \n is used (alone) on *nix and \r is used on Mac.

[Weirfire]

Just to throw in my $0.02 at the end of this conversation, \r\n (in that order) is the linebreak char for windows, \n is used (alone) on *nix and \r is used on Mac.

lol do you not like Unix?

Thanks for the $0.02 Definitely worth knowing. :)