Read a particular column in excel spreadshee​t

[XxLinkxX]

Perhaps i can use your logic with the read excel. I'll mess around with it and get back to you. Thanks Greg, i do appreciate you taking your time to help me. Much appreciated!!

[ben64]

The RGT would have been helpful in this situation but since you don't have it you'll have to use ActiveX.

You seem to overcomplicate things a lot, why don't you just open the csv with Excel and let it handle the automatic conversion of numbers?

After, you just have to add new ranges that contains the conversion formulas and finally to save the result as a new xslx workbook.

NB: You were not using the formulaR1C1 node correctly, see my code for how to use it.

Ben64

EDIT: The advantage of using formulas over converting in LV is if you want to modify data values all is updated automatically.

[Gregory]

Ben to the rescue!

[ben64]

---

gregoryj a écrit :

Ben to the rescue!

---

Spoiler

Lol! 😊

[XxLinkxX]

Thanks Ben,

I wasnt sure what i wanted to do with the file, thats why i wanted to open a seperate one, so i can edit it, add graphs, charts, etc. Actually i forgot to mention, the excel convert/code is coming from my data acquisition program. So basically after its done recording  it will automatically go to excel convert part of the code and make the new sheet with the conversions.  Still learning, since i am new. but it always helps to learn different ways of doing things.

My next questions is, the conversion has more to it. Hence why i over complicated things. perhaps?Basically i have the new excel sheet take my readings and convert it using that equation, however i have a if loop in my equation(condition). my condition for the first reading is if its over 10.5V, the reading will say "Null". For the second reading, if its less than 0,  the conversion will equal 0. This is because the data aquisituion picks up negative voltage. This is why i use a template. i preset the template with the formula and the rows/columns. that way once the test is done, it will automatically convert it. Hope this is clear.

Thanks

[XxLinkxX]

"nvm:" Had to check off the error boxes and make sure it had a green check mark.

[ben64]

---

XxLinkxX a écrit :

Thanks Ben,

I wasnt sure what i wanted to do with the file, thats why i wanted to open a seperate one, so i can edit it, add graphs, charts, etc. Actually i forgot to mention, the excel convert/code is coming from my data acquisition program. So basically after its done recording  it will automatically go to excel convert part of the code and make the new sheet with the conversions.  Still learning, since i am new. but it always helps to learn different ways of doing things.

My next questions is, the conversion has more to it. Hence why i over complicated things. perhaps?Basically i have the new excel sheet take my readings and convert it using that equation, however i have a if loop in my equation(condition). my condition for the first reading is if its over 10.5V, the reading will say "Null". For the second reading, if its less than 0,  the conversion will equal 0. This is because the data aquisituion picks up negative voltage. This is why i use a template. i preset the template with the formula and the rows/columns. that way once the test is done, it will automatically convert it. Hope this is clear.

Thanks

---

It is usually a good practice to separate acquisition from data processing. You could create a template with formulas and conditional formatting and at the end of the acquisition simply copy the converted csv file range into a range of the template and everything will then be generated automatically.

Ben64

[XxLinkxX]

Hey Ben,

Thats what im trying to do. However when i copy over the data, since time is a string and the rest are integers, i have to convert one or the other to match. When i do that, my equations doesnt pick it up. So when i copy everything over as a string, my numbers are in text format. so my formula doesnt pick them up. If i copy them over as a double, the time stamp is not properly copied over. thats where i am stuck. I mean i have a solution, but its not the one that i like.

[Bob_Schor]

Here's how to Have Your Cake and Eat It, too (after a fashion).  You are currently saving Time as the "Time of Measurement", which is a TimeStamp, always a problematic thing with Excel.  Instead, save "Seconds since Start of Experiment" as a Dbl.  It will, of course, start from 0.  

So what if (for some reason) you want "Date and Time" instead of "Seconds from Start"?  Well, you need to write down a Start of Experiment value in a Cell somewhere, and it needs to be in text format.  However, you can do this in a separate "Write one cell" command, so when you write your array of data, it can still be all Floats.

Now all you need to do is write an Excel Formula that will tranform Elapsed Seconds in to Sample Date/Time.  This is simply <Elapsed Seconds>-<StartTime/86400> (where I'm using <> to stand for the particular Excel cell where the data are saved -- don't forget that StartTime lives in a fixed cell, and the division by 86400 is due to LabVIEW times being in seconds, while Excel's times are in days).

Bob Schor

[ben64]

Do not use Spreadsheet String to Array.vi. Open the csv with Excel as I did in a previous post. Use the Copy method to copy the UsedRange to the Clipboard. Then open the template and use the Paste method to paste the copied data to the selected template range.

Ben64