how to read an sql file into sqlite command in Mac terminal window

Question

I am trying to point to a file I have under a path. my test2.sql look like this:

create table setting (id integer primary key, status text)
insert into setting values (1, 'new')

When I type this command:

sqlite> .read users/name/test2.sql

I get this error:

Error: cannot open "test2.sql"

or behaves as if it has executed without error but no records get added to the table.

I do

sqlite> select * from setting;

and that goes back to:

sqlite> 

Are there any gotchas to using the .read command?

Solution 1

First, each SQL statement must end with a semicolon.

-- file: test2.sql
create table setting (id integer primary key, status text);
insert into setting values (1, 'new');

Second, when the database and the file "test2.sql" are in the same directory, you can .read the file using only the file name. Double quotes around the file name avoids errors when there are spaces in the file name.

$ sqlite3 test.db
SQLite version 3.8.7.2 2014-11-18 20:57:56
Enter ".help" for usage hints.
sqlite> .read "test2.sql"

sqlite> .headers on
sqlite> .mode columns
sqlite> select * from setting;
id          status    
----------  ----------
1           new       

Using the full path should always work. Double quote full paths, too, if there might be spaces in the file name. The string users/name/test2.sql isn't a full path. Full paths always start with /. (The directory separator in POSIX paths is /; in HFS paths the separator is :.) You should also be aware that some (most?) filesystems are case-sensitive: /users/name/test2.sql and /Users/name/test2.sql are two different things.

You can also read the file using command-line redirection. Again, if the database and the SQL file are in the same directory, you can just use the file name.

$ rm test.db
$ sqlite3 test.db < "test2.sql"
$ sqlite3 test.db
SQLite version 3.8.7.2 2014-11-18 20:57:56
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> .mode columns
sqlite> select * from setting;
id          status    
----------  ----------
1           new       

Finally, check permissions on the SQL file.

$ ls -l "test2.sql"
-rw-rw-r-- 1 mike mike 118 Jan 15 09:28 test2.sql

If you don't have read permissions on the file, you won't be able to open it.

Solution 2

I had the same problem with using .read to execute my .sql file even though I put .sql file in the same directory as the SQLite executable. Using the full path of .sql file fixed the problem.

Solution 3

Maybe because sqlite working directory and SQL files are not same even though you put sqlite3.exe file in the same folder with SQL files.

• To check sqlite working directory type .shell pwd on your command line

• Then if you want to change sqlite working directory to SQL files, you can type: .cd full-path_SQL_files

• Next time you don't need to input full path of SQL files into command line anymore

Comments

[user3126427]

thanks for the answer. I just tried it one more time. I created an .sql file that has that table creation and insert statement inside test3.sql in my home directory (name). I then tried to .read "test2.sql" but quickly get: cannot open test3.sql

[Mike Sherrill 'Cat Recall']

Check file permissions. ls -l test*.sql Trying to read a file you don't have read permissions for will return "cannot open filename".

[A J]

The same answer has already added by Jia LI. Please add something new to your answer.

[Lachlan Pembroke]

I disagree, my answer has nothing to do with the directory or full path. My answer has to do with hidden extensions doubling up.