Macro to interact with database

[fseipel]

Hi,

We wish to automate our ChemStation GC running version B software.

(1.) First I'd like to import sequence table from LIMS on SQL server. Is this best done with a ChemStation macro, with XML import, or with a purchased product from say Labtronics? Does anyone have example code to bring in worklist using the macro language?

(2.) I have macro guide and have started to read it. I'm kind of new to this but it sounds like for writing results back directly to my SQL server, I need to use DBOPEN; does this use SQL authentication or Windows authentication? It sounds like the most elegant way to manage this for import, would be a SQL select to get the sequence table, and another SQL INSERT to write results back to tables. Does anyone have some sample code for this? Or am I better off just reading XML results files into SQL server?

Is there a simple way to make ChemStation post all its results to a SQL server so it can be queried? I am surprised it stores everything as a file with no central DB.

[tlahren]

This Agilent product note says that MSD chemstation rev D.03 can import sequence tables from LIMS in either .CSV or .TAB format.
http://www.chem.agilent.com/Library/spe ... 046614.pdf
I'm not sure about GC Chemstation rev B but usually things like this are compatible if you know what commands to use in a macro. I know I have seen other references in the past that discuss this. The guys at Agilent should know how to do it very well. I would give tech support a call. They always help me out (in the USA anyway, I have heard their support in other countries is not so great).

We used to compile our results in a quant.csv file in the .D folder using the QT,,"C" command. This makes for an easy format for importing into any LIMS database. However we had a problem with the number of significant figures it prints int he .CSV report so we had to edit the macro that generates it. Let me know if you need any direction in this and I might be able to remember how/where to edit it.

[tlahren]

I just found this manual for Agilent Chemstation Plus. It talks about importing XML info from a LIMS system an other LIMS related database information. It may be of some help to you as you seem to know quite a bit more than I about databases.

http://www.chem-agilent.com/pdf/ChemStation_XML.pdf

[fseipel]

Thanks, I have read XML guide, enabled XML, main problem now is it simply says 'Nothing Imported' when I attempt to bring in the worklist. I have an inquiry with Agilent Support on this. If my employer permits me to, I'll post my code to generate the worklist file from MS Access once they troubleshoot my issue. Does anyone have an MS Access solution/LIMS they wrote which they'd like to share? I'm building one that will be ChemStation-centric. This open-source (MS Access) approach seems ideal as it will be infinitely customizable. I've worked with the data in REPORT0x.XLS files in past but this should be a significant improvement. I am surprised the ChemStation software does not simply have a built in database where results are stored, instead of storing in the individual files. If I can't get XML working I'll try CSV.

Thanks again,

Frank

[fseipel]

Does anyone have any insight into this? I still receive 'Nothing Imported' when I attempt to import an XML worklist, can anyone post an example worklist?

[fseipel]

This problem has finally been resolved. I wanted to document this.

1.) In closer reading of the manual, it briefly mentions C:\Chem32\CORE\xml2csv which parses the XML file. I had run this before to confirm my MSXML version was correct. I did NOT realize that I could run this directly from the command line with arguments. It provides much more verbose error messages than 'Import Worklist' from the GUI, e.g. 'System does not support the specified encoding'.

2.) I had previously copied/pasted the PDF into Notepad. Problem is, in Agilent's PDF Appendix sample worklist, they have a space before the encoding which if pasted into Notepad, causes XML2CSV to ALWAYS throw the 'System does not support the specified encoding' regardless of what format the file is saved in within Notepad, e.g. <?xml version="1.0" encoding=" ISO-8859-1"?> is what the PDF file contains. I had actually tried saving in different formats in Notepad, but because of this issue, none of them worked since the space created an invalid encoding.

3.) If anyone else has any issues with this, try running xml2csv c:\worklist6.xml C:\Chem32\CORE\WORKLIST.XSL in a DOS window where first argument is worklist source file and second is default style sheet path which will display the verbose errors.

4.) If saving a test worklist in Notepad save as ANSI if you use the encoding set in connectivity guide appendix (ISO-8559-1). All of this is rather confusing because Notepad does not use the same nomenclature as XML to describe most encodings.

6.) If encoding is set to UTF-8 and file is saved as UTF-8 in Notepad, it will also import successfully.

7.) Since Unicode is identical to UTF-16, if saved in Notepad as Unicode, with encoding set to UTF-16 in first line of XML header, it will also import without errors.

8.) I have now been able to generate the worklist dynamically using VBA from MS Access. Since I also am able to import the XML results into Access, I am well on my way to building a LIMS.

I'm not currently planning on doing anything in the ChemStation macro language.