CODEWITHSUNDEEP
databasecsvapplescriptautomator
gurkensaas
gurkensaas

Reputation: 913

Converting a .csv table into a database in Applescript/Automator

So I have a .csv-table that contains some content that I would like to transfer into a database, preferably a .db-file. I searched across Applescript and Automator and Applescript and found a few things:

  1. There is an application preinstalled on all macOS systems called Database Events. This application can be used to create new databases, however, I do not know how to transfer the data between a .csv-table and a newly created database without reading thousands of cells.
  2. In Automator, there is an action called Convert CSV to SQL, which sounds like the exact thing I am searching for, however, no matter how I input the data (the action says it takes text, folders/files), it returns nothing.

Is there anything I have missed?

Upvotes: 1

Views: 560

Answers (2)

Mockman
Mockman

Reputation: 1205

Assuming an existing sqlite database which contains a table with fields, the following automator application should allow you to feed comma-separated text into it. Alternatively, it could be rendered as a workflow by adding one of the 'get items' actions at the top. This example uses a table with two fields.

  • Combine Text Files -- Passes text from dropped csv to the next action. Should allow for multiple dropped files.

  • Convert CSV to SQL -- NB this does not execute anything. It merely assembles sql commands. Three elements are used here: before, per row, after. 'per row' should include the table name and inside parentheses, the field names. Presumably, by adding variables you can add more fields. Edit to suit your purposes.

    begin transaction;

    INSERT INTO people(surname, givenname) values("%{1}@","%{2}@");

    end transaction;

  • View Results -- This can be helpful during testing but additionally, it supposedly helps correct for some automator issue with the previous action.

  • Set Value of Variable -- This stores the above-generated statements (e.g. 'SQL Statements')

  • Ask for Finder Items -- This action is flaky for me. I don't get any text prompt and it often repeats the file prompt (I escape the second instance). Type should be Files. Select the database here. Check 'ignore this action's input'. If your database never changes, you could substitute 'get specified…'.

  • Set Value of Variable -- Create a new variable in which to store the database reference (e.g. 'SQL Database')

  • Get Value of Variable -- Fetches the previously stored SQL statements. Check 'ignore…'.

  • Apply SQL -- Database should be set to the database variable set two actions above (e.g. 'SQL Database'). Output should be plain text.

The above (and below) is from Making a standalone application to add data from CSV to Database. Note that for whatever reason, the code blocks there include the page's html code so that needs to be stripped out (as above). The page is well worth reading.

In addition to the above, I use a basic workflow to execute various simple sql commands upon the database.

  • Get Specified Finder Items -- point this at the database file
  • Execute SQL -- I paste the command I wish to use here
  • View Results -- And see what happened here.

The commands I use in the 'execute' action are as follows:

  • create table people(surname varchar, givenname varchar); -- creates a table with two fields
  • SELECT * FROM people; -- lists all records in table
  • DELETE FROM people; -- deletes all records from table

While such stuff could be done in the terminal or a dedicated application, for this I prefer to stay within automator.

Upvotes: 1

Ted Wrigley
Ted Wrigley

Reputation: 3194

the Convert CSV to SQL action doesn't do anything accept add SQL commands into your data file. You send the output to the Execute SQL action to run the SQL commands, which is what would populate the database. And the Database Events app never worked well, or did much.

I'd recommend you go to the App Store and download one of the many SQLite3 database apps: most .db files on MacOS contain SQLite3 formatted data. You'll still need to go on the web and master the basics (Google 'sqlite3 tutorial'), but a well-designed app should do most of the heavy lifting for you, and all of them will import CSV data without fuss or bother. If you can't find one that has an AppleScript dictionary, let me know; that might be my next coding project.

Upvotes: 0

Related Questions