String handling for excel file writing

[svazquez22]

Hello, I am trying to create a header section for my excel file and I have been having issues with handling my strings. Any methods anyone can recommend to achieve the format I show in my DesiredFormat.png file? Thank you.

[GerdW · Accepted solution]

Hi svazquez,

---

@svazquez22 wrote:

I have been having issues with handling my strings.

---

Then you should resolve those issues - by building the string correctly…

(The only difference I see is the placement of that Date value.)

I'm not a fan of those big ConcatString functions, I prefer FormatIntoString or even a plain string array constant:

[Bob_Schor]

A text file is not an Excel File.  An Excel File is not a text file.

There are several ways to have Text data appear in rows and columns (with and without headers).  A common way is to separate "rows" of Text by using "End-of-Line" character(s).  Separating "columns" is a little trickier.  You could use a <tab> character, which puts an "invisible" amount of space that text editors can interpret as "start the next text at an offset of 8 characters from the start of this line", so text....is......spaced..like....this.  This works well when read with a program that interprets the <tab> character properly (one such program, as it happens, is Excel).  Another common "separator character" is the comma (<,>), which might not appear in numerical data (except in some countries (like most European countries, where it is used as the "decimal point" character!).  Files that use commas as column separators and <EOL> as row separators are called "Comma-separated-value" (or .csv) files.  Guess whose Operating System has a special Icon for .csv that looks as though it means "Open with Excel".  [I wonder if Apple does this for the MAC ...].

To make an Excel (extension .xlsx) file in LabVIEW, I use the Report Generation Toolkit, which lets me populate an Excel Worksheet (including a pre-made Template file with headers either created "on the fly" or saved in a separate "template" file).

LabVIEW has a Write Delimited Spreadsheet function that will simplify (somewhat) the writing of text files in .csv or tab-delimited formats.  The "tricky part" of using this is this function is designed to write rows sequentially and "all-at-once".  It is easy to write "Row 1", compute some stuff, write "Row 2", compute, write "2D Data Array starting in Row 3", but once you've written a row, it is definitely not easy to make changes.

If you are going to write Text Files that "look like a Spreadsheet", you might want to investigate Delimited Spreadsheets and/or the Report Generation Toolkit.

Bob Schor

[svazquez22]

I have a program that writes data to an empty .csv file. What I would like to do is run my program, collect data, and have it create a .csv file and write my data to it. As it works now, I run my program, it prompts me to name an empty .csv file, then I collect data, then it writes it to that file I preallocate. I know there is a Write Delimited Spreadsheet vi and I have tried working with it, but I am having trouble implementing my desired string format into it. Any help would be appreciated!

[Bob_Schor · Accepted solution]

Some things to note:

• Comma-separated-variables (or .csv) files are "ordinary text files" where all the data entries are expressed as "text" (meaning numerical data are expressed in numbers, booleans are represented by "True" and "False").  Rows are delimited by "End-of-line" representations (typically using the character "Line-Feed", 0xA), with separate (column) entries separated by a comma, hence the name "comma-separated Variables", or ".csv" as an extension.
• .csv files have little to do with Excel, a proprietary Microsoft "Spread Sheet" application that uses a propriety (binary) format with the extension .xlsx (older versions of Excel use the extension .xls).
• Excel can read .csv files and import the text-based quantities in such files to allow you to "do arithmetic" and other spread-sheet operation with them (such as add or subtract rows and columns from each other).
• Part of the confusion is another Microsoft program, "Windows 10".  They decided to create an Icon for files with the extension ".csv" that strongly resembles the icon for their proprietary Spread-sheet program "Excel".  Furthermore, Excel can read and import comma-separated (or tab-separated) text files and once they are in Excel, can use Excel's "programming style" to manipulate the rows and columns in a limited fashion.

As you have already discovered, using LabVIEW to do "spreadsheet operations" can be complicated and difficult to interpret and understand.  NI provides a Report Generation Toolkit that can link to Microsoft Excel and simplify the process of doing fancy formatting and doing some "Excel" manipulations (creating, in the process, .xlsx files).

If you really want to process .csv files, LabVIEW provides several functions to help you turn 2D "rectangular" arrays of quantities (typically numeric or string) into .csv files.  Note that these should be pure text files, with a designated character (typically <tab> or <,> as a column separator and <EOL> as a row separator.  One pair of functions, on the String Palette, is "Spreadsheet String to Array" and "Array to Spreadsheet String", and another pair, on the Files Palette, is "Read Delimited Spreadsheet" and "Write Delimited Spreadsheet".

I think you will find it much easier to design your LabVIEW code if you structure it a line at a time.  To create a Header line ("Date, Sample Material, Sample Diameter, Sample Mass, Operator, Test Used"), use Spreadsheet String to Array.  To "fill in the blanks below this header line", build an array of values from variables Date, Sample Material,etc.  The tricky part is converting each variable into Strings, but you might find the Format into String function helpful, especially as it can put the <tab> or <,> between the string representations of Date, Sample Material, etc.  And from several "line" 1D arrays, you can build a 2D array (making sure the 1D component arrays have the same length) and, "Presto!", you have a 2D text array in .csv format.

It is still a little messy, but a lot better to understand, to program, to modify, than the mess-of-wires you are trying to untangle.

If you have access to it, I highly recommend trying the Report Generation Toolkit's ability to "talk to Excel".  I can recommend some examples, including some here in the Forums, to show how to do what I think you want to do ...

Bob Schor

[GerdW]

Hi svazquez,

---

@svazquez22 wrote:

As it works now, I run my program, it prompts me to name an empty .csv file, then I collect data, then it writes it to that file I preallocate.

---

When you still use my suggestion all you need to do is to provide a path to the FileOpen function…

(It may help to read the LabVIEW help for each function…)

[svazquez22]

Right, but I need to have an already made csv file attached in the path, otherwise Labview will give me this error:

Error 1059 occurred at Open/Create/Replace File in MyVi.vi

Possible reason(s):

LabVIEW: (Hex 0x423) Unexpected file type.

Z:\User\filelocation*

*Labview does not literally say this file path in the error message , but I edited it for privacy reasons.

[svazquez22]

If there is a way to keep the format I included in my screenshot here with one of those more automated vi's and make it so I don't need to have a pre-existing empty spreadsheet file, I would appreciate an example. I also tried using the write delimited spreadsheet vi and ran into some memory issues causing my labview to crash sometimes.

[GerdW]

Hi svazquez,

---

@svazquez22 wrote:

Error 1059 occurred at Open/Create/Replace File in MyVi.vi

Possible reason(s):

LabVIEW: (Hex 0x423) Unexpected file type.

Z:\User\filelocation*

---

There is no filepath wired in your latest VI.

And "Z:\user\filelocation*" is no valid filepath. Even "Z:\user\filelocation" is not a valid CSV filepath, I would expect "Z:\User\Filepath\File.csv".

---

@svazquez22 wrote:

If there is a way to keep the format I included in my screenshot here with one of those more automated vi's and make it so I don't need to have a pre-existing empty spreadsheet file, I would appreciate an example. I also tried using the write delimited spreadsheet vi and ran into some memory issues causing my labview to crash sometimes.

---

As said before: FileOpen and WriteDelimitedSpreadsheet can create files, not just append to existing files.

Your memory issues may come from your weird formatstring/delimiterstring: why do you wire the output of those ArrayToSpreadsheetString to those inputs? Did you read the help for the WriteDelimitedSpreadsheet function?