New Feature: True Excel Export
Join LibraryThing to post.
We've added a "true" Excel export.
Blog post: http://blog.librarything.com/main/2016/08/new-feature-true-excel-export/
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.
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.
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?
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.
Nice! Seems to work for me. (I'm having some issues with LibreOffice but that seems to be a problem on my side.)
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)?
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.
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?
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. :)
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....
It doesn't import Excel. Excel is a highly specialized proprietary format. Export as some text format.
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!
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.
>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.
>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...
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.
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!
>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.
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.
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.
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.
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 :-)
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
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....
Server problems. It has been noticed by LT staff, so it will probably be fixed soonish.
Do I need to update Microsoft office? I have the 2013 version.
I appreciate any advice!!!
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.)
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.
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.
>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.
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.
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!)
Doesn't work. Is busy for hours to compile and then never finishes its task. Is that a general error or just my computer?
>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.
Yes, it worked fine for me when I tried it after this thread was made.
I'm having the same problem ... it reports processing the books but then spins endlessly, never finishes.
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?)
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.)
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.
>56 mgmcnj: It's not working for anyone right now. Looks like we have to be patient.
Same problem as mentioned in previous posts. Nothing happens after the books are processed.
I tried the excel export but it hangs after the 3348th book.
I'm using Chrome.
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
>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.
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.
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.
>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.
Worked fine for my small collection. This will be very handy - thanks so much!
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!
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.
Failing which, try the "Post-Compromise" advice in the last few paragraphs of this article: http://krebsonsecurity.com/tools-for-a-safer-pc/
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.
You mean an error message like this?: http://www.thewindowsclub.com/office-has-detected-a-problem-with-this-file
Try the advice there.
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.
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.
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.)
>78 bnielsen: It worked for awhile at least. I downloaded my whole library, from two different accounts, without difficulty.
It worked the first 24 hours or so until they got server trouble. Then something broke which apparently hasn't been fixed yet.
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.
>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.
>82 bnielsen: MARC export should be working. Are you having issues with it?
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.
>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
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
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
>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.
>87 lorannen: Thanks. Maybe I should check on some of the other bugs I've reported :-)
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.
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 ...
>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!
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.
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.)
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.
This group does not accept members.
This topic is not marked as primarily about any work, author or other topic.