Database "read all data.vi" not able to read row contents

[oneFranck]

Good day,

I'm trying to read a table from an Excel Database, with the NI VI "Read all data.vi" provided as an example VI.  The VI is slighlty modified to have the file dialog box wired to the open connection VI.  This file dialog provides the connection information to the Excel database.   So far only the column names are showing, but not any row content, as you can see in the screenshot - front_panel.

What the VI does :

- finds the table name(s)

- finds the column name(s)

Problem with the system:

- can't read row data.

The Excel file in question is really really simple.

here is the content of the dsn file:

[ODBC]
DRIVER=Microsoft Excel Driver (*.xls)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
ReadOnly=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=excel 8.0
DriverId=790
DefaultDir=C:\MPBC\My Database
DBQ=C:\MPBC\My Database\SIMPLEST.xls

Any hints on how to get row data?

Thanks in advance.

---info---------

LabVIEW 8.6

Excel 9.0  or 2000

Win XP pro SP3

FB

[Bob_Schor]

While I have a lot of experience using LabVIEW to open Excel Workbooks (.xls or .xlsx) files, I've never used Excel as a Database and am not familiar with the VIs that you describe.  It is easy enough to read the Worksheet contents into LabVIEW, with or without the Headers (and to read the Headers separately) using the Report Generation Toolkit.

Something that I noticed in looking at the pictures (please, submit executable code, not pictures -- if we have an idea to suggest, we usually will try it out and shouldn't have to try to reproduce your code "by hand" ourselves) was that you opened the file LOADING BATCH.xls, while your dsn file specifies SIMPLEST.xls.  Not sure if this is relevant ...

Bob Schor

[oneFranck]

Yes in this case, the control in the picture was unwired.  Now  I decide to try it with connection information file, and with connection string...

I have left the VI with the connection information string in the control.  File is also attached ( just wire dialog box it in same input, it is polymorphic)

This is what the control should contains (I build this string using the wizard when checking True prompt):

DSN=Excel Files;DBQ=C:\MPBC\My Database\SIMPLEST.xls;DefaultDir=C:\MPBC\My Database;DriverId=790;MaxBufferSize=2048;PageTimeout=5;

Code is attached, and should not give an error.

It is failing with both options - can't read data from file.

Thanks,

Francois

[Bob_Schor]

Well, as I said, I can't help with treating Excel as a DataBase, but here is some code to read your Excel file.  I actually read it twice, the first time returning all of the data in a single array (you can split it yourself into Header and Data), and the second time I read in a single row into Header (the Read returns a 2D array, which I turn into 1D) and a second Read for the rest of the data.  To read the rest, I first figure out the "next" row (one past the last row, so I decrement it to get the last row) and specify all the columns).  The 100 msec time delay between Excel Reads is to give Excel a chance to "cool down" (trust me, without the Delay, Excel will throw an Error).  Note that this is a Snippet -- you can drag it into a LabVIEW 2014 Block Diagram and it will become LabVIEW code.

Hmm -- I just noticed that you seem to be using LabVIEW 8.6, more than 5 years old.  I don't know if this version of the RGT is available in that older version.

Bob Schor

[mikeporter]

You can use the ADO database drivers to access Excel spreadsheet files, but there are some restrictions. The worksheet name becomes the table name. The worksheet has to be laid out is a tabular form, no empty columns no empty rows.

Whatever you do don't use the database connectivity toolkit for this. The are easier ADO drivers that I wrote available on the forum and via my blog.

Is there a reason you are doing it this way? If you need a database JET is easy to use and it comes as part of Windows.

Mike...

[oneFranck]

Bob,

I need to develop an applet that uses a database, first we choose to go with Excel, then maybe move to another system later.  So thanks for the code, but it's ok.

Mike:

"The are easier ADO drivers that I wrote available on the forum and via my blog.

Is there a reason you are doing it this way? If you need a database JET is easy to use and it comes as part of Windows."

Thanks I'll check it out.  I'll check the driver you made  on your blog.

FB

[mikeporter]

One other thing to keep in mind is that if you have now, or have ever had the Datalogging and Supervisory Control (DSC) Module installed on your computer, you will also have an instance of the Express version of SQL Server running in the background. It is installed when you install the toolkit, but it isn't removed when you remove the toolkit -- even if all you did was install the demonstation version (hint, hint).

Mike...

[oneFranck]

Mike:

"Whatever you do don't use the database connectivity toolkit for this."

Why?

Unfortunatly I have LabVIEW 8.6... so I could not open your files from here:

http://svn.notatamelion.com/blogProject/testbed application/Tags/Release 7

Frank

[mikeporter]

OK, there is an older version on the forum. Check this thread:

http://forums.ni.com/t5/LabVIEW/Extracting-one-column-from-Database/m-p/547276#M257531

There are versions going back to V7.1! Also a good discussion on why not to use the DCT.

Mike...