HomeGroupsTalkZeitgeist
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.

1timspalding
Aug 15, 2016, 3:50pm Top

2AndreasJ
Aug 15, 2016, 3:55pm Top

Any chance of being able import records in this format?

3timspalding
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.

4MarthaJeanne
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.

5timspalding
Aug 15, 2016, 5:10pm Top

Will try yours.

T

6Familiar_Diversions
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?

7MarthaJeanne
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.

8timspalding
Aug 15, 2016, 5:23pm Top

>6 Familiar_Diversions:

Good catch. I'll get on that.

9timspalding
Aug 15, 2016, 5:58pm Top

>7 MarthaJeanne:

Okay, I tested it with yours. Should work.

10bnielsen
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.)

11shadrach_anki
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)?

12PhaedraB
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.

13timspalding
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.

14Robert@sterbal.com
Aug 15, 2016, 11:33pm Top

Totally wonderful. I like your fix for leading zeros.

15timspalding
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. :)

16PhaedraB
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....

17timspalding
Aug 16, 2016, 1:03am Top

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

18MarthaJeanne
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!

19comtso
Aug 16, 2016, 6:15am Top

It worked nicely for me on Libre Office. Thanks!

20suitable1
Aug 16, 2016, 9:12am Top

When will you be supporting VisiCalc?

21sallylou61
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.

22jjwilson61
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.

23gilroy
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...

24ulmannc
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.

25kitkeller
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!

26sallylou61
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.

27Robert@sterbal.com
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.

28timspalding
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.

29MarthaJeanne
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.

30bnielsen
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 :-)

31Mel4
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

32Open_Hand
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....

33bnielsen
Aug 17, 2016, 8:51am Top

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

34Open_Hand
Aug 17, 2016, 8:54am Top

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

I appreciate any advice!!!

35bnielsen
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.)

36ulmannc
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.

37Open_Hand
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.

38kcpstudent
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.

39kristilabrie
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.

40Jill.Ford
Aug 17, 2016, 6:54pm Top

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

41timspalding
Aug 17, 2016, 8:12pm Top

42bnielsen
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.

43schplorp
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!)

44cpg
Aug 18, 2016, 1:29pm Top

>42 bnielsen:
>43 schplorp:

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

46alexbolding
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?

47bnielsen
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.

48MarthaJeanne
Aug 19, 2016, 9:57am Top

It did until the server problem.

49.Monkey.
Aug 19, 2016, 10:05am Top

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

50bnielsen
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

51innisfree
Aug 19, 2016, 8:22pm Top

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

52Gypsy_Boy
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.

53Gypsy_Boy
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?)

55CharlottesvilleFM
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.)

56mgmcnj
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.

57PhaedraB
Aug 20, 2016, 5:53pm Top

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

58drasvola
Aug 21, 2016, 8:54am Top

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

59gilroy
Aug 21, 2016, 9:33am Top

60drasvola
Aug 22, 2016, 3:06pm Top

61ghiso
Aug 25, 2016, 7:44am Top

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

62AndreasJ
Aug 25, 2016, 7:55am Top

>61 ghiso:

Please see the bug report linked in #59.

63ddejaco
Sep 2, 2016, 1:16pm Top

It works very well (4001 books).

64StJosephIssaquah
Sep 7, 2016, 6:12am Top

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

65mandojoe
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

66bnielsen
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.

67mandojoe
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.

68lorax
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.

69bnielsen
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.

70KatyBee
Sep 18, 2016, 12:28pm Top

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

71mandojoe
Oct 5, 2016, 10:22am Top

Thanks. The TSV file worked fine. -- mandojoe

72Gypsy_Boy
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!

73jjwilson61
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.

74davidgn
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.

75davidgn
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.

76gilroy
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.

77Gypsy_Boy
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.

78bnielsen
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.)

79PhaedraB
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.

80anglemark
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.

81Keeline
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.

James

82bnielsen
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.

83lorannen
Oct 13, 2016, 12:49pm Top

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

84bnielsen
Oct 14, 2016, 8:39am Top

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

http://www.librarything.com/topic/118711

85anglemark
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.

86bnielsen
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
6955

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

87lorannen
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.

88bnielsen
Oct 14, 2016, 5:22pm Top

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

89JSWBooks
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.

90t.sebesta
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 ...

91kristilabrie
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!

92t.sebesta
May 30, 2017, 5:27pm Top

Thank you, I hope it will fixed soon :)

93ccatalfo
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.

94bnielsen
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.)

95the_red_shoes
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.

Group: New features

45,200 messages

This group does not accept members.

About

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

Touchstones

No touchstones

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