Export to CSV

[TexasT]

First of all, Bookpedia looks like a great program!

However, I'm having problems exporting to CSV. Or, rather, importing that export into MS Excel... There are several flies in the ointment:

1 - If something in the "Summary" has quotes around it, it throws the whole thing off and from then on, all commas in the summary text are taken as separators, generating a new column entry.

2 - Tags are not exported

3 - The subject list is imported on different rows, with each subject in its own row.

Changing the quotes to angle brackets <<thus>> is a workaround for #1, although not a pretty one, and I'd rather not go there if at all possible. And as far as #2 and #3, I guess I could do without exporting those (and how do I do that??) and simply include them in a custom field, but that's not an optimal solution either...

What can I do to solve these issues?

Oh, yeah... I'm a brand new user of both Bookpedia and Mac -- I'm a Windows person who, according to my now-much-happier pet geek (my brother), has finally seen The Light.

Any help will be gratefully received. Thanks!

TexasT

[Conor]

1. The quotes in summary should be escaped, they should be turned into \" and Excel should have no issue ignoring them. If the quotes in the summary are not getting escaped for you do let us know, so that we can dig deeper. (It might be the subjects field causing the new entries.)
2. What you see in the table view is what you get in the export. So be sure to add the tags column to the table before exporting. By the same token you can remove the columns you don't like, as well as put them in the order you would like them exported.
3. I see this in the test I ran. I was hoping that Excel would be smart enough to know that new lines inside a comma should not be interpreted to mean a new record. I will do some research and try to find out if there is a standard way to escape new line characters.

Welcome to the Mac and more importantly to Bookpedia.

[TexasT]

1. The quotes in summary should be escaped, they should be turned into \" and Excel should have no issue ignoring them. If the quotes in the summary are not getting escaped for you do let us know, so that we can dig deeper. (It might be the subjects field causing the new entries.)

I saw where it tried to do that:
\Teach Yourself in 24 Hours\"
(that's a little empty rectangular box as the first character; at least, that's what I see...)

As you can see, the escape code is there for the first quote, but the quote isn't. Any commas after that in the text get interpreted as separation codes. BTW, the Summary starts with the book title in quotes.

Now I've added a second book, and I'm having problems with the encoding for diacritics. FYI: Opening the text file in Notepad (Windows) shows it's saved as UTF-8, and if I "save as" using "Unicode", it fixes the problem when importing it into Excel (Windows).

2. What you see in the table view is what you get in the export. So be sure to add the tags column to the table before exporting. By the same token you can remove the columns you don't like, as well as put them in the order you would like them exported.

Well, I thought I had done that... apparently not, since it's working now. Thanks!

3. I see this in the test I ran. I was hoping that Excel would be smart enough to know that new lines inside a comma should not be interpreted to mean a new record. I will do some research and try to find out if there is a standard way to escape new line characters.

I thought it was \n for new line....

Welcome to the Mac and more importantly to Bookpedia.

Thanks! I'm enjoying both so far! I'll be dependent on Windows via Parallels for quite a while, though, since most of my work software is only available for that platform and nothing comparable has been developed for the Mac (present options are pretty pathetic in comparison).

TexasT

[TexasT]

I saw where it tried to do that:
\Teach Yourself in 24 Hours\"
(that's a little empty rectangular box as the first character; at least, that's what I see...)

As you can see, the escape code is there for the first quote, but the quote isn't. Any commas after that in the text get interpreted as separation codes. BTW, the Summary starts with the book title in quotes.

UPDATE:
Inserting text before that book title does not work -- it still doesn't properly escape that first quotation mark. Do you want me to send the files to you? Or you might want to try it yourself with the two books I've added:

Alpha Teach Yourself Spanish in 24 Hours - ISBN 9780028636160 - quotation escape problem
EL Pequeno (sic) Larousse Ilustrado 2007 - ISBN 9789702214281 - encoding problem, diacritics

Thanks, Top Dog Conor!

TexasT

[Conor]

Download the beta of Bookpedia and try the export. I have switched the encoding to unicode from UTF8 and Windows should be able to read it properly. I see the missing quote after the first slash issue with the "Teach Yourself Spanish" book, but I see this issue only in Excel. If I open the file before hand in a text editor all the quotes are escaped. With the beta I also added escape for tabs if the file is tab delimited, so try the tab delimited export instead to avoid the quoting issue.

Thank you for the all the help in making Bookpedia better. Especially with so much information and attention to detail. If it's not an Excel bug and you are not seeing the first quote escaped properly in the text file do let me know.

[TexasT]

Downloaded the beta and tried the export again, both as CSV and tab-delimited. Yup, the CSV export is still messing up those quotation marks in Excel. Just like you, I see them escaped in the text file -- hadn't looked at those earlier because they make me go cross-eyed, . One thing I still notice, though, is that the Summary text is still preceded by a character that looks like this: [] Except that it's a solid little rectangle. Could that be causing problems?

The tab-delimited file works fine, though, so I'm a happy camper. I tend to prefer that type of file for import into Excel anyway, but it didn't work right earlier, which is why I tried the CSV one.

The new lines are being escaped OK in the text file, but, for some reason known only to the Gatesites in their infinite wisdom, Excel isn't recognizing them as line breaks and leaves the " \n" in every time. But that's OK, it's a minor issue, and as long as it gives me the entire summary on its own column, I'm OK with it.

No need to thank me for my "help"; quite the contrary, thank YOU for your quick fixes! And as far as your comment, "Especially with so much information and attention to detail," well, what can I say -- it's that Windows training, I guess. The poor developers on that side can't count on standard hardware, so they need all the help the users can give them!

Again, thanks so much for the fixes, oh Speedy Top Dog Conor, and for that Unicode export, which saves me a step.

I'll go continue playing with Bookpedia, and also CDpedia and DVDpedia.

TexasT

[Conor]

Glad to be of help and more importantly that it's working and you are able to work around the small issues. I don't think Excel does new line imports in any form – including \r either. The issue with tab delimited and CSV files is that there are so broadly used there is no strict standard as there is with XML.

The black square might be a symbol of a character that it can't show. This might be part of your data as I am not able to replicate it. If you could please email me one or two of your entries, I can then test it with your data. Select two entries in the table view that contain the character in the summary and drag them out to your desktop. This will create two .bcard files that you can attach to an email (our address is on the support page).

[TexasT]

Excel, much like Word, "rarely misses an opportunity to perplex" (Bob Buckland, as quoted at http://word.mvps.org/).

I've sent you a zipped file with the .bcard file for the book entry that's causing the funky character, plus an old .txt export and the corresponding .xls file which both show the infamous character -- at least on the Windows side of the equation.

Thanks again!

TexasT