Excel export broken

The "export all books as excel" happily reports that it has processed all my 7003 books and then it just keeps spinning the wheel. So it never gives me a link to download the export file. (Or at least it doesn't within an hour, which in my mind is the same as "never" :-)

7,003 books processed

The tsv export works fine.

(Reloading the page gives me a page where the "Export all books" button is disabled. I.e. I can't try to start from scratch. But maybe this is another bug?)

Yeah, I tested this out with a larger account, and it froze at 8,500 records for me. I kept it up for at least an hour (probably more), so something's getting stuck here. Not sure when Tim will be able to look at this, but hopefully soon! I'll see if I can do any other testing on my end today that might help.

It worked fine for me and my ~8,500 books before the server breakdown so it might well be because of that.

>3 anglemark: Ah, you confess that this is all your fault :-)

(Just joking to pass the time until the excel export is fixed.)

Maybe we need a spin doctor to fix this? (Sorry)

>4 bnielsen: LOL!

Just adding I'm getting the same thing with Excel export. Got to 7224 books and never did anything else for 45 minutes. Reload also yielded a page with a non-function Export button.

Selecting tab-delimited worked fine and was about 5 times faster.

Also wanted to report mine is getting stuck at 4,601 books. I use the export at a weekly basis and its been down for two weeks now. Thanks for your help!

We're a bit of a skeleton staff today, so Tim won't be able to look at Excel export until next week, likely. Please use the export as .tsv (tab-delimited text) option in the meantime! Thanks for your patience, all.

Okay. Just wanted to report that I have only 242 books here and the Excel export won't work for me. Yesterday or today. Thanks.

Didn't we see this same bug symptom back when Export first added the Excel option? I remember getting hung up on large files then. Might not be the exact same problem, but could be related.

I have 126 in a collection that won't export. Text delimited won't work either. I am DESPERATE - have to get this working before my vacation this week!

>11 BLLC: "won't work either" is not a good description. TSV works for me (7003 books), so I'm surprised if it doesn't work for you.

The "eternal spinning" bug with the excel export is well described (and hopefully soon to be fixed by Tim).

Looking into some of these.

Yeah, something is dying on the production servers--the real servers--that isn't dying on the test servers. In as much as the production servers changed last week when we had our bad downtime, I need our sysadmin's help figuring out where to look, etc.

So, sorry, still working on it.

>14 timspalding: Now, there's an easy fix. Just swap the test servers and the real servers :-)

All 5220 of my books have exported, but the little arrows keep spinning. No link to access my exported library. Just thought I'd let you know it still is broken. Thank you so much for offering this. I'm sure this feature is quite complicated, and that you will figure this problem out.
(Umm . . . I'm not being sarcastic - upon reading this, I see how one could be misled.)

Yes, It's still broken. I'd love some information about when it will be fixed,a nd would like it fixed. Thanks.

>17 Lisa2013:

This thread is the appropriate place to watch for information on when it has been fixed. Usually they don't give estimates for when something will be fixed, unless it's of the form "Not until after ". Tim's said quite recently that he's looking into it, which means this thread has been noticed and not specifically stated as being deferred; that's the best-case scenario.

Ok. Thanks, lorax, for that information.

My export stalls at 723 books, so it's not just about large libraries.

My Excel export attempts repeatedly stall. Only 500 books to export; "processed" quickly, then nothing. Have tried Win 7, Win 10, Mac, and various browsers, various days; all with same problem. Highly disappointing, since export worked fine before the "improvement" of adding Excel direct export.

>21 Lasitajs: Highly disappointing, since export worked fine before the "improvement" of adding Excel direct export.

1. You didn't have Excel export before and now you do but it's not working, but you can't say that you're worst off than before.
2. The Excel export was working fine until there one of their front-end servers crashed a few days after it was introduced. They've since recovered most functionality but they seem to be having a problem getting this one back.

We're working on it.

(It may be fixed. We're testing.)

1. You didn't have Excel export before and now you do but it's not working, but you can't say that you're worst off than before.

Well, we had a fake Excel one--it was TSV, really, and it was broken in a different way that affected a minority.

TSV is not a "fake Excel export", it's a very useful export openable by a large number of methods including spreadsheet programs. Please do not drop the perfectly good TSV export just because you now have an Excel export.

>24 lorax: Don't worry, TSV export isn't going anywhere any time soon.

>25 lorannen:

I'd have worried a lot less without that "anytime soon" on the end of that sentence, but thank you. These days I'm not taking anything for granted.

TSV is not a "fake Excel export", it's a very useful export openable by a large number of methods including spreadsheet programs. Please do not drop the perfectly good TSV export just because you now have an Excel export.

I am referring to how we forced initial characters onto the start of the CSV and set the header so that it would fool Excel into opening. It was not a fully functional CSV export, however. It broke when there were unclosed quotes anywhere in any field.

Yes, it did just work for me. Thanks very much.

>28 Lisa2013: Glad to hear it, but we're still tweaking the fix. I wouldn't recommend everyone start re-attempting their exports until we've reported back that it is definitely fixed.

Yeah, it's still dying in the high thousands. But it works below that.

Still working it.

Could't make it work yesterday, but now it went pretty smooth! Very impressive feature. Amazing work, guys!

lorannen, Okay, Thanks. I'll wait until I hear. I have so few books it might not be a stress on the system though.

I'm really grateful that I was able to use this during the first days when it worked. It was a real help in Oxford on Sunday.

>27 timspalding: unclosed quotes as in a 15" deep hole in the ground. :-)

Now working for 6760 books. Fantastic! Thanks.

I just dumped all my data both as .xls and .tsv with about 7260 entries and both look fine.

>36 ulmannc:

If it works, it works. The stall-out-and-die seems to be in the 7,000s, but quantity of reviews and so forth affect it.

Basically, the create-Excel stuff is taking up HUGE amounts of memory for large libraries, and, while we could increase it to cover every library, it would be like handing 50% or more of the power of our main servers to a user for five minutes, with everyone else fighting for the rest. So we're looking at other options.

>37 timspalding: For what it's worth, I have very few reviews. . . well under 200. Saves you a lookup!

>37 timspalding: Not sure what you are doing, but I'm running open office calc in filter mode. Just tried it for real on my exported tsv file:

$ cat lt.tsv | tr -d \" | sed -e 's/.*/"&"/g' | sed -e 's/\t/"\t"/g' > tim.tsv
$ localc --infilter=CSV:9,34,UTF8 --convert-to xls tim.tsv

seems to work like a charm for me. You'll need to look up some of the weird parameters to make it do exactly what you want but the documentation is actually quite good. And they like backwards compatibility which is why some of the input filters have weird names like (StarCalc).
9 = TAB, 34 = double quote

(And I cheated to stay out of trouble with double quotes. I just deleted them from my input file and then "quoted"TAB"each"TAB"field"...)

$ ls -la tim*
-rw-rw-r-- 1 bnielsen bnielsen 14575632 aug 30 23:04 tim.tsv
-rw-rw-r-- 1 bnielsen bnielsen 15077888 aug 30 23:04 tim.xls
$

This took like 20 seconds on my home computer.

Ah, and my export as xls seems to work again. Thanks. I'll go look at it with a microscope :-)

Oh, that's really not good:
$ ls -la lt1.xls
-rw-rw-r-- 1 bnielsen bnielsen 28127232 aug 30 23:10 lt1.xls
$
The file is way too small. Looking at it in localc shows that things go south rather quickly. All books have the Book Id column, but title is missing from row 1223 and down except for a very few ones. The same goes for most of the columns with useful information.

row 1240 is about book id: 22213315 and I get sort key (1), role: Author; Year: 1989, hardcover, page count and a bit more, but anything interesting is MIA.

So the xls export is definitely not working for me right now.

Bump. Also happening for @t.sebesta w/ 2,329 records (http://www.librarything.com/topic/229053#6062440).

>40 kristilabrie: I believe this should be improved as far as stall outs on large numbers of records, as noted in the related Talk post: http://www.librarything.com/topic/229053#6062440) by amending the code to allow longer process times. I was just able to export 4k books to Excel format.

I just exported my 8,809 books to Excel and it went fine.

>42 anglemark: Excellent! Marking as fixed.

>42 anglemark: Are you sure that your books exported fine? When I try to do an Excel export, everything _seems_ fine, but when I open the file most of the information in last half of the file is just missing, i.e. the broken mess I described in >39 bnielsen:.

Ah, >43 lorannen: marked it as fixed. Sorry, no! (Reopening.)

I just exported 7,286 to Excel and data seems complete all the way down.

>44 bnielsen: Sorry, I'm afraid I don't understand much of >39 bnielsen: . Are you exporting your full library, or filtering by collection, or anything? I'll try running an export of your library and see what happens.

>46 lorannen: I'm exporting the whole library as Excel. I'm using Open Office rather than Excel to open the file though. If nobody else has the problem, we should just mark it as fixed. And I can reopen the bug report, if I can find some better evidence.

Summa summarum: I shouldn't have reopened the bug report without better evidence. And thanks to >42 anglemark: and >45 ChrisRiesbeck: for checking!

>47 bnielsen: i'll try your library and see what I get, it's entirely possible there are encoding or data-specific issues at work, of course

>48 ccatalfo: Also possible, if a bit unlikely, that something about our exports isn't playing nice with Open Office. I used Open Office a lot a few years ago, and would occasionally run into spreadsheets that appeared as a garbled mess. Let me know if you want my help testing!

>44 bnielsen: It looks great all the way down.

I'm closing the bug report. Thank for your help. I'll investigate a bit further and reopen if I find out what the problem is :-)

Re-opening as existing bug.

Other reports to look at:

https://www.librarything.com/topic/291252
https://www.librarything.com/topic/301811

I exported my records and I was having a problem. I got this error when trying to open the Excel export in Excel: "We found a problem with some content in 'LibraryThing_username_catalog.xls' Do you want us to try and recover as much as we can? (I successfully exported previously)

I followed bug breadcrumbs to a bunch of comments and other threads. I saw someone mention 'special characters' or other languages not being handled well in Excel and I looked and I did have a couple of keyboard emojis in two reviews that I recently added. (The heart eyes emoji) I removed the emojis and the file exports and opens normally now.

I'm posting this in case another user comes by someday looking for a possible solution.

Is Exporting to Excel still broken? I haven't tried it in several years but used to use it regularly and never had any trouble. Now literally nothing happens after I choose what I want exported - no response. I see discussions of it in Bug Collectors going back to last March and beyond.

>55 MaryQueenofPeace:
>56 Taliesien:
I tried it with my library (5608 books). I did not time the Excel export, but it was more than ten minutes. The resulting file was 11.9 MB. Excel gave me two different warnings about errors in the file, eventually opened SOMETHING, but there was nothing that looked like my books. ISBN numbers, maybe, plus a few other hard-to-recognize columns.

Macintosh, OS 12.5.1, current version of Firefox (not that it should matter for this). Opened using the current Office 365 version of Excel for Mac (it said version 16.64).

I tried again in tab-delimited. This time I noted when it started. It took 12 minutes to prepare the data.

Cross-check: It appears it takes about 9-12 seconds per hundred books. (It did not appear to be entirely consistent.) There was also some start-up time and time at the end.

The resulting .tsv file (5.5 MB), when double-clicked, opened in Numbers rather than Excel; the result appeared to be fine except that it put in a box character rather than a blank if the book has no ISBN (I have a lot of those), and some of the unicode characters appear to have been misread.

Opening in Excel also worked: 5609 records including the header line, so it appears that every book got its own line.

So the .tsv export is perhaps more robust than the Excel export. Still, the Excel export works for some. I'd guess that, as some have suggested, the people who are having problems have strange data in their books. It would perhaps be possible to locate this by exporting portions of one's library (based on entry date, e.g.). When you pass a particular entry date, you may get bad data. Then you can look for the books on that date.

That's a pretty standard programming technique. Of course, it's also a bleeping lot of work that is probably not worth it. :-) But if you really want to do an export, it would be an experiment worth trying.

>57 waltzmn: I completely agree.

Depending on the program you feed your export file into, various problems can occur: If the program expects utf-8 input be aware that some of the fields in the export file are not editable and might contain stuff that's not utf-8. (Leaving you with two options I think: 1. find the problem and correct it before feeding the data into the program. 2. delete the record in your catalogue and input it again manually). The Subject field comes from various library sources and can contain other character sets than utf-8 :-(

Another possible problem is caused by some programs allowing TAB inside of a data field if quoted. I.e. a line like this:
"This line contains 3 stops and a TAB and semicolons"
might be interpreted as one field or as two fields. LT TSV export file is "two fields" but Excel and LibreOffice Calc uses the "one field" interpretation.

For people with programming skills it's rather easy to count the number of fields containing an odd number of " characters. With more effort one can write a program to transform the export file into something that can be safely imported into Excel / LibreOffice Calc / ...

>55 MaryQueenofPeace: Sorry for the trouble!

I think a related issue (see https://www.librarything.com/topic/344095#7928916) was potentially causing issues for our exports, as well. I just tested your Excel and tab-delimited exports and they both downloaded successfully (albeit a bit slow, as @waltzmn mentioned: we're going to look at that) and looked good on my end.

If you can please try again and let me know if you're still seeing issues?