2

I need to dump some of the columns of my Excel spreadsheet to a table in a SQLite database on Mac OS.

I have installed the ODBC drivers and I think I might be able to code up a solution. But before doing this can anyone suggest any other ways to move data from Excel to SQLite database? Ideally I would like to automate the process as I will do this for several tables.

Alan2
  • 23,493
  • 79
  • 256
  • 450
  • 1
    Export to a CSV file in Excel, import that with [sqlite3](https://www.sqlite.org/cli.html#csv_import). – Shawn Nov 19 '18 at 04:53
  • I was thinking that but my data already has in it a lot of commas, quotes and non-english characters so would that not be a problem making that into a CSV? – Alan2 Nov 19 '18 at 05:03
  • Shouldn't be a problem. Just make sure it's exported as UTF-8 so the characters can be saved properly. – Shawn Nov 19 '18 at 05:23
  • 1
    Export a small example asUTF-8 and compare... if it works then fine, don’t just assume it won’t – Solar Mike Nov 19 '18 at 05:29

1 Answers1

1

CSV export in excel and then an import into the database is going to be by far the easiest path forward.

On excel: take care with excel's habit of using the locale of the user and changing how it works depending on that. If you aren't in the USA, odds are excel will do things like using a "," as decimal point or want to use a ";" to separate the CSV fields. If it does that for you, you might need to try to change the locale to make it appear that you're in the USA.

Just test what it produces, and fix it as needed. To view the CSV, just use textedit or something similar.

You should also be able to open excel files in numbers, but it too saves CSV in the locale of the user.

This might also help: Import CSV to SQLite