Can I Do an SQL Query to a CSV File From Lookout?
Primary Software: Lookout
Primary Software Version: N/A
Primary Software Fixed Version: N/A
Secondary Software: N/A
Problem: I am saving information to a CSV file using the Spreadsheet object in Lookout and I would like to import the information back to a DataTable in Lookout using ODBC and SQL. What steps do I need to follow to set up the connection correctly and what information do I need to provide for the Connect String and the SQL command fields?
Solution: First, make sure that you have created a correct data source (DSN) for your text files. Here are the steps you need to follow:
- In Windows 2000/XP, go to the Administrative Tools in the Control Panel and open Data Sources (ODBC).
- In the ODBC Data Source Administrator window, go to either the User DSN or the System DSN tab. A system data source is visible to all users in your computer, while a user data source is only visible for a specific user in that current computer.
- Add a new DSN by clicking the Add... button. A new window will open; select Microsoft Text Driver (*.txt; *.csv). Click on Finish.
- In the next window, type any name you want to use for this DSN, for example "CSV Files" or "Text Files". The description is optional.
- Click OK once you are done. You must see the DSN you just created in the Data Sources window.
Once the DSN is set up, you can configure the query from Lookout:
- In Lookout, create a new DataTable object.
- In the Revise table window, select ODBC for the file type.
- In the Connect String field, type the DSN name and the DBQ. It should look something similar to this:
"DSN=Text Files; DBQ=C:\Program Files\National Instruments\Lookout 5.1\;". The DSN name needs to be exactly the name you assigned when creating it from the ODBC Data Source Administrator; the DBQ field provides a path to where the CSV files will be located. Notice the semicolon (;) at the end of both the DSN and the DBQ. They are really important and do not forget to include them. Also, the DSN and the DBQ do not need to be hard-coded: you can create your own logic so the directory will change depending on the value of some objects you have in your Lookout's Control Panel.
- Type your SQL query in the SQL Command field. It should look something similar to this:
"SELECT * FROM monthly.csv". In this case, monthly.csv is the name of the CSV file where your information is located. The SELECT statement will interpret it as if it was the table name. Remember that this field does not need to be hard-coded either, you can have some logic that will allow you to select the CSV file you want to read from. Also, you could have some relative path selection for the table name, for example "SELECT * FROM \Lookout\2004\sep\21\dailylog.csv".
- To execute the SQL query, you can attach a Pushbutton to the executeSQL data member, so when you press it it will retrieve the information from the CSV file. The value of each cell will be stored in the A1-VI10000 data members of the DataTable object.
For further examples and documentation in the DataTable object, please refer to the links below.
Related Links: KnowledgeBase 39AEPP9U: How Do I Format SQL Commands in Lookout? KnowledgeBase 2AKD4DUJ: Syntax For SQL Commands For The DataTable And SQLExec Objects In LookoutDeveloper Zone Example: Using DataTable to Retrieve Data from an ODBC Data Source (I) - ExampleDeveloper Zone Example: Using DataTable to Retrieve Data from an ODBC Data Source (II) - ExampleDeveloper Zone Example: Different methods of addressing/browsing DataTable cells (direct method, cursors, dynamic symbolic link) - Example
Attachments:
Report Date: 09/23/2004
Last Updated: 09/14/2007
Document ID: 3DM8GUUW
