Feature Req: Better Export To Excel

[vygi]

Hello here,

it's not a SQLyog issue, at least not directly…. but nevertheless….

If I copy&paste data into Excel (and even if I check “fill Excel friendly values”),

I permanently have diferent problems with wrong data type interpretion.

The worst thing are numbers (floats) being interpreted as dates: Excel thinks

that 9.07 is “9th of July 2005” and writes this date into the cells! After that, it's

no more possible to get the right value back because Excel shows a big number

(probaly number of seconds since some date) if I change cell type to “number”.

The only possibility is to change columnt type to text (in andvance), then paste

data from clipboard, and finally change column type to number.

Other problem (although not that critical) are char/varchar fields writen as

numbers but also in this case some data can be lost, eg. I have phone numbers

as varchar and “00498945566” becomes 498945566 in Excel (without “00”)

which is fatal.

So there is no proper way to export data to Excel (or to any similar application) 🙁

Interestingly, urSQL (http://www.urbanresearch.com/software/utils/urbsql/)

does not have such problems and all data – numbers, strings, dates, etc – is

being transferred to Excel *correctly*.

Therefore I'm asking you if there is a chance to add “direct” export to Excel

as urSQL haves. It would be big improvement for many business users.

All the best,

Vygi

[peterlaursen]

Quote:

The worst thing are numbers (floats) being interpreted as dates:

This is a known problem with MS-Excel. It is supposed to 'help' users. ANYTHINNG that Excel can interpret as a DATE-type will be.

The leading zero's being truncated is of course because the numerical '7' and '007' are identical. However the strings '7' and '007' are not.

I have experienced both often when opening some text-type file from Excel. Here however you have an option the bypass the automatic functions and specify the type for each column. But I don't think you have that option when using the clipboard.

I think the solution here is to install MS-Query. It is an optional part of the MS-Office suite (it does not get installed with a standard install so you probably don't have it – yet! – but you only need to restart the MS Office installation program and specify it). With MS-Query and MyODBC you won't need any clipboard or textfile as an intermediate between MySQL and Excel. You transfer directly from any ODBC-compliant database such as MySQL to Excel.

And BTW: you won't need SQLyog either 😛

You could also try OpenOffice2-Calc. It is far superior to MS-Excel im my opinion!

[vygi]

Yes I know that this is a known Excel problem.

Among other things, I'm also web developer and know that download to Excel can be a pain if you try to send just a tab separated values. It works… somehow… but these issues with text/number/date fields suck! Fortunately, there are several packages which allow to create real Excel files and specify data type for every singe cell.

ODBC is an option if you really know what you need and always do the same.

Otherwise you have to use some DB GUIs like SQLyog to put query results into files, and Excel is a common office application. Unfortunately, this described issue makes export to Excel problematic to quite impossible.

I'm not sure but urSQL seems to use direct interface (COM) for Excel exports and it works much better. I never had any problems with urSQL.

This issue is not a big problem to me but quite a show-stopper for some of my colleagues at work. Otherwise – who knows – maybe we would purchase a couple of licenses. But now they continue with urSQL….

[dany]

Try this:

1) Open Excel and select all the columns you will fill

2) Go to the “Format” menu

3) Select “Cells”

4) In the “number” page select “text”

5) Now you can do copy and paste.

Bye

[Ritesh]

Did this method work? If it does, then its an interesting entry in our FAQ system.

Anyway, I will take a look in urSQL and see how they are able to get things straight without any problem.

[vygi]

Ritesh wrote on Nov 29 2005, 09:16 AM:

Did this method work? If it does, then its an interesting entry in our FAQ system.

It works… somehow….

But it is only a workaround. Quite good one if you only need to store date but worse if some calculations must be done.

Example:

let's format cells as text and paste some data from MySQL, let's say just date in column A and two floats in columns B and C.

Now let's try to calculate sum of B and C and put it into D.

That means, I have to write a formula “=B1+C1” into cell D1.

It works only if column D was not indicated as text column.

So I must either know in advance how many columns will be inserted (and only these must be formatted as text) or revert theom to general or number format after copy&paste. This is a 1st issue.

No big deal so far, I can easily change it to “general”.

But now the worse thing: as soon as I edit this formula, the cell becomes text again, and the formula don't work anymore. So I have to select also both columns B and C and format them as numbers in order to use them in some arithmetical formulas. This is the 2nd issue.

It's not possible to define number column as number in advance (before copy&paste) because Excel then puts “38600.00” instead of “5.09”! So number fields *must* be indicated as text before paste and *must* be switched to number format after.

So you see it is not that easy and this workaround isn't good enough.

Ritesh wrote on Nov 29 2005, 09:16 AM:

Anyway, I will take a look in urSQL and see how they are able to get things straight without any problem.

[post=”8015″]<{POST_SNAPBACK}>[/post]

Thanks, Ritesh!

I don't care much about it but I see that data transfer to Excel is a common practice, and this cell format problem must be quite important issue for many office users.

BTW especially European users are affected as we use periods to format date (today is 29.11.2005 or just 29.11). Americans may have less problems with it (11/29/05).

Thanks in advance,

Vygi

[peterlaursen]

Quote:

s we use periods to format date (today is 29.11.2005 or just 29.11). Americans may have less problems with it (11/29/05).

I think all Excel localization cause equally much trouble is this respect! The code of the program is the same. It is only display that differs.

I have experienced quite often that the playing time of a .mp3 – say 3:09 – was converted to a date like thisyear-sept-3rd or thisyear-march-9th (I don't remember right now).

Basically I think the problem is three:

1) SQLyog does not keep track of TYPE of data displayed in DATA or RESULT pane. It is all treated as strings when in the grid views.

2) Windows clipboard does not either – or how does it?

3) Even if data types were 'kept track of' some way you would still have problems with incompatible data types between MySQL and Excel. The FLOAT type is a very good example of this (using FLOAT internally takes some very specialized mathematics and it is represented internally very differently compared to a REAL or DOUBLE). I don't think Excel supports it natively. Actually I think that this

Quote:

Excel then puts “38600.00” instead of “5.09”!

is a FLOAT conversion (or rather lack of same!) issue. ODBC and JDBC handles type conversions to some degree – clipboard does not!

But as far as 1) goes I think that should be fixed. Actually I believe it will have to be to solve old issues with column defaults such as NULL, CURRENT_TIMESTAMP, SESSION_USER etc. Actually here http://www.webyog.com/faq/5_20_en.html I wrote

Quote:

When saving data from the grid view of the DATA or RESULT -panes the grid shall be parsed for NULL, type, functions and (predefined) variables such as (to mention the most important one) CURRENT_TIMESTAMP.

and no one contradicted that yet! If a more 'smart' way of handling Excel could be done alongside it is of course OK with me too.

But to me the database issues (overwriting of defaults) are far more important to me than any M$ issue.

[vygi]

peterlaursen wrote on Nov 29 2005, 12:55 PM:

But to me the database issues (overwriting of defaults) are far more important to me than any M$ issue.

Yes, I agree, it is not a first priority to me as well, but my three workmates have tried SQLyog (after using urSQL) and two of them got these type conversion problems within first 30 minutes; they were disappointed, aren't excited by SQLyog anymore and prefer to stay with urSQL, although it is not MySQL5-compatible and therefore probably will cause problems in the near future. So are the facts…

I am quite sure that urSQL uses Windows COM interface to write “directly” to Excel. It allows to avoid any type interpretation errors and is faster as makes everything in one step, without clipboard or (temporary) files.

Hope it helps: http://www.codeproject.com/com/#Automation

BTW even PHP on Windows has this COM interface, so I very hope it is not too complicated to implement.

Best,

Vygi

[peterlaursen]

Well .. this is a matter of priority basically.

Since I completely stopped using MS-Office it is not my priority <_< . I would rather request OpenDocument (XML) -compliance then! But still I don't think it will solve everything. Data in the SQLyog RESULT and DATA panes are without TYPE I believe. So the data will need to be re-read from MySQL. SQLyog is only able to send strings from RESULT and DATA panes as of now.

[vygi]

peterlaursen wrote on Nov 29 2005, 03:14 PM:

Well .. this is a matter of priority basically.

Yes, I agree,

but if office user like my teammates are SQLyog target customers then decent export to Excel should become not the very lowest priority.

[vygi]

Just FYI: another colleague at work came to me and said that “this your new database tool just looks nice but works shitty because it's impossible to put data into Excel”. This time it was my superior, and now I'm sure that we will get no licenses…

[Ritesh]

vygi wrote on Dec 2 2005, 10:47 AM:

Just FYI: another colleague at work came to me and said that “this your new database tool just looks nice but works shitty because it's impossible to put data into Excel”. This time it was my superior, and now I'm sure that we will get no licenses…

[post=”8069″]<{POST_SNAPBACK}>[/post]

I think this needs to be solved in priority basis for v5.01.

Damn, a release never sees the door due to never-ending crucial features!

[Ritesh]

@vygi: Can you send me some sample data to reproduce the problem?

[vygi]

Ritesh wrote on Dec 2 2005, 02:15 PM:

@vygi: Can you send me some sample data to reproduce the problem?

[post=”8073″]<{POST_SNAPBACK}>[/post]

This is not a bug of SQLyog, this is a general problem with Excel, and to improve it could be not easy, I guess… So please release 5.01 final or public beta soon, and this Excel problem can wait.

From all DB tools I know only urSQL is working quite perfectly. And MySQL Query Browser is able to create MS Excel XP files as XML.

Maybe this feature is not needed by most of the users but, unfurtunately, it is heavily used in our office.

To repoduce this issue, create a simple table, eg.

CREATE TABLE xtest (

s varchar(255),

f float(14,2)

)

and put this data in the table:

abcabc 88.99

000123 10.09

Then execute SELECT * FROM xtest and copy all rows to clipboard (wit excel friendly option or without).

Now open Excel and paste values. I've got:

abcabc 88.99

123 10. Sep

String '000123' has been converted to number 123 and number 10.09 to 10th of September (German date format: dd.mm[.yy]).

That's it 🙂

Let me know if you need any additional information.

Regards,

Vygi

[peterlaursen]

The crazy thing is that I don't get the same (Excel 2000. Danish) as vygi does.

string '000123' is converted to number 123 but number 10.09 is not converted to a date. But M$ might have 'improved' upon this with later versions 😀

The most annoying thing (for me) is that with OpenOffice Calc (latest developer snapshot) I get exactly what vygi get with his Excel. Grrrr …. 🙁

There is a detail more: In German and Danish decimal number are written “10,09” not “10.09”. This is implemented in localised Excel-version that way. Vygi: are you using a German Excel or an English Excel and have localised OS-settings only?

I also believe that it is a further complication, that neither 88.99 nor 10.09 really are numbers in neither German nor Danish! Actually the cells can't be formatted here at all once they are pasted into Excel! Because they are not valid data at all in European localized versions of Excel. formula.jpg illustrates this: you can't add the numbers with a formula when using “.” as decimal seperator. you can when using “,” !!! Nice observation … 😀

I don't think there is any fix possible as long as copy from clipboard is used. Actually any transfer method using glyphs will fail! The decimal 'dot' must be represented by a symbolic 'decimal dot' (and probably that is what COM does) and no glyph and probably no character number of a charset either! The OpenOfice people have struggled with this locale issue (meaning of “.” and “,” in decimal numbers) for more than a year I know – it was a problem with macros in particular. And it almost split the project: Sun wanted one solution (most possible backwards compability with old StarOffice versions) most of the volunteering participants wanted most possible compability with MS Office.

So this is not an easy fix!

[Ritesh]

Looks like it will require lot of research. Nevertheless, we will be releasing v5.01 BETA for the public today.

[vygi]

Happy holidays averybody! 🙂

Any news re data export to Excel?…..

[peterlaursen]

@vygi

I can inform you that at the Webyog Office in Bangalore everything is practically closed down from now till after New Year.

So even they have (hopefully) a happy holiday!

[vygi]

peterlaursen wrote on Dec 28 2005, 02:03 PM:

I can inform you that at the Webyog Office in Bangalore everything is practically closed down from now till after New Year.

Now I still hope that export to Excel via COM will be implemented one day.

I'm sure that our company would purchase 5-licence-pack or so…

[peterlaursen]

Maybe:

http://www.codeproject.com/macro/easyif.asp

There are similar code pieces to be found on the internet!

[Ritesh]

I have pushed this feature as priority for v5.2.

[peterlaursen]

I would then request similar attention to OpenOffice/OpenDocument-format.

But that maybe rather is a question of improved XML-support in SQLyog as a general matter.

[peterlaursen]

now you said it – so now it is here:

http://www.webyog.com/faq/33_20_en.html

[vygi]

vygi wrote on Jan 23 2006, 01:20 PM:

Now I still hope that export to Excel via COM will be implemented one day.

I'm sure that our company would purchase 5-licence-pack or so…

Hey here,

now I hope that you didn't forget export to Excel (and other applications) via COM (or whatever…) and one day it will be implemented.

We also have an issue with the float numbers, especially with FLOAT (10,3) as they have three digits after point. MySQL delivers “3” (three) like “3.000” and Excel interprets it as 3000 (three thousand) because of German region settings. This makes things even more difficult. We even had a bad error in our financial reporting because of this!

Vygi

P.S. but we've got a 5-lic.-pack in hope that this issue some time will be fixed

[peterlaursen]

Code:

We also have an issue with the float numbers, especially with FLOAT (10,3) as they have three digits after point. MySQL delivers “3” (three) like “3.000” and Excel interprets it as 3000 (three thousand) because of German region settings. This makes things even more difficult. We even had a bad error in our financial reporting because of this!

@vygi: did this involve SQLyog?

to workaround the european regionals use string function replace(), ie.

Code:

select replace(variable,'.',',') as variable_with_comma from table; // replace '.' with ','

with the query.

[peterlaursen]

Another workaround is to activate the 'language settings' toolbar from Windows 'process bar' (or whatever it is called). Now when you open Excel, higlight the Excel window, and switch the locales for just that Excel window to English. Works great with Turkish, Czech and Thai, so why not even German? 😀

Then no special SQL is needed.

looks like

[vygi]

peterlaursen wrote on May 3 2006, 09:31 AM:

Another workaround is ….

yes thanks, maybe it ever works for numbers, but remember that there are other problems withour workarounds, eg. wrongly interpreted dates or number as dates in excel (10.05 -> 10th of May 2006) or missing leading zeroes (string '00491634718109' -> number 491634718109) and so on… This makes SQLyog hardly usable, so let's hope one day we will get a real solution.

Regards,

Vygi

[peterlaursen]

🙂

basically Excel is to blame I think! But it will take a few weeks before I'll buy Micorsoft, so we'd better find a solution in between.

Now can't you set the type for each column (avoid auto) when importing CSV?

[vygi]

peterlaursen wrote on May 3 2006, 09:55 AM:

🙂

basically Excel is to blame I think! But it will take a few weeks before I'll buy Micorsoft, so we'd better find a solution in between.

Now can't you set the type for each column (avoid auto) when importing CSV?

no, it doesn't solve all issues as in some cases Excel still overwrites their own settings and still interpretes some values like it wants.

[vygi]

I am sorry to say that your “Plans for future versions of SQLyog” (http://webyog.com/faq/33_20_en.html) aren't up to date anymore:

Next release adding major new functionalities will be named Version 5.2. It is planned for release by the end of first quarter 2006/beginning of second quarter 2006.

[…]

With version 5.2 we also plan to implement some other requests, such as Better interoperability with spreadsheet programs – Microsoft Excel in particular

But I still hope that export to Excel will be not forgotten!

Our company has bought 5-License-Pack of SQLyog but my workmates must still use UrbanSQL because of this issue.

I clearly understand that it's not really a SQLyog problem

but in this case I recommend you to remove or rename “Fill Excel friendly values” option as it makes the process not much more friendly and is just confusing.

[peterlaursen]

@vygi

I know that this FAQ (actually there are a handful) most be updated. Coming soon.

Detailed planning going on …

[Ritesh]

Even the help files are not updated as of now.

We are still not decided what will be in v5.14 and what will not be.

Give us 2-3 more weeks and things will be more clear.

[vygi]

Ritesh wrote on May 23 2006, 01:27 PM:

Give us 2-3 more weeks and things will be more clear.

I can give you even 2-3 months! 🙂

P.S. congratulations to your Open Source decision!

[vygi]

Hey Friends,

now, are there any news on this topic?

Maybe our company has very special and unusual needs but we really need this feature!

Currently, some of us are using Urban SQL (urSQL) client to export data to Excel. It is not really compatible with MySQL 5 and sometimes creates wrong data type in Excel (text instead of numbers) but still works better than SQLyog.

Unfortunately, I am not a Windows/C++ programmer (just web/php) and unable to implement data export to MS applications via COM (or whatever) in SQLyog, but I am sure that such possibility would be very useful for some customers.

Thanks in advance!

Vygi

[Rohit]

We will dedicate one developer full time on this starting from Monday.

We are not going to use COM objects. We will use the Excel XML format to export data.

[vygi]

Rohit wrote on Sep 12 2006, 02:02 PM:

We will dedicate one developer full time on this starting from Monday.

We are not going to use COM objects. We will use the Excel XML format to export data.

🙁

Unfortunately, many corporate customers – probably even most of them – are still using older Excel versions.

I.e. my prevouos company was still using Office 97 (!) and my current company uses mix of Office 2000 and Office XP but I have Office/Excel 2000 which does not support XML-based Excel format.

Nevertheless, this would be a welcomed update and a reason to upgrade my Office to 2003 or at least XP version!

Best regards,

Vygi

[Author]

Posts