1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Copying MySQL databases into CSV Format

Discussion in 'MySQL' started by Weirfire, May 16, 2005.

  1. #1
    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.
     
    Weirfire, May 16, 2005 IP
  2. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #2
    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.
     
    digitalpoint, May 16, 2005 IP
  3. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #3
    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.
     
    Weirfire, May 16, 2005 IP
  4. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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.
     
    exam, May 16, 2005 IP
  5. mopacfan

    mopacfan Peon

    Messages:
    3,273
    Likes Received:
    164
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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.
     
    mopacfan, May 16, 2005 IP
  6. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #6
    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.
     
    Weirfire, May 16, 2005 IP
  7. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #7
    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.
     
    exam, May 16, 2005 IP
    Weirfire likes this.
  8. palespyder

    palespyder Psycho Ninja

    Messages:
    1,254
    Likes Received:
    98
    Best Answers:
    0
    Trophy Points:
    168
    #8
    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.
     
    palespyder, May 16, 2005 IP
  9. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #9
    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. :)
     
    Weirfire, May 16, 2005 IP
  10. palespyder

    palespyder Psycho Ninja

    Messages:
    1,254
    Likes Received:
    98
    Best Answers:
    0
    Trophy Points:
    168
    #10
    Yep do it like this.

    SELECT * INTO OUTFILE '/somefile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM table WHERE column='value'"; 
    PHP:
     
    palespyder, May 16, 2005 IP
  11. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #11
    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
     
    Weirfire, May 16, 2005 IP
  12. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #12
    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
     
    exam, May 16, 2005 IP
  13. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #13
    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:
     
    exam, May 16, 2005 IP
  14. palespyder

    palespyder Psycho Ninja

    Messages:
    1,254
    Likes Received:
    98
    Best Answers:
    0
    Trophy Points:
    168
    #14
    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.
     
    palespyder, May 16, 2005 IP
  15. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #15
    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, May 17, 2005 IP
  16. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #16
    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. :)
     
    Weirfire, May 17, 2005 IP
  17. palespyder

    palespyder Psycho Ninja

    Messages:
    1,254
    Likes Received:
    98
    Best Answers:
    0
    Trophy Points:
    168
    #17
    I hope it works out for you Weirfire, I know Ebay can make folks alot of money :)
     
    palespyder, May 17, 2005 IP
  18. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #18
    Indeed it can. It's for my client but I get a percentage of every sale :)
     
    Weirfire, May 17, 2005 IP
  19. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #19
    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:
     
    Weirfire, May 17, 2005 IP
  20. palespyder

    palespyder Psycho Ninja

    Messages:
    1,254
    Likes Received:
    98
    Best Answers:
    0
    Trophy Points:
    168
    #20
    Well if that feed will work with the ebay system, yes, if not you have to escape any commas like exam said before.
     
    palespyder, May 17, 2005 IP