Export To File – Excel Format

[rj55]

Selecting settings for export in Excel friendly format still shows Escaped By \, but when doing the export it only uses one and the settings shown after this only has the one .

This is with SQLyog Community v8.53, worked correctly in earlier versions.

[Khushboo]

Hi,

We are not able to replicate your issue at our end. We checked in 8.53 and 8.4, they both have the same behavior.

Quote:

This is with SQLyog Community v8.53, worked correctly in earlier versions.

Please tell us which version you tried previously?

Thank You.

Regards,

Khushboo

[rj55]

Sorry, I am not sure what version I used before but I have not used SQLyog very long. Just realized that it could also have been that I just haven't exported any data where escaping was needed until after the update, but that is highly unlikely.

Anyway, main issue is that it doesn't work correctly in the current version and I have to use MySQL Query Browser instead if I need to export data.

[peterlaursen]

This is by design actually.  And there has basically been no change since 5.23.  But I apologize that release notes from early 5.x are not very detailed – and unclear actually.

 

Now let me explain: SQLyog is a GUI tool.  A such it is supposed handle every 'weirdity' of SQL-syntax transparently for user. If for instance you want to insert the string “backslash” from the DATA grid you just write that – unlike in the/any editor where you will need to quote and escape (ie. write “'backs\ash'” – with single quotes and escape the ).  In early SQLyog versions the CSV-export dialog was an exception – here user was supposed to handle escapes himslef. if he wanted “” as the escape-character, he would need to enter “\”.  This was fixed in 5.13 (as far as I can track down). With this release also the CSV-export dialogue escaped transparently and user only needed ot enter “”.

Next problem was that users familiar with the old interface (and with SELECT INTO OUTFILE and LOAD DATA syntax) got confused. What they used to do did not work anymore. So in 5.23 we made an 'exception' as regards the CSV-export dialog: here escaping is optional (basically escape characters are ignored).  So both “\” and “” mean “”.

Do I understand correctly that you want to escape with (litterally) “\”?  In that case you would need to enter “\\”  – what is not possible currently as the input field takes only 2 characters.  This may be (I think it is) a 'regression' after 5.23. 

[rj55]

'peterlaursen' wrote on '26:

This is by design actually.  And there has basically been no change since 5.23.  But I apologize that release notes from early 5.x are not very detailed – and unclear actually.

 

Now let me explain: SQLyog is a GUI tool.  A such it is supposed handle every 'weirdity' of SQL-syntax transparently for user. If for instance you want to insert the string “backslash” from the DATA grid you just write that – unlike in the/any editor where you will need to quote and escape (ie. write “'backs\ash'” – with single quotes and escape the ).  In early SQLyog versions the CSV-export dialog was an exception – here user was supposed to handle escapes himslef. if he wanted “” as the escape-character, he would need to enter “\”.  This was fixed in 5.13 (as far as I can track down). With this release also the CSV-export dialogue escaped transparently and user only needed ot enter “”.

Next problem was that users familiar with the old interface (and with SELECT INTO OUTFILE and LOAD DATA syntax) got confused. What they used to do did not work anymore. So in 5.23 we made an 'exception' as regards the CSV-export dialog: here escaping is optional (basically escape characters are ignored).  So both “\” and “” mean “”.

Do I understand correctly that you want to escape with (litterally) “\”?  In that case you would need to enter “\\”  – what is not possible currently as the input field takes only 2 characters.  This may be (I think it is) a 'regression' after 5.23. 

Well, only thing I really want is that selecting “Excel Friendly” format should result in a file that is compatible with Excel. This is not the case now, but the easy work around is to just use MySQL Query Browser instead when I need to export data to Excel.

[peterlaursen]

Please explain what is not compatible with Excel. It works fine for me with Excel 2010. I select a table for CSV-export, select 'fill Excel-friendly values' and export.  It opens perfectly in Excel (when configured to English locale).

However you should this: http://www.webyog.com/faq/28_73_en.html

“And in most of continental Europe (where the comma is used for decimal notation – ie: “4.123,7” instead of English notation “4,123.7”) it is a de facto convention to use semicolon and not comma as field separator! Localized versions of Microsoft Excel and the Microsoft text-ODBC-driver handles it that way for instance!”

SQLyog 'assumes' English-style locale when defining 'Excel-friendly values' (what I as a continental European have complained about to my colleages for 5+ years – but they don't realize that sometimes computers do not speak English – sigh!  But this is not related to the escape character)

 

[rj55]

I am in the US and use Excel 2007, currently on Windows 7 Ultimate 64-bit, but used Windows Vista 64-bit before with same results.

[peterlaursen]

And what are the (wrong) results.  Please do not make assumptions about the reasons but just explain

1) what you do

2) what you get

3) what you expect (but don't get).

[Author]

Posts