Professional Web Applications Themes

CSV to SQL - MySQL

Hello, I have a list of 73,000 cities in the US that is in CSV format and I wanted to know the best method to convert the list to SQL. Is there some way I can store the list in a huge array so I can interate through each record and add the SQL before and after the values? Any help would be greatly appreciated. Thanks, Aaron...

  1. #1

    Default CSV to SQL

    Hello,

    I have a list of 73,000 cities in the US that is in CSV format and I
    wanted to know the best method to convert the list to SQL. Is there
    some way I can store the list in a huge array so I can interate through
    each record and add the SQL before and after the values? Any help would
    be greatly appreciated.

    Thanks,
    Aaron

    acorn71@gmail.com Guest

  2. #2

    Default Re: CSV to SQL

    com wrote: 
    I typically use a combination of cat and sed to prep the CSV file for
    MySQL.

    If the CSV looked like

    "New York City","New York","10015"

    I would do something create a sed instruction file

    cat > citiesprep.sed
    s/^/insert into tablename values ("/
    s/$/);/


    then run

    sed -f cities.sed <sourcefile >outputfile

    this will have an extra insert into line at the end, edit that off.

    then run

    mysql databasename <outputfile

    That's it. You would need to have a table with three text fields for
    the above example data.

    You would substitute your database and table names where appropriate.

    You might be able to copy / paste the below code then chmod +x it, after
    copying / pasting the sed code above into a file called cities.sed.
    Edit the copied / pasted code below substituting your table/database.

    This assumes you know how to use the editor emacs.


    #!/bin/sh
    sed -f cities.sed <sourcefile >outputfile
    emacs outputfile
    mysql database <outputfile

    echo "select count(*) from tablename;"|mysql databasename

    J.R0wan Guest

  3. #3

    Default Re: CSV to SQL

    <com> wrote:
     
    First thing that enters my mind is mysqlimport as described here:
    http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html.

    For example:
    $ /usr/local/mysql/bin/mysqlimport --user=root --password --verbose
    --delete --fields-terminated-by=';' --lines-terminated-by="\n"
    --columns=your_first_columnname,second_columnname,th ird_columnname
    your_db_name /tmp/your_inputcsv.csv

    NB Unix end-of-line is x0A not x0D so possibly you have to adjust the
    ..csv for this.
     
    Regards,
    Johan

    --
    _____________________________________
    Ing. Johan van Oostrum
    chaos geordend - www.chaosgeordend.nl
    _____________________________________
    Johan Guest

  4. #4

    Default Re: CSV to SQL

    com wrote:
     

    Why?

    If you want to import it into MySQL: use LOAD DATA INFILE.


    If you really want to convert CSV to SQL Statements: there are many
    solutions, depending on your knowledge. You can write a one-liner in
    sed or awk or Perl. Or you write a small program in Java, C or Visual
    Basic. Whatever suits you.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  5. #5

    Default Re: CSV to SQL

    I use this solution for the oracle.
    SQL Loader is the one for Oracle. I hope they have something
    similar for oracle


    M Guest

  6. #6

    Default Re: CSV to SQL


    Axel Schwenke wrote: 
    >
    > Why?
    >
    > If you want to import it into MySQL: use LOAD DATA INFILE.
    >
    >
    > If you really want to convert CSV to SQL Statements: there are many
    > solutions, depending on your knowledge. You can write a one-liner in
    > sed or awk or Perl. Or you write a small program in Java, C or Visual
    > Basic. Whatever suits you.[/ref]

    Or use the CSV engine and avoid import entirely?
     

    toby Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139