using text file to upload tables

[ethansql]

hi i tried uploading a large table into my mysql database and in the text file i wrote the following:,

/*

SQLyog Enterprise v4.05

Host – 4.0.13-nt : Database – directory

*********************************************************************

Server version : 4.0.13-nt

*/

USE `databasename`;

insert into tablename values ('57622','Cherry Creek','Ziebach','SD','South Dakota'),('57623','Dupree','Ziebach','SD','South Dakota'),…..

i'm not able to insert this text file… what i did was i loged into my remote mysql server, and i chose DB > Import From SQL Dump… select the file and Execute.

what did i do wrong? please please help.

[peterlaursen]

Is the DB and table existing ??

If not you must include CREATE statement for the DB and the table.

[ethansql]

DB and table is there… now i'm updating the table.

[Ritesh]

Quote:

now i'm updating the table.

So is it working or not?

[ethansql]

it's not working…. not sure how i should write the “insert into…” line that's why i posted on this board. please advise

[Ritesh]

Check http://dev.mysql.com/doc/mysql/en/insert.html for more details.

[ethansql]

ok i started from scratch… db exists but tablename does not exist… so i'm creating a table and inserting the values into the table… please tell me what's wrong with this text file… right now this is what i have and it's not working:

USE `databasename`;

drop table if exists `databasename`.`tablename`;

CREATE TABLE `tablename` (

`col1` varchar(6) default NULL,

`col2` varchar(20) default NULL,

`col3` varchar(20) default NULL,

`col4` char(2) default NULL,

`col5` varchar(20) default NULL

) TYPE=MyISAM;

USE `databasename`;

insert into `tablename` values ('57622','Cherry Creek','Ziebach','SD','South Dakota'),('57623','Dupree','Ziebach','SD','South Dakota'),…..

thanks,

[seanhogge]

From what you posted, I was able to create an .sql file and a test database. This is what I put in the text file:

USE testing;

drop table if exists testing.testtable;

CREATE TABLE testtable (

col1 varchar(6) default NULL,

col2 varchar(20) default NULL,

col3 varchar(20) default NULL,

col4 char(2) default NULL,

col5 varchar(20) default NULL

) TYPE=MyISAM;

USE testing;

insert into testtable values ('57622','Cherry Creek','Ziebach','SD','South Dakota'),('57623','Dupree','Ziebach','SD','South Dakota');

It worked without a hitch on SQLyog v4.05 as long as the testing database exists. When I tried to run it a second time (meaning the table already exists) I again received no errors. I can't see what might cause the error unless you're leaving those ticks or apostrophes in your text file somewhere.

Sorry if that doesn't help you at all.

[ethansql]

that's interesting. my version actually works if i don't try to export a junk of data.

i see that seanhogge's version works as well. thanks seanhogge for sharing your insights.

i'm frustrated that i'm still not able to upload the entire database. however, if i let my friend import it to the web server, it works fine… weird.

having said that, should the term be “export” instead of “import”? because i'm exporting the data on my computer to the webserver.

[seanhogge]

Doesn't it throw an error giving you a line number when you attempt to import the text file to the webserver?

[peterlaursen]

a few points …

You don't import/export to a “webserver”. A webserver (like Apache or IIS) handles HTML-files and possibly preprocessed files like PHP, ASP and JSP. What you mean is probably a “remote mySQL server” located with your webhosting.

SQLyog is a MySQL-client and terms used with with SQLyog are as with the MySQL server itself. So when you are connected to a MySQL server with SQLyog as the client and “upload” a .sql-file it is an IMPORT. Data are IMPORTED to the database controlled to the MySQl-server. Every MySQL client uses this terminlogy.

PLEASE NOTE

I start to be somewhat confused about this thread! Are problems solved ? If there still is a problem, that you wouild like some help solve, then please summarize where you are at. I lost the meaning of all this 😮

If you are unable to IMPORT a .sql file to the databaseserver the INSERT statements could be too long and thus conflict with the MAX_ALLOWED_PACKET setting with the server. If that's it you can uncheck “Create Bulk Insert Statements” in the export dialogue. Then each row will have it''s own INSERT-statement.

[ethansql]

Thanks everyone who contributed to this thread… i'm new to mysql and sqlyog so your insights are useful to my learning curve.

as i mentioned, when i tried importing a large textfile.sql that starts with the following:

USE `databasename`;

drop table if exists `databasename`.`tablename`;

CREATE TABLE `tablename` (

`col1` varchar(6) default NULL,

`col2` varchar(20) default NULL,

`col3` varchar(20) default NULL,

`col4` char(2) default NULL,

`col5` varchar(20) default NULL

) TYPE=MyISAM;

insert into `tablename` values ('1001','first name1','last name1','dob1','education1'),('1002','first name2','last name2','dob2','education2'),…..

here's the error message that i get:

Line no.:23

Error Code: 2006 – MySQL server has gone away

Line no.:23 is the line that starts with “insert into `tablename`values…..”

The weird thing is that if i just used 10-15 values (i.e. 1001… 1002…. 1003…. 1004… 1015), i don't get any error message. i didn't think this would be that difficult, but yet i'm still stuck not being able to import the database into the webserver.

[peterlaursen]

You can read about the error message “MySQL server has gone away” here.

http://dev.mysql.com/doc/mysql/en/gone-away.html

Try putting fewer/only one row/record in each INSERT statement, like

insert into `tablename` values ('1001','first name1','lastname1','dob1','education1');

insert into `tablename` values ('1002','first name2','lastname2','dob2','education2');

etc …

If the test-file is generated with SQLyog you can uncheck the “insert bulk statement” checkbox in the export dialogue box.

[ethansql]

i'm still having problems uploading a large db (about 3mb of data)… can anyone please help. i don't want to break the table into line by line like

insert into `tablename` values ('1001','first name1','lastname1','dob1','education1');

insert into `tablename` values ('1002','first name2','lastname2','dob2','education2');

seanhogge do you know how fix this? you're right, it gives me a line number error message like the following:

Line no.:23

Error Code: 2006 – MySQL server has gone away

please help. thanks,

also, if i break the file into pieces, will that work? if i do that i don't want the file that i just imported to override the previous import.

[Ritesh]

There are many reasons for the above error. Check out http://www.google.com/search?complete=1&hl…way&btnG=Search for more details.

[Author]

Posts