11-01-2018 06:07 PM
This is not too deep of a programming question, I just wanted to hear what people do if you want to store a string in a single cell of a spreadsheet but it contains characters that would cause it to span multiple cells (at least in Excel). It seems like end of line, carriage return, and line feed cause Excel to shift to next row, and tab causes Excel to shift to next column (when used as the delimiter). My current idea is to replace with something similar, so I could reconstruct the original string if necessary. For instance "\t" becomes " -t ".
It will probably depend on the application and what kind of data you need to parse out later, but I just wanted to see what other people do!
11-02-2018 03:05 AM - edited 11-02-2018 03:32 AM
When you say spreadsheet, do you mean a CSV/PRN/ text based file rather than a true Excel workbook?
Using the report generation toolkit / ActiveX, it seems to allow tabs and new lines in a single cell.
For strings I have used a similar method to what you proposed <CR> becomes \r, <TAB> becomes \t etc., but making sure I also escape the escape character where used in the original data i.e. string "Foo\Bar" becomes "Foo\\Bar"
11-02-2018 05:53 AM
@Gregory wrote:
This is not too deep of a programming question, I just wanted to hear what people do if you want to store a string in a single cell of a spreadsheet but it contains characters that would cause it to span multiple cells (at least in Excel). It seems like end of line, carriage return, and line feed cause Excel to shift to next row, and tab causes Excel to shift to next column (when used as the delimiter). My current idea is to replace with something similar, so I could reconstruct the original string if necessary. For instance "\t" becomes " -t ".
It will probably depend on the application and what kind of data you need to parse out later, but I just wanted to see what other people do!
Problems like these never have a universal solution, especially where reconstruction of the data is required. Imagine storing commands for a shell like this, -t could be a legitimate flag and not a tab.
To make a scheme for this its always a case by case basis, you need to know what is and what is not allowed, and hope and pray that never changes 🙂
In your use case, does the data need to be human readable / editable in the spreadsheet?
11-02-2018 11:28 AM
Thanks for chiming in.
Matt: I am saving (.txt) files with tab delimiter using the "Write Delimited Spreadsheet Function".
Deceased: I am saving ASCII data from a device that is kind of a health monitor with a combination of 30 voltages, currents, and temperatures. I could try to parse out the parameters, but if the device ever gives an abnormal response, it's nice to be able to look back and see what it was. I will make a data viewer for people to load up data and look at it, and ideally my colleagues will use that, but sometimes people do go and look at the raw data as well.
11-02-2018 03:55 PM - edited 11-02-2018 03:58 PM
I save everything in a TAB delimited TEXT file with a .txt extension.
This serves two purposes
BTW: It does not matter if the contents you are writing is larger than a "cell" appears to be. Excel does not care and has a few options to make it easier to read, like work wrap and adjusting the column width.
11-02-2018 05:47 PM - edited 11-02-2018 05:48 PM
Enclosing the string in quotes places the whole string into one cell, regardless of what is actually in the cell. I'm not sure what happens if the string itself contains quotation marks, though.
11-02-2018 07:00 PM
Hi Bill,
Thanks, that's a clever trick when viewing the data in Excel! It doesn't work so great if I want to read it back later with LabVIEW though...
I wrote the following 1-element array to a text file using Write Delimited Spreadsheet: "this\tis\ta\ttest"
In Excel I see a single cell: thisisatest
If I read back the data using Read Delimited Spreadsheet I get a 4-element array: "this | is | a | test"
11-03-2018 03:07 PM
I'm surprised that LabVIEW didn't adopt the RFC 4180 Standard. Then again, it seems that CSV files are another one of those "loosely defined" text file formats, like ini files.