Big news! LibraryThing is now free to all! Read the blog post and discuss the change on Talk.
This site uses cookies to deliver our services, improve performance, for analytics, and (if not signed in) for advertising. By using LibraryThing you acknowledge that you have read and understand our Terms of Service and Privacy Policy. Your use of the site and services is subject to these policies and terms.
  • LibraryThing
  • Book discussions
  • Your LibraryThing
  • Join to start using.

New Feature: True Excel Export

New features

Join LibraryThing to post.

Aug 15, 2016, 3:50pm Top

Aug 15, 2016, 3:55pm Top

Any chance of being able import records in this format?

Aug 15, 2016, 4:04pm Top

We need to improve import generally, but I see problems there. Excel is so flexible, people are going to do very odd things and expect them to import. But we at least could do it now.

Edited: Aug 15, 2016, 4:41pm Top

This worked fine on a small subset, but not on my full catalogue. Both times that I tried the full catalogue I got the message that 'there was a problem with the website so it had reloaded' during the download process.

I'll try again in the morning.

Aug 15, 2016, 5:10pm Top

Will try yours.


Aug 15, 2016, 5:15pm Top

I noticed that the ratings are automatically rounded up if you used half-star ratings. Is there any way to get the export to include the true ratings?

Aug 15, 2016, 5:16pm Top

I was doing it on the iPad. If that doesn't work in the mornng, I'll try it on the Windows machine and try to move it from there. I'd really like this to work because we leave for England in a week, and being able to check my list before purchasing books is a good idea.

Aug 15, 2016, 5:23pm Top

>6 Familiar_Diversions:

Good catch. I'll get on that.

Aug 15, 2016, 5:58pm Top

>7 MarthaJeanne:

Okay, I tested it with yours. Should work.

Aug 15, 2016, 6:01pm Top

Nice! Seems to work for me. (I'm having some issues with LibreOffice but that seems to be a problem on my side.)

Aug 15, 2016, 6:06pm Top

I noticed that for books with multiple date started/date read entries, only the most recent one seems to be captured. Is there any way to get it to export all dates entered there (or am I just not seeing where it puts them)?

Aug 15, 2016, 10:25pm Top

I've had the best luck (in terms of how much I want getting imported from the export file) using .tsv. I'll try this. But really, it's the import that's driving me crazy. I export a handful from one account, import to the other, and spend a whole day cutting and pasting. My nightmare is that you're going to announce true export/import just as I've finished moving 2000+ entries by hand.

Aug 15, 2016, 10:44pm Top

I noticed that the ratings are automatically rounded up if you used half-star ratings. Is there any way to get the export to include the true ratings?

Fixed. Thanks.

Aug 15, 2016, 11:33pm Top

Totally wonderful. I like your fix for leading zeros.

Aug 16, 2016, 12:24am Top

>14 Robert@sterbal.com:

For ISBNs? Yeah, there is literally NO WAY to do it otherwise. Setting the ISBN field to text half-works, but on the Mac it still tries to put them into scientific notation until you click on the field. Argh. :)

Aug 16, 2016, 12:54am Top

Well, gosh dang it, I just exported a few test books as Excel from one account. When I tried to import the file into the other account I got this:

Problem: There are no books in your file.

Tried twice. Grrr....

Aug 16, 2016, 1:03am Top

It doesn't import Excel. Excel is a highly specialized proprietary format. Export as some text format.

Edited: Aug 16, 2016, 3:52am Top

OK, I think this is going to work. With over 7 000 books, the file comes to 9.1MB. That's a big file, and for some reason Safari couldn't handle it. Whether it was the size of the file as such, or just the length of time it look to download on our connection, I have no idea.

On the Windows machine each stage took a long time. Processing, downloading, uploading to OneDrive. Now I am at the crucial stage of downloading to the iPad.

ETA That took two tries, but has now worked. Success! I can go book shopping in England!

Aug 16, 2016, 6:15am Top

It worked nicely for me on Libre Office. Thanks!

Aug 16, 2016, 9:12am Top

When will you be supporting VisiCalc?

Aug 16, 2016, 10:26am Top

I do appreciate the ease of selecting which records to export. However, would it be possible to limit the number of fields exported? It appears that all the fields possible on the record are exported; I would like to limit it to only certain selected fields such as title, author, publication information, comments (since our Friends Meeting library puts its call number in that field), and tags. It is a real nuisance going into the spreadsheet and deleting numerous columns.

Aug 16, 2016, 10:30am Top

>21 sallylou61: More of a nuisance than deselecting the columns to export in LT? It seems like six of one, half dozen of the other to me.

Aug 16, 2016, 2:02pm Top

>21 sallylou61: There is now a field for personal call number systems, so they could move the call number from the comments to that field...

Aug 16, 2016, 3:15pm Top

I ran a test just now for both excel and tsv and all worked well. Excel is slower but in the scheme of things, no big deal. tsv was about 20 seconds to go through the format stage and about 5 seconds to download. Excel was about a minute or so to go through the format stage and about 5 seconds to download. My library size is 7230.

Aug 16, 2016, 4:04pm Top

I *love* this!! I'm always curious as to how many books I've read thus far this year...now I can keep track. Plus it gives me a list I can sort/edit, etc. THANKS!

Aug 16, 2016, 8:34pm Top

>22 jjwilson61: There are currently 5 styles (A thru E) in which one can display their catalogs. It would be great to say that one wanted their records to be exported into excel using style A for example. That would be exporting the selected fields without needing to check off each one.

I'm not sure how much programming this might involve -- or how high a priority the exporting of records is in the many endeavors which LT staff pursue to serve its customers.

Aug 16, 2016, 9:12pm Top

ah, I've never worked with the Mac version. How annoying. I'm emailing you a version of mine that shows a columnar version of a row on a separate worksheet.

Aug 16, 2016, 11:38pm Top

I think we'll stick with a single export. Choosing fields would get very fiddly and for little benefit—if you want to delete a column, delete a column.

Aug 17, 2016, 12:36am Top

I think a lot of whether deleting before or after downloading matters is dependant on the speed of your connection. >24 ulmannc: said that he could download is 5 seconds. I wish! (Similar library sizes.) Being able to pare down the size of the file before downloading might have saved me a lot of hassle.

Aug 17, 2016, 5:53am Top

Maybe this should be a bug report, but I'll try here first. When I press Export, it starts processing, and after a while it reaches 7003 books processed, but the rotating icon doesn't go away and the download option doesn't appear.
Have I exceeded some limit or timeout?
I did succeed in downloading the xls file once, but that's two days ago. (That made me notice that some of my Comment fields ended with blank lines, so I've cleaned up that and wanted to have another go :-)

Aug 17, 2016, 8:11am Top

I tried to export Excel report this morning and it is not working. I am able to used all imports links Except the "Universal Import". I did not have this problem last week. I also tried the url in the blog post and was not successful. What am I doing wrong?...Thank you for your help

Aug 17, 2016, 8:49am Top

how do you create a small subset?
as soon as I click on Import/Export, I get the problem message.
This does not work at all for me....

Aug 17, 2016, 8:51am Top

Server problems. It has been noticed by LT staff, so it will probably be fixed soonish.

Aug 17, 2016, 8:54am Top

Do I need to update Microsoft office? I have the 2013 version.

I appreciate any advice!!!

Aug 17, 2016, 9:26am Top

Not because of this, you don't. (And right now, LT has server problems, so wait an hour or so before you try to export your data.)

Aug 17, 2016, 10:09am Top

>34 Open_Hand: As >35 bnielsen: said, sit tight for a while and DON'T update MS Office. Go have a cup of (your favorite beverage), take a walk for a while and see when things are reported as working OK.

Aug 17, 2016, 10:34am Top

omg. thank you so much. I bought a laptop to take to the Open Hand Library and I've been freaking out that the laptop is just not powerful enough to handle Library Thing.

Aug 17, 2016, 2:29pm Top

I can export everything just fine from LT, but it will never import to Goodreads. I've tried everything I can think of to get it to import to GR, but it just doesn't work.

Aug 17, 2016, 2:37pm Top

>38 kcpstudent: Last time I tested imports to Goodreads, they had an issue with their UTF-8 encoding (if I recall correctly). No response from contacting them about it yet, I'm afraid. You might try emailing them on your end! They might be more responsive.

Aug 17, 2016, 6:54pm Top

When will you be able to import users libraries to Egnyte?

Aug 17, 2016, 8:12pm Top

Edited: Aug 18, 2016, 12:40am Top

The problem reported in >30 bnielsen: is still happening for me. The "export all books as excel" happily reports that it has processed all 7003 books and then it just keeps spinning the wheel. So it never gives me a link to download the export file.

I'll open a bug report on this.

Aug 18, 2016, 12:41pm Top

Ditto here, and I have 1,265 books to process. It just keeps spinning and never gives me a download link. Pity, since this would be an easy way to import into Goodreads. (Being a library cataloger for my work, though, I appreciate that LibraryThing allows for more complete cataloging like I'm used to doing, although there are still some annoyances where I wish I could do the sorts of things I can do at work, but, really, for the price, I can't complain!)

Aug 18, 2016, 1:29pm Top

>42 bnielsen:
>43 schplorp:

Ditto, with mine just sitting and spinning at 2,146 books processed.

Aug 19, 2016, 7:56am Top

Doesn't work. Is busy for hours to compile and then never finishes its task. Is that a general error or just my computer?

Aug 19, 2016, 9:51am Top

>46 alexbolding: Definitely not your computer. But I don't know if it works at all for any user.

It's a bit disappointing that the new feature simply doesn't work.

Aug 19, 2016, 9:57am Top

It did until the server problem.

Aug 19, 2016, 10:05am Top

Yes, it worked fine for me when I tried it after this thread was made.

Edited: Aug 19, 2016, 1:41pm Top

I exported once in excel format and then it hasn't worked since. The export file was not perfect but that might have been my version of office.

Or maybe my browser was just acting weird: http://xkcd.com/1722/

Ah, explanation on delayed bug fix here: http://www.librarything.com/topic/229303#5695907

Aug 19, 2016, 8:22pm Top

I'm having the same problem ... it reports processing the books but then spins endlessly, never finishes.

Aug 20, 2016, 8:12am Top

Same problem as >30 bnielsen: and >42 bnielsen: et al.

Everything was ducky until it got to book 3703. It's been hanging there for an unreasonable period of time and my guess is it's permanently hung. Will try again and report back if I get any different result.

Aug 20, 2016, 8:20am Top

Follow up: had a thought and tried exporting "Filtered Books" by checking "Your Library" since, in my case, it amounts to the same thing. As they say in France, "no soap." (It IS France where they say that, right?)

Aug 20, 2016, 3:18pm Top

Although the export worked well for me on a very small file early in the afternoon of August 16th, it has not worked for me since - not even on a file of 11 titles. I just tried again today. (I've ust read the post that it will not be worked on until next week.)

Aug 20, 2016, 4:01pm Top

Export to Excel is simply not working for me. I have tried using both Firefox and Internet Explorer browsers. Message on screen shows "xxx Books processed" but it seems to get stuck there and no download ever happens. Neither did the export to Excel work when I tried on a subset of our collection.

Aug 20, 2016, 5:53pm Top

>56 mgmcnj: It's not working for anyone right now. Looks like we have to be patient.

Aug 21, 2016, 8:54am Top

Same problem as mentioned in previous posts. Nothing happens after the books are processed.

Aug 21, 2016, 9:33am Top

Aug 22, 2016, 3:06pm Top

Aug 25, 2016, 7:44am Top

I tried the excel export but it hangs after the 3348th book.
I'm using Chrome.

Aug 25, 2016, 7:55am Top

>61 ghiso:

Please see the bug report linked in #59.

Sep 2, 2016, 1:16pm Top

It works very well (4001 books).

Sep 7, 2016, 6:12am Top

Excel Export is working wonderfully. Thanks for fixing it!!!

Sep 11, 2016, 10:57am Top

Tried this for the first time today. I have Microsoft Excel for Mac 2011, version 14.6.7 (160722), latest installed update 14.6.7. I downloaded the file but when I opened it in Excel I received a message that some data was unreadable, and did I want Excel to open the file and repair it? Yes. Excel opened and a message said that that it had repaired or removed unreadable data. The log file that was created reads:

Repair Result to LibraryThing_mandojoe_catalog 01944.xml
"Errors were detected in file 'Macintosh HD:Users:mandojoe:Downloads:LibraryThing_mandojoe_catalog.xls'
Excel recovered your formulas and cell values, but some data may have been lost."

There was much missing data near the end of the file, beginning with line 4779 (out of 5116 lines). I didn't explore the results in detail but concluded that this just wasn't working. Any issues I should know about? -- mandojoe

Sep 11, 2016, 2:57pm Top

>65 mandojoe: See http://www.librarything.com/topic/229303

I think you are seeing the same issues as me. I.e. lots of data missing towards the end of the file.

An easy check would be to download the tsv version and note its size. Then download the Excel export and save it as .csv file. The two files should be about the same size since they should contain more or less the same information.

Sep 13, 2016, 10:23am Top

Thanks, bnielsen. I'll give it a try. I expect that nothing of my data is lost but that there is a problem with the direct True Excel Export software.

Sep 13, 2016, 11:50am Top

>65 mandojoe:

In your place I would download a more robust format (preferably TSV as it is more complete), and see what's going on at the end of line 4779 or the beginning of line 4780 that's causing Excel to fail.

Edited: Sep 13, 2016, 2:32pm Top

>68 lorax: Technically I think it is the program exporting to the file that does something weird. I.e. you get a legal Excel file, but the contents are less than expected. I'm just objecting to your "causing Excel to fail", because I think that this is not Excel's fault and I don't think it fails :-)
I could be wrong, but the export file is somewhat smaller than I'd expect, so I don't think my missing data is exported at all.

And yes, TSV should work like a charm.

Sep 18, 2016, 12:28pm Top

Worked fine for my small collection. This will be very handy - thanks so much!

Oct 5, 2016, 10:22am Top

Thanks. The TSV file worked fine. -- mandojoe

Edited: Oct 8, 2016, 9:44am Top

Okay. I've tried multiple times on multiple computers using multiple different browsers. Every single time I get the same result. First, the downloaded file gives me an error message when I go to open it. Then, after it opens, I always get the same exact result: three columns of data, one with an eight-digit number (the book ID), one with a single digit number (the "Sort Character") and one column with a date. NOTHING else. Plus a banner in red in the excel file saying that it has detected a problem with the file and that editing it may harm my computer. What is going on and how can I fix this? Thanks!

Oct 8, 2016, 10:24am Top

Plus a banner in red in the excel file saying that it has detected a problem with the file and that editing it may harm my computer.

That sounds like a virus. A lot of them have messages like that and use things like red banners. There's no way that you could edit a non-system file in a way that would harm your computer.

Edited: Oct 8, 2016, 11:08am Top

>73 jjwilson61: Seconded. Try this: http://www.surfright.nl/en/hitmanpro
Failing which, try the "Post-Compromise" advice in the last few paragraphs of this article: http://krebsonsecurity.com/tools-for-a-safer-pc/
Good luck.

ETA: Well, now that I read it again... multiple computers and multiple browsers... hmm. Now I might actually have to think. This is a bad time to ask me to do that. I'm gonna hope someone else here can do the thinking. Meanwhile, I've got to do some napping. And then some more working.

Edited: Oct 8, 2016, 11:14am Top

You mean an error message like this?: http://www.thewindowsclub.com/office-has-detected-a-problem-with-this-file
Try the advice there.

Oct 8, 2016, 12:17pm Top

>72 Gypsy_Boy:

Could you give more details on the devices you're using? For instance the Operating system/browser combination and maybe the Excel version? These would all make a difference in what's wrong.

Oct 8, 2016, 4:28pm Top

Thanks. On my home computer, the operating system is Windows 7; the browsers are Firefox 49.0.1 and IE 11. The Excel version is 14.0.7172.5000, part of Office Professional Plus 2010.

>75 davidgn: davidgn: yes, like the red banner there.

I found a workaround; I gave up on the Excel version and ran it as tab-delimited text and then pasted into Excel, but I don't understand why it won't simply work as Excel from the start.

Thanks all.

Oct 8, 2016, 5:48pm Top

>77 Gypsy_Boy:

The True Excel Export doesn't actually work. (And I don't really have an answer as to why LT announces a feature and then ignores reports about how it doesn't work.)

Oct 9, 2016, 2:10am Top

>78 bnielsen: It worked for awhile at least. I downloaded my whole library, from two different accounts, without difficulty.

Oct 9, 2016, 6:17am Top

It worked the first 24 hours or so until they got server trouble. Then something broke which apparently hasn't been fixed yet.

Oct 12, 2016, 7:27pm Top

I'm just guessing but suppose that the "True Excel Export" must generate the XML that Excel uses in the newer versions for the .xlsx files. Sometimes people don't have the updates or recent enough versions to handle these. If so, they would never work for them. However office 2010 seems new enough to handle that.

An XML document must be just so or it will fail to import. From reading the above it seems that sometimes the issue was connected with special characters that confused the system. To test this, they would have to look at libraries with problems and see where the import dies.

Tab-delimited export is not perfect (missing many fields) but remains what works for now.


Edited: Oct 13, 2016, 11:43am Top

>81 Keeline: I don't think the True Excel Export have more fields than the TSV. But anyway it is broken :-) The Mark export have a couple of interesting fields, but that is also broken. At least it is not called the True Mark Export yet, so it must have some potential to break in an even worse way.

Oct 13, 2016, 12:49pm Top

>82 bnielsen: MARC export should be working. Are you having issues with it?

Oct 14, 2016, 8:39am Top

>83 lorannen: Not really, since I stopped using it because this bug was never fixed:


Oct 14, 2016, 9:09am Top

MARC seems to work for my 8,600+ books, I just tried it. I only have 16 books from "Det kongelige Bibliotek" and 8 from "Det Kongelige Bibliotek", though.

Edited: Oct 14, 2016, 10:05am Top

>85 anglemark: Thanks. I'll give it another try and see if it has been silently fixed somewhere in the last two years :-)

(I think the main advantage to MARC export was that it contained a few reviews for books I hadn't reviewed myself).

ETA: Same problem, I think:

cat lt.MARC | perl marc.pl --ifmt=marc --ofmt=text - | grep -c ^LDR

but it seems that I can export the missing entries one by one, so maybe the filtered export is different?

I also get stuff like this. I.e. a 590 with line breaks and missing characters. ("Omslaget viser mødet i en komite på ca tolv personer" bliver til "Omslaget viser mdet i en komite p ca tolv personer")

>85 anglemark: have you checked your MARC export for stuff like this?

LDR 01348 2200181 i 4500
001 18282723
003 MePoLT
005 20161014091123.0
040 \\|aMePoLT|cMePoLT|erda
090 \4|aPN6231.M2P3
092 \4|a350.0002
100 1\|aParkinson, C. Northcote.
245 10|aParkinsons lov eller stræben efter fremgang.
520 \\|aParkinsons lov eller strben efter fremgang by C. Northcote Parkinson (1958)
590 \\|aOmslag: Osbert Lancaster
Omslaget viser mdet i en komite p ca tolv personer, der slet ikke ser ud til at have nogen interesse i at n et resultat
Indskannet omslag - N650U - 150 dpi

Oversat fra engelsk "Parkinson's Law" af Eva Hemmer Hansen
920 \\|aBureaucracy|aHumour|aManagement|aRecycled
921 \\|aIndeholder Parkinsons lov eller den voksende pyramide, Den rette mand eller udvælgelseslæren, Ledere og komiteer eller ineffektivitetskoefficienten, Folkets vilje eller den årlige generalforsamling, Personlighedssortering eller cocktailformlen, Højfinansen eller interessens forsvindingspunkt, Palmebladstag - Packard eller en formel for succes, Planer og komplekser eller det administrative trauma, Injalitis eller paralytisk lammelse, Pensionspunktet eller aldersgrænsen
Klassikeren indenfor området med illustrationer af Osbert Lancaster.
En klassisk bog selvom man godt kan mærke alderen tynge den
923 \\|aYour library

Oct 14, 2016, 12:41pm Top

>86 bnielsen: Thanks for the detail. Because one of our devs marked (ha) that old one as fixed a while ago, it had slipped off my radar. He's not around today, but I'll follow up with him on Monday and see where we're at.

Oct 14, 2016, 5:22pm Top

>87 lorannen: Thanks. Maybe I should check on some of the other bugs I've reported :-)

May 26, 2017, 6:22pm Top

Your True Excel Export worked beautifully for my library -- less than 2,000 entries (books + music). I did have to make sure to save download in Excel Wrkbk (*.xlsx) format for my Office 365. Thanks for this feature.

Edited: May 30, 2017, 3:15am Top

I am wondering for the Posts. I never got a Result by 2329 Books, It never comes to the end. Not on .com and not on .de. It dont works.
For less of 500 books it works ...

Edited: May 30, 2017, 10:27am Top

>90 t.sebesta: I can confirm this is happening on .com (on our test server) for your account:

The arrows just load and load, and the export seems to be stuck on the "2,329 books processed" - you never actually get the option to download the file. Refreshing the page just brings you back to the option to export again.

ETA: The bug reported for this issue (http://www.librarything.com/topic/229303) hasn't yet been fixed, so you may want to try exporting as .tsv (tab-delimited text) for now until a fix is made!

May 30, 2017, 5:27pm Top

Thank you, I hope it will fixed soon :)

Oct 16, 2017, 1:59pm Top

I believe this should be fixed - I changed the code to allow longer process time. I was just able to export 4k books to Excel format.

Oct 16, 2017, 6:18pm Top

I don't think it is fixed, at least not for my library:

I've updated http://www.librarything.com/topic/229303 and checked that the bug is still present. (Not the spinning disk, but rather the weird output.)

Nov 12, 2018, 9:42am Top

Finally found this due to Google and the 2016 blog post and it worked like a charm for me, thanks! I've never liked the other options. I just wish it was a little more accessible.

Aug 8, 2019, 7:44pm Top

Just downloaded to Excel - worked like a charm! Small library at this point, and really appreciate the smooth export.

Group: New features

45,361 messages

This group does not accept members.


This topic is not marked as primarily about any work, author or other topic.


No touchstones

About | Contact | Privacy/Terms | Help/FAQs | Blog | Store | APIs | TinyCat | Legacy Libraries | Early Reviewers | Common Knowledge | 146,531,477 books! | Top bar: Always visible