I got a mysql database "backup" that someone made and I have to use it, trying to get a website back online. Its a Joomla web database. The person gave me a file "database.txt" (not database.sql) which opens with Notepad. Trying to "import" in phpmyadmin does not work and just gives out errors like

" Ending quote ' was expected. (near "" at position 4438), and " 42 values were expected, but found 21. (near "(" at position 3967)

I'm attaching 2 screeshots here so that you guys can see the contents of this file,somewhere it says mysql dumping, making me feel that the file was created by mysqldump, but I wonder why it shows up as .txt and not .sql file.

Is there a way I can still get this database into my server (offline or online)? I will appreciate a guide or an online tutorial on how to accomplish this. I really do need the database.


Recommended Answers

All 2 Replies

The screenshot starts by saying it is a phpmyadmin sql dump. Although we can't see the entire lines of code, it looks as if it is starting with the table definitions before the data itself is added. You will have to look for the create statements yourself to confirm this. you should also check for insert commands in the file.

So if it has been done correctly, all you need to do is change the name from database.txt to database.sql, and using phpmyadmin click on the import button, select your file and see what happens. Or copy and paste it into the sql query box in phpmyadmin. sometimes you get problems with file sizes however, and things run slow or grind to a halt.

To work successfully, the tables need to be created and populated in the correct order. IE if a table has three foreign keys it refers to, their tables must be created first so the foreign keys' source tables exist. You might have to run the import a couple of times if you get problems.

If you keep getting the errors, then sorry, but it's a bad dump file.

However it is much MUCH if you use a proper database backup plugin to create a backup of the site then restore it to the new site's server. That way any plugins and images are transfered as well.

If you need to import data from text file (.txt, .csv, ...) to a database table, you should use LOAD DATA INFILE.

LOAD DATA LOCAL INFILE '/home/cpaneluser/data.txt' INTO TABLE Data;

LOAD DATA LOCAL INFILE 'file-path' INTO TABLE table-name;

You need to upload the text file to your hosting account and then add the path to the query.

Note: Line terminators can differ depending on the operation system where the file has been created.

In Linux a newline is signified with the following characters: \n

In Windows a newline might be signified with \r\n or only \r (WordPad).

You need to know that difference if you need to specify newlines in your SQL import query.

For example:


If the text file is created on Windows OS, you might have to use LINES TERMINATED BY '\r\n' as a line terminator.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.