LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

What do you do when you want to store text in a single "cell" of a spreadsheet file?

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!

 

Capture.PNG

0 Kudos
Message 1 of 8
(3,105 Views)

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"



Using LV2018 32 bit

Highly recommended open source screen capture software (useful for bug reports).

https://getsharex.com/
0 Kudos
Message 2 of 8
(3,073 Views)

@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!

 

Capture.PNG


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?

 

 

 

0 Kudos
Message 3 of 8
(3,061 Views)

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. 

0 Kudos
Message 4 of 8
(3,043 Views)

I save everything in a TAB delimited TEXT file with a .txt extension. 

 

This serves two purposes 

  1. I can look at the data file with any text editor and the columns will line up properly unlike a .CSV file.
  2. Excel can open and import these files with no effort for data analysis

 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.

========================
=== Engineer Ambiguously ===
========================
0 Kudos
Message 5 of 8
(3,027 Views)

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.

Bill
CLD
(Mid-Level minion.)
My support system ensures that I don't look totally incompetent.
Proud to say that I've progressed beyond knowing just enough to be dangerous. I now know enough to know that I have no clue about anything at all.
Humble author of the CLAD Nugget.
Message 6 of 8
(3,021 Views)

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"

0 Kudos
Message 7 of 8
(3,013 Views)

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.

Bill
CLD
(Mid-Level minion.)
My support system ensures that I don't look totally incompetent.
Proud to say that I've progressed beyond knowing just enough to be dangerous. I now know enough to know that I have no clue about anything at all.
Humble author of the CLAD Nugget.
0 Kudos
Message 8 of 8
(2,995 Views)