search

uwlib-cams / MARC2RDA

mapping between MARC21 and RDA-RDF
Creative Commons Zero v1.0 Universal
29 stars 1 forks source link

spreadsheet values #352

Closed CECSpecialistI closed 2 years ago

CECSpecialistI commented 2 years ago

spreadsheet values in character positions (and elsewhere) are doing weird things, trying to turn into dates. need to figure out how to make them sit still in a .txt file. Punctuation?

mcm104 commented 2 years ago

After some Googling, it looks like the solution would be in how we open .txt files in Excel -- it's converting plain text into dates upon opening the file, not saving it.

Clicking and dragging a .txt file into Excel will automatically convert plain text into numbers/dates wherever it can, so this should not be how people open our .txt to work on them.

If a .txt file is opened through File > Open, Excel should open up a Text Import Wizard. In Step 3 of the Wizard, select all columns in the Data Preview, and under Column data format, select Text, then Finish. This will import all cells as plain text, and will avoid autoformatting MARC fields, character positions, etc.

I haven't tested out what Excel does if you change the default application for .txt to Excel.

gerontakos commented 2 years ago

Here's an attempt at an explanation, if it matters: When we save an Excel file as text, every data type becomes "General." That is to say, every time we save the file, no matter what we did to "format cells" and change data types, it gets saved as "General." When launching those Excel general data types, numbers that include either forward slash / or hyphen - may be converted to a date format, and numbers that have a colon : or space+A or space+P may be converted to time format (Excel interprets the space + (A | P) as AM/PM). Melissa's method forces all columns to the Excel "Text" format when opening or "importing" the file. I don't see anything wrong with that. In fact, it mat be the only way to do it! In discussion #332 Laura specifically recommended opening a file that way. She included a few more details too. Jian in #332 recommended opening a file in a manner (dragging into Excel) that will change numbers formatted as described above. I will go back to that discussion and say that that method of opening a file is "no longer valid." Theo (me!) in #332 recommended opening a file in a manner (start excel FILE.txt) that will change numbers formatted as described above. I will go back to that discussion and say that that method of opening a file is "no longer valid."