spreadsheet values

[CECSpecialistI]

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]

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]

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

[CECSpecialistI]

@gerontakos and @lake44me volunteered to follow up on this before 202-05-04

[lake44me]

This Microsoft discussion is about a related problem - quote marks appearing when text is copied and pasted from one cell to another. It may be that I have unwittingly acquired a set of quote marks by doing this, and they just continued to be propagated.

https://answers.microsoft.com/en-us/msoffice/forum/all/why-is-excel-adding-quotes-when-i-copy-a-cell-with/28e23792-4b66-4d6b-94f9-4720c238659a

The relevant insights are that Excel is adding quotes if the text contains specific non-printable characters. "I found that Excel adds the quotes only to strings containing the non-printable characters 10 (line feed), 13 (carriage return) and 9 (tab). All other non-printable characters, such as 12 (form feed) or 30 (record separator) do NOT cause Excel to add quotes."

So, the reason this seems to specifically happen to certain columns is those are the columns where long explanatory notes and examples would be entered, and we're trying to format them.

The Microsoft person trying to provide an answer did not know why Excel was doing this. They suggested pasting copied text first into Word, before copying it back into another cell.

Another article https://excel.tips.net/T002237_Getting_Rid_of_Extra_Quote_Marks_in_Exported_Text_Files.html (not Microsoft, but probably knowledgeable) states that "Sometimes the export filters used by Excel can produce undesired results in the exported file.
For instance, when you export to a tab-delimited text file, some Excel filters place quote marks around the text in a cell.
For instance, assume that a particular cell contains the following text:

Create bts; sitemask = "0110"; pcmlink = 40

This, however, is how the text in the cell is exported by Excel:

"Create bts; sitemask = ""0110""; pcmlink = 40"

Notice that Excel adds extra quotation marks, first around the entire cell contents, and then an extra set around any previously "quoted" text within the cell."

So having quotation marks around, or within the text of a cell is probably also a trigger to acquire an extra layer of quotation marks when Excel re-converts from Excel to tab-delimited format as the file is being re-saved.

Can we make our notes and examples understandable without using line feeds, the other 2 characters, any kinds of quote marks, or any other trigger we come across? Or, should we pursue workarounds? I'm not sure about VBA scripts, etc.

Laura

"I blame Microsoft"

[lake44me]

I have noticed quote marks in other places than my mappings - for example the label for 520 which is now reading: "SUMMARY, ETC. (R)" . I have experimentally removed the quote marks and the comma from just the first field label (suspecting that none of the non-printable characters were visible so maybe the comma caused the quote marks? oh dear...), and when I save and merge the file we'll see what happens.

[lake44me]

So now, I'm adding back the comma in the first 520 label and pushing. And lo and behold, that first 520 label has now acquired quote marks. It has a ways to go to rival the other 520 labels (and other parts of the 520 lines that contain that comma).

I'm not sure I can live without commas and do this.
Wonder if Libre Office's spreadsheet editor does this kind of thing? Haven't used it since it used to be Open Office...

Will try one more thing tomorrow - leaving the obvious text fields as "General" and only making the tag numbers "Text" while setting up the import - to see if it would make a difference. Need sleep now.

[lake44me]

Leaving the MARCFIELDLABEL column as "General" data type makes no difference in the addition of quote marks around the field content. They keep piling up if certain character(s) are contained in the text.

[lake44me]

I did one more experiment and one bit of research. On import of the .txt tab delimited 500 file, I left the Text Delimiter value as " (quote mark), and saved the file under another name. When I reopened it, the 520 label that had acquired one set of quote marks (the first one) still had just one set of quote marks. I'm not sure this will save us in every case, but maybe we should just let one set of quote marks remain if Excel applies them upon saving a file, and open it with the quote mark text delimiter set.

I checked out Libre Offices "Calc" (spreadsheet) application's manual https://documentation.libreoffice.org/assets/Uploads/Documentation/en/CG7.2/CG72-CalcGuide.pdf It describes CSV files thus:

Comma-separated values (CSV) files are spreadsheet files in a text format where cell contents
are separated by a character such as a comma or semi-colon. Each line in a CSV text file
represents a row in a spreadsheet. Text is entered between quotation marks; numbers are
entered without quotation marks.

So, it expects quote marks around text, and I think this applies no matter whether a tab, comma or other character is the cell delimiter is used. Its import process is very similar to Excel. That seems to be how spreadsheet applications operate.

[gerontakos]

It looks, after all Laura’s work, that the double-quote problem is largely solved.

First of all, when we open/import, we should always open our .txt tab-delimited files with text qualifier = " .

The text qualifier is our friend. It is used to wrap cell values that contain either a tab, CRLF, a comma, or quotation marks as part of the value.

The behavior of cells with values containing a CRLF or a tab is difficult to discern. It seems best we avoid using CRLF or tab inside cells. Commas however seem fine to use; our values just get wrapped in double-quotes.

Similarly, if we want to put any part of our values in quotes, the full cell value will get wrapped in quotes. In this case, there’s a complication: the text delimiter is equivalent to the quotes that are part of the cell value. The solution: escape the internal double quotes with … double quotes!

So the cell value that opens in Excel with text delimiter = " that displays as:
Joanne says open "flounder.txt" first
...would look like this as raw output:
"Joanne says open ""flounder.txt"" first"
…and that’s the way it should open in Excel if text delimiiter = {none}.
When text delimiter = " it displays as follows:
Joanne says open "flounder.txt" first

This is the root of our problem. We’re opening with text delimiter = {none} but when Excel closes a .txt file, it adds text delimiters. So we open with text delimiter = {none} and we see:
"Joanne says open ""flounder.txt"" first"
When we close the file, Excel says, uh oh, we’d better ensure that output with text delimiters and then saves as follows:
"""Joanne says open """"flounder.txt"""" first"""
(It wraps it in quotes, escapes the initial and terminal quotation marks, then inside that wrapper sees two quotation marks around flounder.txt and both those quotation marks need to be escaped; say the escape character were \ , it would look like this:
"\"Joanne says open \"\"flounder.txt\"\" first\""
).

The quotation marks proliferate each time we save using a .txt file with text delimiter = {none}.

If that sounds correct to everyone, we can write it into the workflow somehow. Then we can test it in practice and make adjustments as needed.

As for the already-existing double-quotes: I don't think it will be too difficult, somewhere down the line (downstream!) to eliminate them. We can use others' macros, write our own, or just find and replace using a text editor. So even if they're there, it doesn't seem like a big deal. It certainly should not stop us from moving forward.