Merging CSV into MYSQL

Discussion in 'MySQL' started by furca, Sep 27, 2009.

0
  1. #1
    Hello,

    I have never really seen a need to use a database before, but now I am forced to use one. Please read below and tell me how I should go about doing it.

    1) I have 1,000 CSV files. (Approx 800 lines of data in each)
    2) I need to merge them into a single data list.
    3) I think I should use MYSQL on my server to do this.
    4) After merging them into one I need to delete all of the columns except the first.
    5) Finally, I need a single notepad file of the data.

    --- I would do this in excel but it exceeds excels limits.
    --- The data is sorted by column #2 and I wish for the final output to be sorted by column #2

    Please tell me how to do this!

    Cheers,
    Andy
     
    furca, Sep 27, 2009 IP
  2. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #2
    Use MySQL's LOAD DATA INFILE statement to import data to a table. Do whatever you want to with your data then delete rest of the rows if needed otherwise simply export a single column according to needs using INTO OUTFILE option or using a PHP script to export data to csv or text.
     
    mwasif, Oct 1, 2009 IP
  3. furca

    furca Well-Known Member

    Messages:
    1,478
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    125
    #3
    Thank you for the help! A few more questions..

    I have all of the CSV files (over 1000) in one single folder on my computer. Can I "easily" upload these to MySQL into 1 table? Would this involve moving all files to server then running a command? Is there a way to do them all in one batch with one command instead of individually?

    Thanks
     
    furca, Oct 1, 2009 IP
  4. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #4
    I believe you can do that into 1 table. You can use phpMyAdmin Import option to import csv tables one by one. Otherwise you have to move all files to the server using FTP and then using mysql command line import one file at a time or make some PHP or anyother script to get all these files one by one and import them. I don't think you can batch insert all the files in a single command.
     
    mwasif, Oct 1, 2009 IP
  5. furca

    furca Well-Known Member

    Messages:
    1,478
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    125
    #5
    I figured it out. Now, could you guys assist me on the approp. query to..


    1) Search a particular field called KEYWORDS for X.
    2) Each time X is there DELETE THE ROW.

    I really appreciate the help guys. I'm learning a lot!
     
    furca, Oct 2, 2009 IP
  6. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #6 
    SELECT * FROM table_name WHERE KEYWORDS='X'
    Code (markup):
    DELETE FROM table_name WHERE KEYWORDS='X'
    Code (markup):
    Backup your database before running DELETE query.
     
    mwasif, Oct 2, 2009 IP
  7. furca

    furca Well-Known Member

    Messages:
    1,478
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    125
    #7
    NVM found it :)
     
    Last edited: Oct 2, 2009
    furca, Oct 2, 2009 IP
  8. Organic Chem

    Organic Chem Peon

    Messages:
    14
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #8
    If in fact you needed to merge your 1000+ csv files into one file and assuming you are not using SQL, you can easily merge the csv files using bulk file merger. This puppy can merge .xls, .csv, .pdf, .doc, and .txt files into one master file.

    Site: http://essexredevelopment.com

    Any yes, this is my product.
     
    Organic Chem, Dec 14, 2009 IP