Extracting fractional data using the "Read All Sheets" VI.

[Mac303]

Greetings....

I want to extract various data types from an excel files that contain dates, fractional data, and Alphanumerics using the Read All Sheets function.

I'm using %.2f for the number format as an input to the "Read All Sheets" XLR8 function but my fractional data is not reported but rather a constant time (1:00:00.000 AM) is reported.

How do I accurrately extract all my data types using the Read All Sheets function?

Thank you.

Jason

[Mac303]

To clarify, when I run the Entire Spreadsheet XLR8 example along with the supplied excel file, the data doesn’t match nor is it in the correct format. See two images below.

[Peter-daq.io]

Hi Jason,

could you maybe attach a small sample xlsx file?

The read all sheets function uses the Read Area (All Types).vi and currently only supports standard numeric, string, date and formula cells. The fractional data seems to be interpreted as a date, that's why it would be great to have an example.

Thanks

Peter

[Peter-daq.io]

Sorry, could you please re-post the pictures? They appear to be broken. Again, a small xls file would be very helpful, too. When I read fractional formatted cells, they are returned as decimal numbers.

Peter

[christopherdouglas]

I am having a similar issue. When I attempt to read a sheet with strings and numerics, all the numerics get converted to a timestamp. Any solutions to the problem?

I am using the Read Entire Spreadsheet example with no formating into the subvis.

Also when I open the spreadsheet in excel the format on the numerics is General but even when I change to Numeric it acts the same.

In excel I see:

After Reading all sheets with XLR8:

I have attached a sample .xlsx where the issues is also happening.

[Hans-Joachim]

Hi christopherdouglas,

thanks for your detailed reply!

We took this issue on top of our bug list which will be fixed in XLR8 2.1!

We think that this bug is caused by a formatting issue with the input "number format" of the "read all sheets.vi" and the "regional and language options" differences in windows between Germany and the United States.

We will keep you up to date about the solution to this problem!

In the meanwhile if you found a way to make it work untill XLR8 2.1 please post it here for customers with a similiar issue.

[Mac303]

Hi christopherdouglas et al.

I made the read a sheet VI work by converting the number format for all cells in each sheet to a text format.  When I did this the reading the all sheets worked just fine as well as all other XLR8 VIs.  In my application I'm using a template to generate excel data files/sheets so I have the freedom to define the format.

BTW, XLR8 is a good product.  I like it much better compared to ActiveX based VIs for communicating with  excel.  Using XLR8 I don't need to worry about launching excel to extract data or manipulate excel files.   Hope this helps.

Jason

[christopherdouglas]

Hi Hans and Jason,

Thanks for the replys. I attempted to change the number format for all the cells in excel to a text format but did not see any results.

I have attached is sample .xlsx with the all the data having a text format.

Thanks,

Chris

[Peter-daq.io]

Chris,

unfortunately I cannot reproduce the error on my system at the moment. I saw the issue once before but I cannot pinpoint it. On my system everything works fine.

Could you please try to re-install XLR8 using the VI Package Manager?

Which version of Windows and which version of LabVIEW are you using?

Thanks

Peter

[parissa_wall3]

Unfortunately I have the same problem when I want to copy my data from one sheet to another, I use "Read Current Sheet" which cannot detect fractional numbers and returns the 01.01.1904 instead. when is the expected data of 2.1 release?