From CSV to SQL: A Quick Way to Import Data
Turn a spreadsheet export into INSERT statements, check for duplicates first, and avoid the usual quoting problems.
Someone sends you a spreadsheet of customers and asks you to load it into a database. You could write an import script. Or you could generate the SQL in thirty seconds.
Clean the data first
Look for duplicates before you import. Paste one column, such as email addresses, into Find Duplicate Lines and see which ones repeat. Fixing them now saves a unique-key error later.
Generate the INSERT statements
Paste the CSV into the CSV to SQL converter. The first row becomes the column names. Numbers stay unquoted, text is wrapped in quotes with apostrophes escaped, and empty cells become NULL. Set the table name and copy the result.
Tidy and review
If you merge the statements with other queries, run them through the SQL formatter so they are easy to read in a code review.
Things to watch for
- Dates should be in a format your database understands, usually YYYY-MM-DD.
- Phone numbers and PIN codes that start with zero should be text, not numbers. Quote them in the CSV so the leading zero survives.
- For large files, use your database's bulk import instead, such as
LOAD DATAorCOPY. - Always test on a copy first.
If you need the data as JSON for an API instead, the CSV to JSON converter does the same job.