Writing Tab Delimited String to Delimited Spreadsheet

[CastleWorks]

I have  a tab delimited string I would like to write to a spreadsheet the "Write Delimited Spreadsheet.vi"

The VI only accepts 1D or 2D array data, and I have struggled to figure out how to convert the string data to an array.

In my snippet below I am writing the same string to a txt file and that works fine, and I can then open that text file in a spreadsheet without problems, but I would prefer to write it directly to a spreadsheet to begin with.

[aputman]

Spreadsheet String to Array on the string palette.

[CastleWorks]

I tried that and I get an error when opening it with Excel. Excel does not have any problems opening the txt file I am writing the same string to.

Not sure what I am doing wrong on the spreadsheet VI that Excel does not like.

[aputman]

Writing to a spreadsheet file does not mean Excel, at least in the version of Labview that I use.  I don't know if newer versions are capable of writing to an XLSX file as a default.  Change the file extension to .txt and write it.  When you open the text file with Excel, it will use the tab delimiters to create columns and rows.  

[BowenM]

If you want to write directly to an excel file you need the report generation toolkit.

The better solution here is write to a csv file (comma delimited) and name it .csv.  Excel detects csv files as a native format and they will open correctly in excel.  It does not do this with tab delimited files.

[aputman]

I should also add that if you are using comma delimited data, you can write that to a text file and save it with a .csv extension and that is a commonly recognized delimited file for use in spreadsheet applications.  Most people assume Excel when they think of a spreadsheet file because we are all so familiar with Excel and use it for everything (at least I do).

But writing to an actual Excel file is usually not the best idea because Excel is about the formatting and display of the data, whereas a text file or CSV file is only about the actual data itself.  Unless you are tasked with providing a fully featured application with reports and such, IMO it's best to stick with text files.

[aputman]

---

@BowenM wrote:

If you want to write directly to an excel file you need the report generation toolkit.

The better solution here is write to a csv file (comma delimited) and name it .csv.  Excel detects csv files as a native format and they will open correctly in excel.  It does not do this with tab delimited files.

---

Tab delimited text files do open properly in Excel.  They aren't natively recognized, as you say, but if you right click -> Open With...Excel, it will properly create rows and columns.  

Also if you go into Excel and open the text file from the Open dialog, you'll get a text import wizard where you can do basic column formatting and you can skip importing certain columns, if necessary.

[Bob_Schor]

Blame Microsoft!  Excel allows you to save an Excel Worksheet in "text" form as a Comma Separated Value (.csv) file, and gives it an "Excel-like" icon.  If you see a file with an Excel Icon, you need to look closely at the Icon or else have the file's extension visible to see if it is a text file (.csv), which any old program (even LabVIEW) can open and parse, or a proprietary Excel-formatted .xls or .xlsx file, which needs More Horsepower (the RGT does a pretty good job of reading "true Excel").

Years ago, when one said "Spreadsheet", everyone thought "Lotus".  Now, everyone thinks "Excel".  But a Spreadsheet (an arrangement of data in rows and columns) is not the same as an (Excel) WorkSheet.

Bob Schor

[CastleWorks]

Changing to a CSV extension and changing this tab constant to a comma allowed Excel to open it, but the formatting is not correct

I have both text strings and numberic string...only the numberic strings showed up in the csv file, not the test strings

Thoughts?

[aputman]

In order to use CSV, your spreadsheet string has to have commas as the separator, rather than tabs.  And you instruct the Spreadsheet String to Array to look for commas.  However you have to consider your data because if your data has commas embedded in it (i.e. a datecode Feb 8, 2014), you will get columns where they may not be intended (between the 8 and 2).