Move data from Citadel to SQL Server 2005

[CletcherJ]

I wish to move data from a Citadel database. I have tried to use an ODBC connection to a Citadel database with little success. I can connect and write queries using Microsoft's Excel, but what I want to do cannot be done in Excel (due to Excel's row and column limitations and because I need a "heavier" lifter than Excel) . The data-tag names are longer than MS Access will accept and Visual Studio Professional 2005 connects but no queries are successful. MS Access can open the ALIAS table.

What I want.

I wish the UTC date and time, Data-Tag Name, and data values between start and end times (logical run) for data contained in the RAWDATA table of the ODBC connection. I need to repeat this for the 400+ data-tags in the Citadel database.

An ODBC connection is inherently slow and inefficient and thus isn't the connection of choice. However, I don't know of any other means to connect. I have years of data from which to extract runs on multiple DAS Citadel databases.

Is the Citadel database the best source for this data extraction or is there a "native" LabView database that this extraction process might use for more efficient data extraction?'

Is there a product marketed by NI that will extract this data to SQL Server or a delimited text file?

[Amanda_H]

Hi,

I've found a few KnowledgeBase articles that I think might be helpful for you:

Using Tag Names in MS Access To Retrieve Data from Citadel
Using Data Transform with MS Interface and the Citadel ODBC Interface

Here is a DeveloperZone article that describes more about this as well.

Hope this helps!

[CletcherJ]

Thanks Amanda:

Today, I was able to connect from within MS Access via an ADODB connection to the ODBC Citadel database. That is working well so far and seems to be reasonably fast--returned 3 recordsets from the RawData table having about 370; 1,400; and 460,000 records in a little over 16 seconds from a PC with medium power (cpu configuration and RAM availability). The recordsets were ordered but no "WHERE" clauses were involved.

I'll use these recordsets to vet the algorithm I am developing to identify "runs"--there are 19 complete runs and 1 incomplete run at the end of the Citadel database. The next Citadel database repeats runs 18 and 19 and then has run 20 through whatever. The algorithm currently successfully finds the UTC run start date/time. Tomorrow, I'll work on the run end date/time portion of the algorithm and if all goes well Monday will be the big test of picking the data between the start and end times for each run for each of about 370 data tag names.

Thanks, again.

[Amanda_H]

That sounds great! I wish you luck with your application.