Colleagues, Anybody who knows how to go around a CSV problem; where barcodes (and or some fields) downloaded as CSV report in Koha is automatically transformed into dates by the spreadsheet application (Using Ms Excel 2007). See attached screenshot for example or download here: https://dl.dropboxusercontent.com/u/7725309/koha_csv.png EVAN NJOROGE about.me/uhusiano *•*adlsn.org* "Facilitating access to African digital content" *
Op 16/07/13 12:41, Evan Mungai Njoroge schreef:
Anybody who knows how to go around a CSV problem; where barcodes (and or some fields) downloaded as CSV report in Koha is automatically transformed into dates by the spreadsheet application (Using Ms Excel 2007).
My cynical reply would be to not use a spreadsheet for things you're not supposed to use it for, like being a database :) However, more realistically (and this applies more to Libre Office, but I'm sure Excel has something equivalent), when you import the CSV file you get a dialogue that previews it for you. At the top of the columns in the preview, you can select them to change their type. By default they're something like "general" which means "best guess." You should set all the ones you want to be literal to be something like "text" and it won't try to be smart. -- Robin Sheat Catalyst IT Ltd. ✆ +64 4 803 2204 GPG: 5957 6D23 8B16 EFAB FEF8 7175 14D3 6485 A99C EB6D
I had the same problem with Excel 2007 when exporting records from Horizon for import into Koha--the barcodes field had numbers like 3.2975E+13. Here's what worked for me: Right click on the top of the column Select "format cells" Choose "number" Change "decimal places" to 0. Irrelevant to this problem but possibly useful: I also had to design a cataloging framework for our library. I exported the default framework to a CSV spreadsheet, but editing it in Excel 2007 wouldn't work--I had to edit it in OpenOffice.Org Calc before I could upload it back into a new framework. Hope this helps, Fred King kohauser@phred.us
Colleagues, Anybody who knows how to go around a CSV problem; where barcodes (and or some fields) downloaded as CSV report in Koha is automatically transformed into dates by the spreadsheet application (Using Ms Excel 2007).
See attached screenshot for example or download here: https://dl.dropboxusercontent.com/u/7725309/koha_csv.png
EVAN NJOROGE about.me/uhusiano **adlsn.org* "Facilitating access to African digital content" * _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Op 16/07/13 14:48, Fred King schreef:
Right click on the top of the column Select "format cells" Choose "number" Change "decimal places" to 0.
Use text, don't use number. This is exactly what I'm talking about :) Barcodes aren't numbers, they're strings of characters with no particular semantics. In particular, leading zeros and punctuation like '-' risk getting lost. Barcodes can even have letters in them, like the example provided does. I've also seen ISBNs turned into scientific notation because they were too big to be regular numbers. That caused headaches. My fundamental belief is that spreadsheets tend to cause data loss unless you tread very carefully. This kind of thing is not what they're designed for, nor are they good at it (though they _seem_ that way.) When working on migrations, the only reason a spreadsheet will touch my data is for browsing it, they are OK for getting rough summaries of what's in there, but they always corrupt something. So I never let them save anything. It even can cause major economic downturns! http://finance.fortune.cnn.com/2013/04/17/rogoff-reinhart-excel-errors/ -- Robin Sheat Catalyst IT Ltd. ✆ +64 4 803 2204 GPG: 5957 6D23 8B16 EFAB FEF8 7175 14D3 6485 A99C EB6D
I would agree with you, except that in my case changing from the default to text kept the [rude adjective] barcode numbers in scientific notation. The barcodes I was trying to import into Koha didn't have any leading zeros, letters, or punctuation (I missed seeing those in the example)--they were just 14-digit numbers, each of which began with the same five numbers. And exporting from Horizon as a CSV file, massaging the data in a spreadsheet, then re-exporting using the Koha CSV import template was the easiest way to convert our database. For me, it worked. But YMMV. Fred King kohauser@phred.us
Op 16/07/13 14:48, Fred King schreef:
Right click on the top of the column Select "format cells" Choose "number" Change "decimal places" to 0.
Use text, don't use number. This is exactly what I'm talking about :) Barcodes aren't numbers, they're strings of characters with no particular semantics.
In particular, leading zeros and punctuation like '-' risk getting lost. Barcodes can even have letters in them, like the example provided does. I've also seen ISBNs turned into scientific notation because they were too big to be regular numbers. That caused headaches.
My fundamental belief is that spreadsheets tend to cause data loss unless you tread very carefully. This kind of thing is not what they're designed for, nor are they good at it (though they _seem_ that way.)
When working on migrations, the only reason a spreadsheet will touch my data is for browsing it, they are OK for getting rough summaries of what's in there, but they always corrupt something. So I never let them save anything.
It even can cause major economic downturns! http://finance.fortune.cnn.com/2013/04/17/rogoff-reinhart-excel-errors/
-- Robin Sheat Catalyst IT Ltd. â +64 4 803 2204 GPG: 5957 6D23 8B16 EFAB FEF8 7175 14D3 6485 A99C EB6D _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
participants (3)
-
Evan Mungai Njoroge -
Fred King -
Robin Sheat