macOS Utility to convert CSV to SQL for import

jerauf said:

My new web host doesn't allow importing data from CSV files using PHPmyAdmin. I use Filemaker to build the database. With my old host, I could export to CSV and import that file without problem. The new host doesn't allow this. So I need to import using SQL statements. And, with all of the special characters, going back over all of my data and fixing them with \'s is going to be a nightmare.

Is there a utility that will convert CSV to SQL statements? Or does Filemaker do it and I'm just not seeing it?

Click to expand...

Have you considered using a text editor like Smultron or Textmate to do a global search/replace of the special characters - I don't know Filemaker so I can't say if it has this sort of functionality?

The other thing is that with a text editor you can do some pretty nifty stuff using regular expressions if ordinary search/replace won't 'cut the mustard'.

jerauf said:

My new web host doesn't allow importing data from CSV files using PHPmyAdmin. I use Filemaker to build the database. With my old host, I could export to CSV and import that file without problem. The new host doesn't allow this. So I need to import using SQL statements. And, with all of the special characters, going back over all of my data and fixing them with \'s is going to be a nightmare.

Is there a utility that will convert CSV to SQL statements? Or does Filemaker do it and I'm just not seeing it?

Click to expand...

Ignore PHPmyAdmin. Login use ssh and run the SQL interpeter.
Which DBMS are you using on the server. Almost all of them have an import utility for CSV files.

If you do have to convert your data to SQL inserts in the past I've used "sed". Sed is a great little editor and it is pretty easy to get it to add the backslashes and SQL syntax. Sed accepts regular expressions and. It can replace tabs with escaped tabs and so on.

But first look at the SQL command line interpeter. I know Postgresql's "psql" wil do what you need.

If you really want a GUI tool for this, you could use Aquafold's AquaDataStudio (version 4.x is free for personal use) it will read a csv file and write a load of SQL statements for you through the Tools -> Import Data function.

Since you are on a Mac, use the Unix command line tools. Perl, sed, awk, and even bash could do this. You would have to write a script, and this type of work is what those languages or utilites were written for.