Export Data To Excel Xml Trouble

[PerFredlund]

Hi 🙂

I am having trouble exporting the content of a table to an Excel friendly format.

When i select to export i choose to export as Excel XML. I select all columns (default) and set tex column width 2500 and 2 decimal places.

I take the column width to mean the maximum size of text that a cell in the Excel book can contain. The export then proceeds fine, but one column does not show the data. Instead it just shows the datatype (Image/Binary). One other column is also of this type but correctly shows the data inside it when i open it in Excel (Excel 2003). I have included the full definition of the table (abstracts). The column with the problem is called *title*. I also include the exported file.

I hope this problem is reproducible and that the issue can be fixed. In my search of other export tools i have had to resort to phpMyAdmin. It exports correctly for me, but i have to do it many times starting from different rows, as it can only out output about 70/80 rows (tuples) at a time. This is annoying me quite a lot. And yes i can create my own php script, but what is a nice GUI for ?? 😀

Kind regards,

Per K Fredlund

(edit: I am using the SQLyog 6.12 community edition …)

[peterlaursen]

Could you please provide a small table dump with data where this export fails like that?

If ther is a '' sequence in the data they will be considered binary data!

[PerFredlund]

Hi 🙂

I have included the exported data.

Just a quick comment. SQLyog is not losing focus during the export process 🙂

Please remove the .xml additional extension <_< Gosh, too few filetypes are allowed for upload, i needed 6 attempts!! 🙁 Best wishes, – Per

[peterlaursen]

Except for compressed image files (jpg, gif, png) and very small .txt's we request that you zip attachments.

There are two reasons for the limtation:

1) protect users against downloading and accidentially executing files with any kind of active content

2) save disk space on the server

I do not understand 'Please remove the .xml additional extension' .. from where?

we will return the 'original matter' soon,

[PerFredlund]

peterlaursen wrote on Nov 14 2007, 10:14 AM:

Except for compressed image files (jpg, gif, png) and very small .txt's we request that you zip attachments.

There are two reasons for the limtation:

1) protect users against downloading files wit hany kind of active content

2) save dis space on the server

I do not understand 'Please remove the .xml additional extension' .. from where?

we will return the 'original matter' soon,

I would have thought that using an unknown extension would be fine too, like .per (my first name). I also tried .rar archive, but it didnt like it.

I mean to remove the .xml extension from the uploaded .sql file. Ok i will just zip it not rar it or give other non-runnable names.

Back to the problem, the *title* field contains solely plain text, user supplied through some web page. Why would it be treated as binary? That beats me … hmm …

I attached the sql dump as a zip archive 🙂

-Per K Fredlund

[peterlaursen]

Your dump imported creating 515 rows here.

In your first post you write:

“Instead it just shows the datatype (Image/Binary).”

Is this in the SQLyog DATA tab it does so. It does not for me – neither in the `title` or `body` column. Please explain?

BTW: There are some encoding issues with some data. Example like

after import this

Quote:

“Now that you mention it doctor…”- Symptom reporting and the need for systematic questioning

becomes

Quote:

“Now that you mention it doctor…�- Symptom reporting and the need for systematic questioning

Edited: Adding “SET NAMES UTF8;” to your dump corrected that!

I think it is a bug with this phpMyAdmin version that it encodes as utf8 (and even inserts utf8 BOM (Byte order Mark) at the beginning) but does not SET NAMES accordingly. Please use SQLyog (or 'mysqldump') for dumping next time you want to post here (unless there is a very special reason not to!).

[peterlaursen]

Now I see the problem!

After reading the exported file into Excel, most rows in the `title`column displays 'binary/image'. We will look into this on Monday.

Now .. if you want to export those data to Excel you can export as 'CSV' instead of 'EXCEL XML'. Select this option, click the 'change' button .. 'fill Excel friendly values' and change the column delimiter from “,” (comma) to “;” (semicolon) if you are running a localized Continental European version of MS-Office.

For the comma/semicolon issue refer to:

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!”

[peterlaursen]

Please verify the fix for this issue with this build:

ENTERPRISE link:

http://www.webyog.com/downloads/betas/not_…QLyog614Ent.exe

COMMUNITY link:

http://www.webyog.com/downloads/betas/not_…d/SQLyog614.exe

[PerFredlund]

Hi 🙂

I have been away from the forum for a while. Can you (Peter) please reactivate the link again, as it is no longer working. I want to try out the fix 😉

Best wishes,

Per

[peterlaursen]

You can just update to 6.14 FINAL.

release announcement:

http://www.webyog.com/forums//index.php?showtopic=3648

[Author]

Posts