Large Tables Save as Blank CSV Files in IE7

sm9

Hi there,

I am using DataTables 1.5.4 and the TableTools plugin to export CSV files, and this works great, thanks.

However, I've found that as the table has gotten bigger (more columns and more rows), it got to a point where pressing the Save as CSV was taking around 50 seconds to do anything, and then when I do save this CSV file, it is completely empty, 0 bytes. This happens in Internet Explorer 7 (which most of my clients seem to use) but works fine in Safari 4 for Mac (output file is 414kb).

Currently, it works okay with 40 columns and 100 rows in IE7, but it fails with 166 rows, so I've set a manual limit within my CMS for now. My web server is a VPS so I can change any settings if you think this is a server issue, but I thought I'd post here first.

Thanks for any advice,

Stephen

allan

Hi Stephen,

Wow - 40 columns! It appears that there is a limit in the ExternalInterface which TableTools uses as the JavaScript /Flash bridge - see my post here: http://datatables.net/forums/comments.php?DiscussionID=327#Item_46 .

The Flashkit post suggests that the limit is around 360K, although since it is undocumented, who knows what it actually is... It might also be different between versions and platforms... Either way, it you are hitting 414K then, that would suggest that this is the problem.

The way around it... Well - I'm not sure that there is one on the client-side to be honest( splitting the file into multiple parts perhaps?). If you are going to be using much large data sets and can't limit it to less that 360K, I would suggest that you might need to do some integration with a server-side process which will create the file as required and then fire it back to the user, without using flash.

Might also be worth asking around the Adobe folk to see if they might know what is going on with large data exchanges.

Regards,
Allan

sm9

Hi Allan,

I don't know much about Flash I'm afraid so not even sure I could string along a decent question to explain what I was asking, never mind anything else! :-)

Out of interest (please excuse my ignorance), how come Flash is needed to perform exports anyway? Can't this be done with JavaScript or Ajax or something?

Strange how the same page can export the full file in Safari on a Mac, but not in IE7 on Windows XP.

Thanks,

Stephen

allan

Hi Stephen,

I use flash here because it has the ability to save information to a file, entirely on the client-side. Javascript in the browser doesn't provide any kind of access to the file system, so that kind of thing simply isn't possible. Indeed it's only in Flash 10 that this has become possible in Flash.

The other option, as I mentioned, is to have a server-side process create the file and then send it to the browser with a MIME type which will prompt the browser to download and save the file. I didn't do it this way for TableTools as I wanted to keep everything on the client-side (no mucking around with PHP v C# v ASP etc).

Regards,
Allan

sm9

Hi Allan,

Thanks for explaining that. I don't have a clue with JavaScript, never mind PHP or the others! Though I can write a php echo statement and a phpinfo! :)

I've noticed a few things with the export facility and wondered if you knew workarounds for them at all?

1. I have some fields that are separated with commas, such as: address1, address2, address3, etc. I found that with commas, this meant the cells broke out of the cell for the CSV exports (I know what CSV stands for! :)). Is there anything I can surround the field with so this doesn't happen, but also, so that it doesn't appear in the export too? i.e. Surround the address fields with a speech mark or something, but it'd be ingored when exported to CSV and just keep all those comma separated fields within the speechmark within a single cell?

2. In order to get around the above, I have just added spaces instead of commas. However, I found that my spaces were stripped by the time this was exported?

3. In order to get around number 2, I added   instead of spaces. But when I export to CSV or Excel, they appear as   rather than a comma. A quick find/replace solved this, but it'd be great if this manual step wasn't needed.

Thanks for any help with this,

Stephen

allan

Hi Stephen,

If you have a look at the TableTools files included in the 1.5.4 DataTables download, you'll be able to see that there is actually already a CVS boundary option - which addresses the issue of commas. This is on by default, so you shouldn't need to do anything, but you can modify the boundary using 'sCsvBoundary' if you want. And hopefully that will take care of all three points :-)

Regards,
Allan

sm9

Hi Allan,

Thanks for this - I think I need to take the hint and try using this version of TableTools instead! :-)

Is there, by any chance, anything in that new version which would perform find/replace at all? I'm thinking of when you get strange symbols due to strange apostrophe's or bullet points from Microsoft Word etc.

Thanks,

Stephen

allan

Hi Stephen,

The version that comes with 1.5.4 doesn't do any kind of find/replace. There is an outstanding issue with & being converted to &, and possibly other HTML entities - but that's the only one I'm aware of. What you could do is modify the source to do a search and replace if that is what you want.

Regards,
Allan

sm9

Hi Allan,

I've managed to convert a lot of my strange symbols within the CMS itself before outputting the results to the page. Except that TableTools seems to convert my stripped-down ampersands to the & amp ; (space added to prevent html conversion on this page).

I assume this issue still isn't addressed so I'll see if I can find my way around some gobbledegook (aka JavaScript!) and edit the code.

Thanks,

Stephen

allan

Hi Stephen,

You are quite right - this is my one outstanding issue with TableTools at the moment, and I suspect I'll need to modify the swf to fix it - but haven't really had a chance to have a go at it yet. I'm slowly getting to it on my to do list though, and hoping yet for a JS solution... :-)

Regards,
Allan

sm9

Hi Allan,

I think you should prioritise your to-do list so that you look that the issues most important to me first! ;-)

Stephen

sm9

Hi Allan,

I've just upgraded from DataTables 1.5.4 and TableTools 1.0.2, to DataTables 1.6.0 and TableTools 1.1.0.

I don't seem to have my ampersands getting converted any more so thank you for that!

But one thing I have noticed is that in Safari 4 on a Mac this morning, before upgrading, I could export an entire table of data, which consisted of 9 columns, 1490 rows, and came to 217kb when exported as a XLS file (for interest, this would only export a blank file in IE7, even though it wasn't near the 360kb limit).

However, after upgrading just now, I've noticed that the same table only exports 434 rows of the same table, which produces a 135kb XLS file.

Any idea why this export limit seems to have reduced after upgrading? And if so, do you know of a way to overcome this at all?

Thanks,

Stephen

allan

Hi Stephen,

It's a soft limit (it seems to vary from browser to browse and version to version) - so that might have something to do with it, certainly there shouldn't be any reason why it would drop in size in TableTools! However, I thought I had actually worked around this problem - so that's slightly disappointing. Any chance you could give me a sample of your data which is causing the problem? ( datatables.net/contact would probably be best!).

My demo of Flash CS4 runs out in about 15 days, so need to get it sorted quickly :-)

Regards,
Allan

sm9

Hi Allan,

I've tested this a bit more and found that by simply rolling back just the TableTools.js file from version 1.1 to 1.0.2, I'm then able (in Safari on a Mac) to download the entire table as an Excel file - the biggest file I've tested has been 4.7mb (1,500 rows and 42 columns). Using 1.1, the Excel files always max out at 135kb.

The only disadvantage I have with version 1.0.2 is that I have to manually find/replace the ampersands in the Excel file, plus I'm using a hack to export bVisible false columns rather than the native version built in with 1.1.

I sent you a message via your contact form so please reply to me via email and let me know the best way to share information/testing for you.

Thanks,

Stephen

domingo

I had a similar issue with Firefox. The reason was that at least for FF nodeValue has a limit of 4k, hence when fnHtmlDecode(sData) is called, the result is trimmed. In TableTools.js line 643 I used
[code]return sData;
//instead of return fnHtmlDecode( sData );[/code]

to solve the problem... but then your ampersands will get converted again

Regards
domingo

allan

Hi domingo,

Awesome - thanks for the tip :-). I've just released TableTools v1.1.1 ( http://datatables.net/releases/TableTools.1.1.1.zip ) which addresses exactly this problem and a couple of other little things.

Regards,
Allan

domingo

Allan nice fix you've done...but seems that TableTools upgrades won't stop here :-). Another issue arises when using fnHtmlDecode, in Internet Explorer, the whole data in excel appears in one cell. Seems that fnHtmlDecode strips "\n" and "\t".

Regards,
Domingo

allan

URGH! Bummer... one more upgrade coming in the near future... Thanks for the tip. I should have tested that in IE in all fairness - I though "what could possibly go wrong". That will teach me.

Allan

allan

Let's try this one on for size: http://datatables.net/releases/TableTools.1.1.2.zip :-)

That should do the trick for large files, HTML entities and UTF-8 support in all browsers! But do let me know if you find anything else!

Regards,
Allan