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?

Have you tried using an absolute path like /Users/name/test2.sql ? — Tom
– Tom, Commented Jan 15, 2015 at 12:32
Sorry, no. The full command would be: .read /Users/name/test2.sql — Tom
– Tom, Commented Jan 15, 2015 at 12:36
Try select * from setting; rather than select * from question; — Mark Setchell
– Mark Setchell, Commented Jan 15, 2015 at 12:37
I tried this too .read /Users/name/test2.sql and it didn't work — user3126427
– user3126427, Commented Jan 15, 2015 at 12:38

Mike Sherrill 'Cat Recall' · Accepted Answer · 2015-01-16 20:24:42Z

6

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.

edited Jan 16, 2015 at 20:24

answered Jan 15, 2015 at 14:49

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3126427 Over a year ago

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' Over a year ago

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

ThiHuongNguyen · Accepted Answer · 2020-08-20 09:49:35Z

2

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

answered Aug 20, 2020 at 9:49

ThiHuongNguyen

212 bronze badges

Comments

Jia Li · Accepted Answer · 2015-09-06 19:24:24Z

1

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.

answered Sep 6, 2015 at 19:24

Jia Li

493 bronze badges

Comments

Harold Nelson · Accepted Answer · 2015-12-12 17:02:59Z

0

There may be a problem with the name of the file if you're working in Windows. Windows hides extensions by default. What you see as "FN" may really be "FN.txt"

answered Dec 12, 2015 at 17:02

Harold Nelson

11 bronze badge

Comments

Lachlan Pembroke · Accepted Answer · 2016-01-23 04:38:03Z

0

I had the same problem, found that I had named the file test.sql.sql due to windows hiding file extensions (new computer). I renamed the file to test.sql and it worked.

answered Jan 23, 2016 at 4:38

Lachlan Pembroke

1

2 Comments

A J Over a year ago

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

Lachlan Pembroke Over a year ago

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

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related