write to spreadsheet with formatting

[bunnykins]

Dear LabVIEW Forum,

I'm having some difficulties with something that should be fairly simple however the online help hasn't been able to assist me having spent several hours looking.

I'm trying to setup a LabVIEW program to write the following example data to a spreadsheet file (two columns with first row entry Serial number and Item, then underneath this the various data) I intend to expand this to add more data but just need to get the basic formatting principles working:

Serial number    Item

1                          20

2                          40

I have attached my .vi effort to date however I cannot figure out how to get the formatting working (i.e. remove trailing zeros, carriage return or tabs).  If anyone can help out it would be appreciated.

Regards,

[johnsold]

Since you are already generating and formatting strings, just concatenate the strings into one string and use Write to Text File.vi rather than the Write to Spreadsheet File.vi.  That is what we did before the Spreadsheet File VI became available.

Your delimiter is "{backslash}r", not a carriage return.  To set the delimiter to carriage return use the carriage return constant from the String functions palette or set the string constant to '\' Codes Display before entering the data into it.

Connect some temporary string indicators to various strings to see how things will look.  Try both the Normal Display and '\' Codes Display.  It appears that you are getting some spaces which you may not want.

If your Serial Numbers and Item Numbers are integers, use an integer datatype rather than DBL and use the %d format string.

Lynn

[bunnykins]

Hi Lynn,

Thanks for your reply however I didn't really understand your advice?  I tried the \d and \r however this just appends these characters to the end of the data?  My program needs to write to a spreadsheet file (no choice).

Sorry but I'm not an experienced user of LabVIEW just a very frustrated learner.

Regards,

[johnsold]

Before \r will work, the control or constant into which you are typing it must be set to '\' Codes Display.  You do this by context-clicking on the control or constant.  Search the help for 

Backslash ('\') Codes Display

\d does not seem to be a valid '\' code.

A spreadsheet file is a tab-delimited text file.  Most spreadsheet programs will read such files and divide each line into columns (cells) by tabs.  The carriage returns separate rows.  You do not need to use Write to Spreadsheet File.vi to create a spreadsheet file.  If you open the block diagram of Write to Spreadsheet File.vi, you will find that it uses Write to Text File internally.

Lynn

[altenbach]

---

@bunnykins wrote:

I have attached my .vi effort to date however I cannot figure out how to get the formatting working (i.e. remove trailing zeros, carriage return or tabs).  If anyone can help out it would be appreciated.

Regards,

---

I don't see any code that generates trailing zeroes, carriager returns, or tabs.

Just form the final 2D string array and write to the file. Here are some ways, there are infinitely more. 😄

(What is the purpose of your WHILE loop???)

[Darin.K · Accepted solution]

---

@altenbach wrote:

---

I don't see any code that generates trailing zeroes, carriager returns, or tabs.

---

%f used as a format string effectively gives you %.6f, should be %d if no trailing zeros are wanted.

I'll add to the infinite possibilities, with the assumption that you be building up the file over time.  Write the labels once, then append to the file in a loop.  I find this method to be safer then building a string if the program takes a long time to run. 

If you have all of the values at once, follow the other suggestions to build a string and then write it.  A simple way is to remove the For Loop in my snippet and wire the 2D array directly to the second Write to Spreadsheet File.vi.  Without looking, I'll wager something similar is one of altenbach's mods.

[bunnykins]

Darin,

Many thanks for your explanation and example LabVIEW code.  I have tried this and it works fine.  You are correct that I intend to add additional items to the spreadsheet file so your proposed solution works fine. I apprerciate your help.

Regards,

[COSUN]

How do I get rid of carriage returns or end of line spacing whichever it is called while using the write to spreadsheet. I open up my file in excel and the first two rows are correct but than there is a blank row in between each row of data after that.  Any ideas?

[RavensFan]

You'll have to show us your code so we can see what you are doing.  Are you sure you don't have an additional EOL character in your data besides the one that the Write to Spreadsheet File puts in?

[GerdW]

Hi COSUN,

ideas:

- attach your VI for better suggestions and to show, what you have programmed...

- don't write the additional EOL chars to the file...