07-22-2015 10:58 AM
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
07-22-2015 11:26 AM
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
07-22-2015 12:48 PM
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
07-22-2015 09:37 PM
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
07-23-2015 12:27 AM
07-23-2015 12:42 PM
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
07-23-2015 01:23 PM
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...
07-24-2015 02:54 PM
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
07-24-2015 03:24 PM