Read a particular column in excel spreadshee​t

[XxLinkxX]

Hey guys i spent some time learning the index array but i ran into an issue. Basically what i am trying to do is take my reading from one sheet and copy it over to another and convert it. The problem is that i have two type of data. my first column in Time which uses a string. The other two columns are type double. Im trying to use index array but for some reason i'm not able to get the whole column copied over. It's only the first cell. I've attached my program as well as the sheets to help. The two images show the original sheet i want to copy from, and the new sheet with the results. As you can see it just copies the first cell into the entire column.

I am able to configure the program to copy the columns over but the numbers are converted to text in excel, which is a problem with my equation.

My other solution is that i can have the program read two sheets where time is on one and the readings are on another. Then i can read/write using the desired output(String/double).

However i want to maintain only one sheet. Any thoughts?

[Gregory]

Have you looked into the report generation toolkit? I think it would greatly simplify this.

[Bob_Schor]

There is a Report Generation Toolkit for Microsoft Office that is included with LabVIEW 2014 and LabVIEW 2015, but is an "add-on" in LabVIEW 2012.  The RGT uses an Object model of the LabVIEW Report, and makes it much easier to read and write Excel Workbooks and Worksheets without needing to wade into the complexities of ActiveX.  So my first piece of advice is to get your hands on the RGT, if possible, either by buying/acquiring/installing the LabVIEW 2012 version or acquiring and installing LabVIEW 2014 or 2015.

Now, I'm still not entirely certain I understand what it is you are trying to do.  I see pictures (I have a well-deserved reputation on the Forums for saying "Don't attach pictures" -- have the Excel file instead would be much more helpful) of Sheet, showing three columns formatted as Time and as numeric (I presume), and of NewSheet, showing 6 columns (one being blank) starting in Row 5, with Column A formatted as Time (with some possible errors, which might "disappear" if we had the Excel file and widened the column width) and as numbers.

What are these?  Is one an Input, one an Output?  Are you trying to read one and extract data?  Are you extracting the Data into a Cluster (Time, Value 1, Value 2) or into an Array (Numeric or String)?  What do you want to do with the Data (which might impact whether you read it as text or string)?  What is the relationship between the data format shown in Sheet and in NewSheet?

Since I don't really understand what you want to do, I can't easily comment on how to do it.  But I can say, with confidence, that manipulating an Excel Workbook, particularly using the Report Generation Toolkit in LabVIEW, is fairly straight-forward and not too difficult.  There is a "trick" in reading Date/Time data, as LabVIEW and Excel store these data in differing (internal) formats, but there are ways to work around this, once we know (better) what it is you want to do.

Bob Schor

[XxLinkxX]

Hey Bob,

So basicaly my first sheet is raw data acquired from a test station (csv file).

It consists of a time stamp and the values given at the time. What im trying to do is, by using a template,  i want to convert those reading by using an equation. But since the time is in string format, i cant simply copy all the data as string, so i have to convert the other two colums as a double. Basically the program will do conversion into a proper excel sheet. i will be adding graphs and tables as well, but i wanted to figure out how to get the data to be copied properly. Hope this makes it clear.

[Bob_Schor]

OK, attach a sample .csv and .xlsx file.  Create the .xlsx by opening the .csv (with Excel, for example), manipulating the rows and columns as you would want the output to look, and saving it in .xlsx format.  If, when you do this, the Time column gets "messed up", see if you can reformat that column using a "Time" format, or, if worst comes to worst, create those entries yourself "by hand".

With these in hand, it should be fairly simple to code up a routine that will open the .csv (using either the RGT or Read from Spreadsheet file) and create an output .xlsx file in the format that you desire.  Note that if you want blank rows and column headers, be sure to show them in the sample .xlsx file that you attach.

Bob Schor

[XxLinkxX]

Hey bob, i beleve i tried that initally, but since it was tidious to make a time chart by hand after every test, i added the time stamp so after the test was done i can simply convert the file with the conversion. I'm sure i am close. after i index the array and convert to a number i do get the column. I'm just not sure how to do copy it over to the new sheet.

[Gregory]

Hi Link,

He is not suggesting that you do it by hand every time, just do it once so that we can see what you want the resulting Excel file to look like (and the corresponding raw data).

[Bob_Schor]

I just want to see a sample (it could be as short as a dozen lines).  What I'm proposing to do is to show you how to do something, but without your data, without knowing your input and your output formats, it doesn't make sense for me to do anything.

Bob Schor

[XxLinkxX]

Hey bob,

I'm sorry i misunderstood. I attached a sample of reading, and how i want it to look. If thats what you meant. 

[XxLinkxX]

Thanks Greg for the heads up.