"error While Importing Data" Sqlyog Enterprise

[lc.cooek]

Hi,

I am fairly new to this, and stuck like a mule in a puddle of mud. So I hope someone can help me. I am trying to import an excel file saved as .cvs and I keep getting this error: “error while importing data” in SQLYog Enterprise. its just good enough to confuse me. I came across an old conversation on this site that talked abut escape characters… so I tried to play around with the escape characters but no luck. I just don't understand why this is so difficult when exporting data is such a breeze.

Much obliged for any help.

Lance

[peterlaursen]

I think the easiest would be if you attached

1) a small .csv file to reproduce with

2) a screenshot of you settings in the import dialogue

3) the CREATE TABLE statement ofr the table you are importing to.

[lc.cooek]

peterlaursen wrote on Jun 19 2007, 07:29 AM:

I think the easiest would be if you attached

1) a small .csv file to reproduce with

2) a screenshot of you settings in the import dialogue

3) the CREATE TABLE statement ofr the table you are importing to.

Thank you. Here is the stuff. Please take a look at your convenience.

The table is already there I just want to append data from an excel file. but here is the command generated by Yog:

load data local infile 'C:/Documents and Settings/My Documents/KCMC/KCMC_Files/Administration/Documents/Demographic_Data/db_kcmc_user.csv' into table `kcmc_site`.`kcmc_users` fields escaped by '\' terminated by ',' enclosed by '”' lines terminated by 'rn' ignore 1 lines ( `email`, `user_guid`, `firstName`, `lastName`, `clinicName`, `phone`, `postal`, `city`, `street`, `province`, `country`, `specialty` )

I have also attached a screen shot of the .cvs file – for some reason the forum doesn't allow me to attach actual file.

Thank you very much for your help.

Lance

[peterlaursen]

You can always zip files before attaching.  Please do!

[lc.cooek]

peterlaursen wrote on Jun 19 2007, 01:35 PM:

You can always zip files before attaching.  Please do!

I am attaching the zipped file.

Thank you.

[adarsh]

Hello,

Please attach the CREATE TABLE statement of the table you are importing to.

Thanks

[lc.cooek]

adarsh wrote on Jun 19 2007, 02:48 PM:

Hello,

Please attach the CREATE TABLE statement of the table you are importing to.

Thanks

this is what I get when I expost table as sql statement…I am assuming this is what you asked for.

CREATE TABLE `kcmc_users` (

`email` varchar(50) NOT NULL default '',

`user_guid` varchar(32) default NULL,

`firstName` varchar(30) default NULL,

`lastName` varchar(30) default NULL,

`clinicName` varchar(50) default NULL,

`phone` varchar(15) default NULL,

`postal` varchar(7) default NULL,

`city` varchar(30) default NULL,

`street` varchar(100) default NULL,

`province` varchar(20) default NULL,

`country` varchar(30) default NULL,

`specialty` text,

PRIMARY KEY (`email`)

)

Thank you,

Lance

[adarsh]

Hello,

It is not reproducable here.

Please specify the SQLyog version you are using. and also please try the SQLyog 6.03 and let us know the details.

Thanks

[lc.cooek]

adarsh wrote on Jun 20 2007, 05:41 AM:

Hello,

It is not reproducable here.

Please specify the SQLyog version you are using. and also please try the SQLyog 6.03 and let us know the details.

Thanks

Hi,

we are using SQLYog Enterprise GUI v5.19

[peterlaursen]

Then please try version 6.03!

There is no difference with CSV-import between COMMUNITY and ENTERPRISE. it does not matter which one you use.  Also if you wnat to keep 5.19 you can install 6.03 to another folder.  Now if this is already fixed between 5.19 and 6.03 there really is nothing else we can do!

We cannot reproduce this with 6.03!

[lc.cooek]

peterlaursen wrote on Jun 20 2007, 03:42 PM:

Then please try version 6.03!

There is no difference with CSV-import between COMMUNITY and ENTERPRISE. it does not matter which one you use.  Also if you wnat to keep 5.19 you can install 6.03 to another folder.  Now if this is already fixed between 5.19 and 6.03 there really is nothing else we can do!

We cannot reproduce this with 6.03!

I installed 6.03, but it doesn't let me connect to remote server! HELP!

[peterlaursen]

“I installed 6.03, but it doesn't let me connect to remote server”

I am sorry but there is no information we can use here!

Why not?  How do you connect? What is the difference you experience between 5.19 and 6.03?

What happens – do you get some error message?

I cannot see any reason why 6.03 should behave otherwise than 5.19 in this respect!  You must explain the problem so that we can understand what is happening!

[peterlaursen]

“I installed 6.03, but it doesn't let me connect to remote server”

I am sorry but there is no information we can use here!

Why not?  How do you connect? What is the difference you experience between 5.19 and 6.03?

What happens – do you get some error message?

I cannot see any reason why 6.03 should behave otherwise than 5.19 in this respect!  You must explain the problem so that we can understand what is happening!

[lc.cooek]

peterlaursen wrote on Jun 21 2007, 08:07 AM:

“I installed 6.03, but it doesn't let me connect to remote server”

I am sorry but there is no information we can use here!

Why not?  How do you connect? What is the difference you experience between 5.19 and 6.03?

What happens – do you get some error message?

I cannot see any reason why 6.03 should behave otherwise than 5.19 in this respect!  You must explain the problem so that we can understand what is happening!

Sorry about that. The prpblem is that in the enterprise version the connection window has two tabs: the “Server” tab and the “Tunnel” tab. so I can give it information about where the host isactually sitting. In the community version (6.03) the connection window only has the “Server” tab which means it always assumes that the server is sitting locally, doesn't it? but we have a dedicated server that needs to be connectedto remotely. Please let me know if this doesn't make sense to you.

[peterlaursen]

NO .. it is not like that!

You can conect to remote servers by specifying any ip (“10.0.0.125) or server-alias (“myfancyserver.biz”) also with COMMUNITY.  But you will need to use direct connection.  To use tunnelling you must use ENTERPRISE.

However CSV import uses the (My)SQL command “LOAD LOCAL DATA INFILE ….”  For that reason csv import will only work on 'localhost'. 

Also 6.x ENTERPRISE has reorganised the TUNNEL tabl into two tabs: one for HTTP another for SSH (and a SSL option was further added)

[lc.cooek]

peterlaursen wrote on Jun 21 2007, 03:49 PM:

NO .. it is not like that!

You can conect to remote servers by specifying any ip (“10.0.0.125) or server-alias (“myfancyserver.biz”) also with COMMUNITY.  But you will need to use direct connection.  To use tunnelling you must use ENTERPRISE.

However CSV import uses the (My)SQL command “LOAD LOCAL DATA INFILE ….”  For that reason csv import will only work on 'localhost'. 

Also 6.x ENTERPRISE has reorganised the TUNNEL tabl into two tabs: one for HTTP another for SSH (and a SSL option was further added)

so sql yog community version can't help me then, back at square one.

[peterlaursen]

If you are connecting to a remote server (no matter the connection method) CSV-import of a file stored locally won't work, becuase LOAD LOCAL DATA INFILE will look for the file on the computer where the server is running.

[peterlaursen]

my mistake

MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Quote:

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full pathname to specify its exact location. If given as a relative pathname, the name is interpreted relative to the directory in which the client program was started.

But the point is that with HTTP tunneling LOCAL machine is the machine where the webserver/PHP is running, not your local machine.

[lc.cooek]

peterlaursen wrote on Jun 22 2007, 09:28 AM:

my mistake

MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

But the point is that with HTTP tunneling LOCAL machine is the machine where the webserver/PHP is running, not your local machine.

so what ai am understanding is that GUI's import function will not upload local files to the server because it uses the LOAD LOCAL DATA INFILE command.

Does SQL Yog Enterprise version provide no options through the GUI to append data from the local machine to the server??

[peterlaursen]

Yes.  Data sync does.

You can install a MySQL server locally import to this one and sync with a remote server.

[lc.cooek]

peterlaursen wrote on Jun 22 2007, 02:51 PM:

Yes.  Data sync does.

You can install a MySQL server locally import to this one and sync with a remote server.

Done. Thank you for your help an patience.

[Author]

Posts