CODEWITHSUNDEEP
databasemacossqlite
user3126427
user3126427

Reputation: 855

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

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?

Upvotes: 6

Views: 14980

Answers (5)

ThiHuongNguyen
ThiHuongNguyen

Reputation: 21

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

Upvotes: 2

Lachlan Pembroke
Lachlan Pembroke

Reputation: 1

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.

Upvotes: 0

Harold Nelson
Harold Nelson

Reputation: 1

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"

Upvotes: 0

Jia Li
Jia Li

Reputation: 49

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.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

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.

Upvotes: 6

Related Questions