Loading the script

The converter, the desktop app and intosql convert write a plain .sql file for one database. This page shows how to run that file from a terminal and from a GUI tool, what the file contains, how to skip the script and load a file directly, and what to do when the database refuses it.

Downloads from the converter are named after the table and the database, such as ventas_2024.postgresql.sql. The examples below use those names; any name works.

What the script contains#

Every script is UTF-8 and starts with a comment naming the database. Then, in order:

1. Session settings, so the database reads the file as UTF-8 and the values as written.
2. A transaction start (all databases except Oracle).
3. CREATE TABLE with the detected types, unless you chose to leave it out for an existing table.
4. The rows, as multi-row INSERT statements of 500 rows each by default.
5. COMMIT.

Whatever the batch size, a statement is closed once it passes 1 MiB, so it stays under every server's packet and batch limits. You change the batch size with --batch-rows in the command line.

A PostgreSQL script from intosql convert --copy uses COPY ... FROM stdin instead of INSERT. Only psql runs that form.

PostgreSQL#

From a terminal:

psql -v ON_ERROR_STOP=1 -d tienda -f ventas_2024.postgresql.sql

ON_ERROR_STOP=1 makes psql stop at the first error and exit with a non-zero code. Because the script runs in one transaction, an error leaves the database as it was. Don't add -1 or --single-transaction: the script opens its own transaction.

Add -h, -p and -U for a remote server, or pass a connection URL to -d.

From a GUI tool, open the file in the query editor (pgAdmin's Query Tool, DBeaver or similar) and run it whole. Scripts made with --copy need psql.

MySQL#

mysql --default-character-set=utf8mb4 tienda < ventas_2024.mysql.sql

Add -h, -P, -u and -p for a remote server. mysql stops at the first error and exits with code 1.

CREATE TABLE commits on its own in MySQL, so if a row fails, the rows are rolled back but the empty table stays. Drop it before you run the script again.

From MySQL Workbench, use File > Run SQL Script and pick the file, or open it and run it in a query tab.

MariaDB#

mariadb --default-character-set=utf8mb4 tienda < ventas_2024.mariadb.sql

The mysql client works the same way. As with MySQL, the table stays when a row fails, empty.

SQLite#

sqlite3 -bail ventas.db < ventas_2024.sqlite.sql

The database file is created if it doesn't exist. Use -bail: without it, sqlite3 reports an error and carries on with the next statement, so a failed CREATE TABLE can still leave rows inserted into a table of the same name. With -bail, it stops and the open transaction is rolled back.

For SQLite, the converter also offers the finished database: next to the script, Download ventas_2024.sqlite gives a database file built from that same script, ready to open in any SQLite tool. It is offered for scripts up to 256 MB.

To load the script in a GUI tool, use DB Browser for SQLite's File > Import > Database from SQL file, or open the .sqlite download directly.

SQL Server#

sqlcmd -S localhost -d tienda -f 65001 -b -i ventas_2024.sql_server.sql

-f 65001 reads the file as UTF-8; without it, accented text can arrive garbled. -b makes sqlcmd exit with an error code when a statement fails. Add -U and -P for SQL authentication, or -E for Windows authentication on older sqlcmd versions.

Text values are written as N'...' and text columns as nvarchar, so every character survives regardless of the database's collation.

From SQL Server Management Studio or Azure Data Studio, open the file and run it. Both read UTF-8 files.

Oracle#

sqlplus app_user@//localhost:1521/FREEPDB1 @ventas_2024.oracle.sql

The script starts with SET DEFINE OFF, so & in your data is kept as written instead of being read as a substitution variable. Rows go in as INSERT ALL ... SELECT 1 FROM DUAL, 200 rows per statement, and a COMMIT ends the script. CREATE TABLE commits on its own in Oracle.

The script doesn't end with EXIT, so sqlplus stays at its prompt when it finishes. To stop at the first error, roll back and exit, wrap it:

sqlplus -S app_user@//localhost:1521/FREEPDB1 <<'EOF'
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
@ventas_2024.oracle.sql
EXIT
EOF

SQLcl runs the same file. In SQL Developer, open it and use Run Script (F5), which honors SET DEFINE OFF.

Loading without a script#

intosql load reads a file and loads it straight into PostgreSQL, MySQL, MariaDB or SQLite. It creates the table with the same types the script would use, loads every row and prints the same summary as convert.

Pro: Loading into PostgreSQL, MySQL and MariaDB is part of IntoSQL Pro, with a 14-day trial that starts on first use. Loading into SQLite is free. See Free and Pro.

intosql load ventas_2024.csv --into postgres://ana@localhost:5432/tienda
intosql load clientes_2024.xlsx --into mysql://ana:secreto@db.example.com:3306/tienda
intosql load clientes_2024.xlsx --into mariadb://ana@localhost/tienda
intosql load pedidos.json --into ventas.db

Loaded 5 of 5 rows from ventas_2024.csv into ventas_2024 for PostgreSQL.
8 fixes made; `intosql inspect ventas_2024.csv` lists them.

Targets#

PostgreSQL connects over TLS when the server offers it, checking the certificate against the public certificate authorities. Add ?sslmode=disable to the URL to connect without TLS. A MySQL server that listens only on a socket takes ?socket=/tmp/mysql.sock.

When the target isn't a database URL or a SQLite file, the error leaves out everything after ://, so a password in the URL isn't printed.

How each database is loaded#

• PostgreSQL: the table is created on one connection, then the rows stream in with COPY ... FROM STDIN over several connections at once (--connections, 4 by default).
• MySQL and MariaDB: multi-row INSERT statements in one transaction (--batch-rows, 500 by default), with the same SET NAMES utf8mb4 the script uses.
• SQLite: prepared inserts in one transaction.

Options#

load also reads the same reading and column flags as convert, such as --sheet, --locale and --exclude. The command line page lists them.

When a load fails#

The load stops at the first error and prints the database's own message:

PostgreSQL refused the load: relation "clientes_2024" already exists

If load created the table, it drops it again, so a failed load leaves nothing behind. A new SQLite file created by the load is removed too. The command exits with code 1.

With --no-create-table, the table is yours and is never dropped. MySQL, MariaDB and SQLite roll back the rows. In PostgreSQL, each connection commits its own COPY, so when one connection fails, rows from the others can remain. Use --connections 1 when loading into an existing PostgreSQL table must be all or nothing.

Loading from the desktop app#

The desktop app loads with the same engine as intosql load:

1. Under Databases, add a PostgreSQL, MySQL, MariaDB or SQLite database. Passwords are kept in the system keychain, never in a file.
2. Open a file and adjust the columns as in the converter.
3. Choose Load into a database, pick the database and start the load.

A progress bar shows the load, and you can cancel it; a cancelled load commits nothing. When it finishes, the app shows how many rows went into which table, or the database's own message when it refuses. Loading into a database server uses the same Pro license and trial as the command line. SQL Server and Oracle load through the script.

Troubleshooting#

The table already exists#

Every script and intosql load create the table. To add rows to a table you already have, turn off Start with the CREATE TABLE statement in the converter, or pass --no-create-table on the command line. To start over, drop the table first. After a failed MySQL or MariaDB script, the empty table is still there.

Permission denied when creating the table#

The user needs permission to create tables in the target schema.

• PostgreSQL 15 and newer don't let every user create tables in public. Grant it (GRANT CREATE ON SCHEMA public TO ana;), or pick a schema the user owns with --schema.
• MySQL and MariaDB need the CREATE and INSERT privileges on the database.
• SQL Server needs CREATE TABLE in the database and ALTER on the schema.
• Oracle needs the CREATE TABLE privilege and a quota on the tablespace.

The schema doesn't exist#

--schema names an existing schema; IntoSQL doesn't create it. Create it first, or leave --schema out.

Accented text arrives garbled#

The script is UTF-8. The client has to read it as UTF-8:

• MySQL and MariaDB: pass --default-character-set=utf8mb4.
• SQL Server: pass -f 65001 to sqlcmd.
• Oracle: set NLS_LANG to a UTF-8 character set before running sqlplus, such as export NLS_LANG=AMERICAN_AMERICA.AL32UTF8.
• PostgreSQL: the script sets client_encoding itself. A database created with a narrower encoding, such as LATIN1, refuses characters it can't store; create the database as UTF8.

Packet or batch size errors#

Statements close at 1 MiB, below MySQL's and MariaDB's default max_allowed_packet. If your server has a smaller limit, convert with a smaller --batch-rows, or raise max_allowed_packet on the server.

The script is too large for a GUI tool#

GUI editors load the whole file before running it. For large scripts, run them from a terminal with the commands above, or use intosql load for PostgreSQL, MySQL, MariaDB and SQLite.