Import task automation

[lugishr]

Hello,

First of all, let me tell that SQLiteStudio is great application and I really like it.

I'm using Import tool a lot. I have data available in CSV which I import into SQLiteStudio.
Since I have quite a lot of this CSV files it takes me some time until I import all I need.
So it would be perfect if I could somehow automate this.

Is it possible to automate the import task somehow (stored procedure, batch/cmd script, ...) ?

Thanks,
Hrvoje

[fkuhnke]

Hi Hrvoje

SQLite does not allow stored procedures and has no statement like LOAD DATA INFILE. However, there are at least two options for bulk import of CSV data.

Within SQLiteStudio

You can use the SQLiteStudio import() function to achieve this. Create a SELECT statement similar to this for each CSV file you want to import:

SELECT import('/home/user/sample_tab.csv', 'CSV', 'myTable', 'UTF-8', 'CsvImport.FirstRowAsColumns=true
CsvImport.Separator=4
CsvImport.CustomSeparator=|');

Parameters need to be set as follows:

• full path to CSV file
• Literal string CSV as file type
• Name of table to be used for import
• Encoding of the CSV file
• CsvImport options (see wiki for more info).
  Note: CsvImport options need to be separated by line breaks. In case you can go with the current default simply pass an empty string ('') as parameter. My CSV sample data is a bit eccentric and uses a pipe (|) as field separator -- of course more common values will be comma, semicolon or tab (\t).
  Note as well: Make sure your CSV data has Windows-style line breaks (\r\n). Otherwise the first char of each line might be eaten during import!

When creating the instructions it might be helpful to do this in a good text editor (e.g Notepad++) that gives better support for dealing with larger amounts of text. Once you have everything you need you can save these instructions as SQL file, load it in SQLiteStudio's SQL editor in and execute it from there.

Without SQLiteStudio using the command prompt

Another option might be to use the scripting capability of SQLite. To do this, create a file like the following:

-- open database
.open /home/user/sqlittest.db
.mode csv
-- first parameter is the column separator, second parameter is the line/record separator
.separator "|" "\r\n"
-- add lines below as needed
-- first parameter is path to CSV, second parameter is the table name
.import /home/user/Desktop/sample_01.csv myTable_01
.import /home/user/Desktop/sample_02.csv myTable_02
-- exit SQLite
.quit

When you save this script as import.txt you can run it by calling sqlite3 < import.txt in the Terminal.
Please note that each command is preceded by a dot; a fairly comprehensive documentation on commands and syntax can be found on the SQLite site.

With both methods the table will be created in case it does not exist; otherwise data will be appended.

I appreciate SQLiteStudio very much for evaluating data and testing SQL statements. However, for bulk import/export I prefer the command prompt method because the instructions are much easier to create with a little help from Excel/Calc and some regex magic ;-)

Hope this helps,
Best,
\frank

[lugishr]

Thank you for your answer! I really appreciate it!

[Osophoso]

Hi Frank,

I know this thread is now a few months old but maybe you can also help me. Can you see a reason why the import() function in SQLiteStudio (v3.3.3) run as a query would be considerably slower than when using the Import Tool (from the menu) within SQLiteStudio?

When I say "considerably slower" the difference is 1373 seconds via the SQL command to load 74k lines vs ~ 1 to 2 seconds via the import tool for the same file.

[pawelsalawa]

Please create a bug. It should not be slower.

[rgriswol]

Note of caution - when using the csv import I chose the pipe "|" as my field separator.
I foolishly was caught off guard as my data had unbalance double quotes -
data had the double quote used in a name such as d"Angelo - the unbalance quote set the rest of the data out of sync withe the table definition. Caution - csv import will assume a " defines a field start/stop.