Mysql Error No Previous Outfile Available
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up loading script to populate database up vote 2 down vote favorite I am trying to load a script to populate a database. I have created the database and the user, and now I just need to create the tables from the script. I am using this command from the cmd: source C:\Temp\filename.sql; and I either get a message indicating error 2 (that the file cannot be read) or 'No previous outfile available, you must give a file name!' I have looked over questions relating to this, but have not found a solution. Please assist, mehmeh mysql sql share|improve this question asked Aug 30 '12 at 5:46 mehmeh 112 add a comment| 1 Answer 1 active oldest votes up vote 3 down vote In MySQL, backslash is the escape character. So, in your path, replace backslashes by slashes or double backslashes. This gives SOURCE C:/Temp/filename.sql; or SOURCE C:\\Temp\\filename.sql; share|improve this answer answered Aug 30 '12 at 6:14 Éric Guirbal 24614 Hi Eric, that is exactly what i just read elsewhere and tired, and it works!! thank you very much for the assistance! –mehmeh Aug 30 '12 at 6:16 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service
data in a text file. mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars'; Query OK, 8 rows affected (0.00 sec) We select all rows (8) from the Cars table into the cars file located in the /tmp directory. We need to have permissions to write to that directory. $ cat /tmp/cars 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 We show the contents of the file. mysql> DELETE FROM Cars; mysql> LOAD DATA INFILE http://stackoverflow.com/questions/12190572/loading-script-to-populate-database '/tmp/cars' INTO TABLE Cars; In the first statement we delete all rows from the table. In the second statement we load all data from the text file into the Cars table. mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars.csv' -> FIELDS TERMINATED BY ','; In the above SQL statement, we dump all data from the Cars table into a cars.csv file. The FIELDS TERMINATED http://zetcode.com/databases/mysqltutorial/exportimport/ BY clause controls, how the data will be terminated in the text file. We have chosen a comma character. The CSV stands for Comma Separated Values and it is a very common and very portable file format. It can be imported by numerous other applications like OpenOffice, other databases etc. $ cat /tmp/cars.csv 1,Audi,52642 2,Mercedes,57127 3,Skoda,9000 4,Volvo,29000 5,Bentley,350000 6,Citroen,21000 7,Hummer,41400 8,Volkswagen,21600 This is the contents of the cars.csv file. mysql> DELETE FROM Cars; mysql> LOAD DATA INFILE '/tmp/cars.csv' INTO TABLE Cars -> FIELDS TERMINATED BY ','; mysql> SELECT * FROM Cars; +----+------------+--------+ | Id | Name | Cost | +----+------------+--------+ | 1 | Audi | 52642 | | 2 | Mercedes | 57127 | | 3 | Skoda | 9000 | | 4 | Volvo | 29000 | | 5 | Bentley | 350000 | | 6 | Citroen | 21000 | | 7 | Hummer | 41400 | | 8 | Volkswagen | 21600 | +----+------------+--------+ We delete all the data and restore it from the cars.csv file. Exporting to XML files It is possible to export and import XML data using the mysql monitor. $ mysql -uroot -p --xml
Sign in Home Linux Apache MySQL Perl PHP Python CSS Javascript HTML HTML5 Performance References Blog About Me List all pages Site Manager Using Wikidot edit http://khaidoan.wikidot.com/mysql-import-export this panel MySQL - Importing and Exporting Data mysql How does mysqldump and mysqlimport work? Each client program (mysqldump and mysqlimport) examines its arguments to determine what you want it to do, and then constructs appropriate SQL statements and sends them to the server on your behalf. What are the purpose of LOAD DATA INFILE and SELECT … INTO OUTFILE? LOAD mysql error DATA INFILE reads records from a data file, and inserts them into a table. SELECT … INTO OUTFILE writes the records to a file. The two statements are not quite opposites. LOAD DATA INFILE imports a file into a single table, whereas SELECT … INTO OUTFILE can write a result set that may be produced by selecting from multiple tables. How can mysql error no we use SELECT INTO OUTFILE? SELECT column1, column2 FROM mydatabase.mytable INTO OUTFILE "c:/path/outfile.txt"; The INTO OUTFILE clause comes before the FROM clause: SELECT * INTO OUTFILE 'Country.txt' FROM Country; The INTO OUTFILE clause changes the operation of the SELECT statement in several ways: The output produced by a SELECT … INTO OUTFILE statement never leaves the server host. Instead of sending the result over the network to the client, the server writes it to a file on the server host. To prevent files from being overwritten, either accidentally or maliciously, the server requires that the output file not already exist. The statement causes the server to write a new file on the server host, so you must connect to the server using an account that has the FILE privilege. The file is created with filesystem access permissions that make it owned by MySQL server but world-readable. The output file contains one line per row selected by the statement. By default, column values are delimited by tab characters and lines are terminated with newlines, but you can control the output format by