Ver 3.52 Import CSV problem

[darrylw]

Hello,

I found out about SQLyog by purchasing a learning book on MySQL/PHP that included

Ver 3.11.

I assume that I am OK to use this without extra payment.

I have run into a problem importing csv files in both version 3.11 and 3.52 as I had downloaded

and installed the newer version to see if the problem went away.

The first statement is that which SQLyog created from the interface

while the second one is that which I changed to include the ` character for the

desired fields.

The first one gives a 1064 error while the second one works OK when run as a direct SQL statement.

load data local infile 'c:/mysqlimport.csv' into table `import`.`import1` fields terminated by ',' lines terminated by 'n' ( index, name, email, phone, job );

load data local infile 'c:/mysqlimport.csv' into table `import`.`import1` fields terminated by ',' lines terminated by 'n' ( `index`, `name`, `email`, `phone`, `job` );

My second problem has to do with the line terminator. I changed the file format from DOS to UNIX and the /n works OK but I do not seem to be able to handle the /r /n that DOS uses

when the file is simply saved on a Windows machine as a standard csv file. (Excel for example)

Can anyone indicate to me how best to handle standard DOS/Win Text files?

[Ritesh]

Quote:

load data local infile 'c:/mysqlimport.csv' into table `import`.`import1` fields terminated by ',' lines terminated by 'n' ( `index`, `name`, `email`, `phone`, `job` );

Thanks. We will work on it and fix the error ( if there are any ) in SQLyog 3.6.

Quote:

but I do not seem to be able to handle the /r /n that DOS uses

Use rn and /r/n

BTW, which book about MySQL/PHP did you buy?

HTH

[darrylw]

Thanks for the speedy reply.

I also tried adding rn as line terminators in the interface but that did not seem to work

at the time.

Should these show in SQL as something like `r n` or `r` `n` or something that combines them like (`r`, `n`)

The book is PHP/MySQL Programming by ANDY HARRIS from Premier Press. The Acknowledgements included the webyog development team. ISBN 1-931841-32-2

[Ritesh]

It will be

Quote:

lines terminated by 'rn'

Can you attach a screenshot of the dialog box wherein you are giving the Escape characters.

Also, will it be possible for you attach the .CSV file so that we can look into it and tell you the exact Escaping characters….

[darrylw]

Once again I find myself a bit confused……

I decided to create a new table to import a csv file that was left in DOS format. The import file

was the same one that I had problems with before.

I entered (rn), not in parenthesis in the line termination and (,) as the field delimiter as I had done before.

This time the file imported with no complaints other than missing some of the records.

I emptied the table and re-ran the routine and it worked again.

So I then did it again but changed the table to not have any primary key or non-null conditions.

This time it imported all of the records.

I must admit that I have no idea why the pgm seemed to remove the ` from the field indicators

when I was trying to import the file originally.

Perhaps I should now simply not worry and be happy.

Thanks for your help…..maybe I found a bug but maybe I created one in my environment somehow.

P.S. Should I remove version 3.52 and go back to 3.11 that did not complain about registering?

[robbiebow]

Hello,

Thanks for this tool – it's been very useful!

I experience exactly the same errors using v4.04 Free Edition. For example, I can export in CSV format using SQLyog, then try import the same CSV and get an import error. Looking at the history, the fields escaped by is not escaped properly, i.e.

/*[13:35:18][ 0 ms]*/ load data local infile 'C:/Documents and Settings/robbieb/My Documents/test.csv' into table `spiracmdb`.`aieshotaudit` fields escaped by '' terminated by ',' optionally enclosed by '”' lines terminated by 'n' ( `id`, `co_id`, `hit_dt`, `eShot_id`, `status`, `cust_id`, `cust_email` )

If I take that SQL and change '' to '\' it then works. Has this bug been fixed?

[Ritesh]

Just enter \ in SQLyog's CSV Export/Import Escape Character window.

[Author]

Posts