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

<acorn71@gmail.com> wrote:

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

>
> Thanks,
> Aaron
Regards,
Johan

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

acorn71@gmail.com wrote:

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

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/

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

Axel Schwenke wrote:
> acorn71@gmail.com wrote:
>
> > 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.
>
> 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.

Or use the CSV engine and avoid import entirely?

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