Saving to a existing excel template (selecting specific cells)

[dkim1017]

Hi fellows, a new comer here to the community eager to learn.

I have a .txt file with 2 columns of data:

1   11

1   22

1   33

2   44

2   55

2   66

in this format.

I want to append this (only the right column) to an existing file and define to which cells is should save, for example C6:C11.

I've tried several approaches, but haven't been able to deliver.

As far as obtaining the right column only, I should parse through the file and create another file with just the right column.

Then, when appending to the template, I wouldn't have to worry about separating the two columns.

I can append using the append block in the palette, but how should I go about defining which cells to append to?

I don't need the user to select an active cell, I already know which cells I want.

Thank you all for your help!

[aputman]

Using the report generation toolkit....

You can specify a path to your template on the New Report VI.

[Bob_Schor]

There are two different (but related) tasks here.  First, you need to read your text file and produce an array of values to save.  This has little to do with Excel, as your data come from a Text (.txt) file.  One decision you'll need to make is whether to create a Numeric array or a String array to represent your data.

The second question is, given an array, how do you save it to a specific place in an existing Excel Worksheet.  Here, again, there are several things to consider.  The first question is are you concerned about existing Excel data, i.e. do you want to "write into Column 3" or "write into the next Column"?  Are you writing your data as new Rows or new Columns?  [Note I'm assuming, based on your post, that you are only concerned with a 1D array, i.e. either a single Row or single Column].  You also need to consider whether you want to write Numeric or String data into Excel.  Note that the example above uses Excel Easy Text, which (a) requires that you output the array one element at a time and (b) restricts you to strings (at least according to the LabVIEW Help).  An alternative is to use Excel Easy Table, which allows either Numeric or String input, but needs a 2D (not 1D) array -- the easy way to fix this is to pass your 1D array through a Build Array function to give it a 2D "appearance" -- it will be a row, but you can use Transpose Array to turn it into a column.

Bob Schor

[dkim1017]

aputman,

I wanted to try this, but I got stuck when trying to make the "row" block.  How to I go about building it?

Also, I'm assuming that the 5 indicates that data start at the 5th row.  What do the -1 and 2 represent in the cluster box?

Thank you for your input.

[dkim1017]

Bob_Schor,

My data file is .txt file, and I read from it using "Read from Spreadsheet" block in labview.  I believe it output a string array, but the output data type can be either or, as long as it gets the job done.

There is existing data in the template, but I want the program to overwrite onto it.

For instance, the template comes pre-filled with headers and indices that take up the first 5 rows, and some of the columns.

Therefore, I know exactly which cells I need to overwrite to every time. (e.g. C5:C15)

I am writing my data as a new column, and I have a 2D array, but I only want to make use of the second column, since the first column is just indexing numbers.

As mentioned above, the type of data can be either or, as long as it is appended to the right cells.

I hope this clarifies all the important aspects.

I will try to put up some screenshots to share what I've done and where I am still getting stuck.

Thank you for your help.

[Bob_Schor]

If your data are not too numerous, it might be simpler and faster to use a "Brute Force" method.  I'm going to assume that your data file has a row of Header information followed by N rows of Data Values.

1. Read the Header row into "Header" and the N rows of Data Values into a 2D Data Values array.
2. Make whatever changes/additions you need to Data Values.  I'm assuming you are replacing existing data in columns (and not adding or subtracting columns), replacing entire rows, and/or adding new rows (with the same number of columns) at the end, so that Data Values, formerly N rows and K columns, now as N1 rows and K columns.
3. Overwrite the entire data file with a new one having the same Header row and the new Data Values array.

Note that this completely avoids the problem of "How do I replace a cell in an existing Spreadsheet" -- you simply replace the entire Spreadsheet!

Bob Schor

[aputman]

---

@dkim1017 wrote:

aputman,

I wanted to try this, but I got stuck when trying to make the "row" block.  How to I go about building it?

Also, I'm assuming that the 5 indicates that data start at the 5th row.  What do the -1 and 2 represent in the cluster box?

Thank you for your input.

---

Ok, you said you want to write into C6:C11.  C is the 3rd column but they start counting with 0.  So the "2" constant is telling the VI to write to the 3rd column ("C").  The -1 normally means the whole row but i am overwriting the row value every iteration of the loop, starting with a 5 on the first iteration of the loop (which is actually row 6....starts with 0).  

So the first iteration, i am writing a value of 11 to (5,2) which is actually C6

2nd iteration, I am writing a value of 22 to (6,2) which is actually C7

etc.