Need a Macro to export Data to XML format.

[cptcrusty1]

PREFACE - I am not a chemist, or engineer, I am a geek that was hired to get ChemStation to play nice with a LIMS system. I figured out that ChemStation will export to an XML file which is 90% of the solution to my problem. The problem is the XML file doesn't have all the data points I need. I also need to export the Sample ID, Date and Time the sample was taken ( I believe this is input by the user when initiating the analysis in the GCMS). There are other data points I need, but this is a start.

Any help is greatly appreciated.

Thanks
Crusty.

[Csaba]

Hi,

Isn't it strange that the default reports formats are as bad as they are?

It was a time ago I played with designing custom reports in ChemStation A6 to get all informtion I wanted in the report (but then with some unanticipated drawbacks concerning the chromatogram itself). I hope you have a newer and more powerful version of ChemStation with which you can 1) design a custom report with all your analysis details you want reported and 2) then genrate the XML report file from it. I hope it helps.

[krickos]

Hi

Unless you can get help here. also consider using their customer support. In the past we had them make custom macros for us when the standard ones was not good enough.

[Tod]

Hi, i am also go my working with lims,is your gcms version with MSD chemstation?at the very begin we want to export report with XML format, we give up as it can not contain the data we want, then we turn to custom report and export the XLS format, what we need to do is creat a stript to read this exported files in LIMS.

[cptcrusty1]

The LIMS system we're using is based on MS Access. The Access version is 2000. I've written some code that will prompt the user to navigate to the xml file they want, then prompt for some additional information (since it's not in the xml file) and then import the file, calculate some results, and finally save. Our ChemStation is with MSD as well. If I had the time, I"d learn the macro language and write the custom script, but for now, I'm just doing what I can in MS Access.

[lmh]

important point in any interaction of instrument software and LIMS is which way do you want the data to flow? You talk of importing instrument information into LIMS, but the alternative is to get into the habit of preparing your sequence information in LIMS, and importing it into Chemstation... there are advantages and disadvantages both ways.

[cptcrusty1]

@lmh--You lost me.

I'm just a geek working code on contract for a customer. They have an MS Access Based LIMS application that does not interface with ChemStation. The chemists are printing a list of results from their GCMS analysis and manually typing them into a spreadsheet which is then manually stored in LIMS. I'm just simplifying the task. I have a partial solution in place, but the ultimate solution would be for ChemStation to export an XLS or XML file with the information I need already in place and saved with the sample ID as part of the file. Its a simple matter to build an automated server side process to scan for those files and import them automaticly. Without the missing data points, I still have to have the users input some information manually, albeit, they do it 1 time, not 1 time for each compound on the result which can be in the hundreds. When I'm done, what currently takes up to 4 hours daily, will take less than a minute. I'd say that's an improvement.

[richiekichi]

You might have to persuade the chemists you work with to change the way they process their results (batch processing for example). Whatever software they use will most likely have the option of exporting data to a workable format such as an xls or csv. Once in excel, you can write macros to satisfy your every whim.

[Karen01]

@lmh--You lost me.

I'm just a geek working code on contract for a customer. They have an MS Access Based LIMS application that does not interface with ChemStation. The chemists are printing a list of results from their GCMS analysis and manually typing them into a spreadsheet which is then manually stored in LIMS.

I am chemist/chromatographer who is just learning ChemStation, but who does some coding, and (many years ago) was involved in a LIMS installation and wrote a module extending that LIMS.

For a good workflow ideally samples should first get logged into a LIMS and have the tests assigned there... That way it is possible to track the status of all samples once they come into the lab and they can be assigned to specific product, project or study for reporting/review/comparison to specs/statistical purposes.

When it comes time to do the chromatography, a 'worksheet' gets transferred into the CDS somehow... When there is no direct link the simple solution is for the LIMS to print a paper worksheet with the sample ID's assigned there as barcodes, and enter them into the sequence 'manually' via barcode reader. This still a timesaver, and ensures that the ID's entered will be the ones the LIMS expects to link the data.

Not sure if you are or have addressed that part. If the lab is not doing that, you can just have the samples 'originate' from the CDS export. In that case any other information (product, project study etc) would either have to be entered into the LIMS afterwards (ugh) into the Sequence in some existing or custom fields as part of the export.

I am thinking of doing something like the latter ... I work for small startup that really does not need full blown LIMS (nor would pay for one) and i like doing this type of thing as i miss the DB functionality of Empower and in the long run it would make my life easier.

What i'm thinking of is getting data out of Chemstation and transferring it to an SQL database, so I can make data available immediately, preferably through a web interface that can query the DB.

What i envision is having Chemstation produce text files/reports of some sort (be they XML, CSV, or Tab delimited or simply text- i don't think XML is necessarily a time saver in creating something like this if the exported fields are always the same) during or after the run and save the file to a server volume (or locally and have a background app copy them every 5 or 10 minutes to the server so there is no issue with ChemStation if the server goes down ).

Then I'm thinking of having an app run in the background on the sever and check that directory for new export/report files every few minutes and when found parse them and put the digested data into the DB.

I'm just simplifying the task.

I know ... So am I!

I have a partial solution in place, but the ultimate solution would be for ChemStation to export an XLS or XML file with the information I need already in place and saved with the sample ID as part of the file.

I believe you can add custom sample fields, but unfortunately the operator has to enter them in a separate dialog. In nay case additional sample info can be in the data file.

ChemStation has a macro language which looks to be a weekly typed BASIC variant that knowns nothing of OOP.

The macros can be run from a command line or as part of a ChemStation Sequence...

I suspect you (or I ) could easily create CSV, or tab delimited files (i don't know if the language has support for XML) with it IF we had the right documentation (if you find out where it is available please let us - me - know!) . If I have to I can parse text report output files

I plan to do as little coding as possible in the ChemStation environment itself as i prefer a strongly typed OOP language.

When I'm done, what currently takes up to 4 hours daily, will take less than a minute. I'd say that's an improvement.

Back when i was running a lab (rather than running samples), I did mange to get significant productivity enhancements and eliminate drudgery by writing some code to help workflow... that made both my direct reports and manger happy!

[lmh]

Cptcrusty, I was getting at what Karen put into words much better: people get used to writing their sequences in the chromatography software, and then there's sometimes a struggle about how to get the sequence file read by LIMS. It's sometimes more sensible to assign all the sample and file names etc. in the LIMS system, and import this into the sequence table of the chromatography software.

In no case should anybody be printing out results on paper and retyping numbers. That's just plain silly. I've never encountered a piece of chromatography software yet that can't export quantitated data electronically in some form or another.

Good luck!

[chemstation]

HI Cptcrusty,


I wrote an excel spread macro, that would scan a MSD directory, and import all header info. that you can then customise which bits to show.

http://www.chemuserworld.com/forum/view ... php?tid=14

Alex

[Karen01]

If you are working with a recent version of ChemStation see:

http://www.chem.agilent.com/Library/use ... 32_XML.pdf

[cptcrusty1]

Karen, et. al;

So far, I've determined that ChemStation will export an XML file, although it does not contain the sample ID, nor any specific identifying characteristics of the sample other than the results of the voodoo that ChemStation has produced. I'm understanding that I ought to be able to customize this through ChemStation, yet have not been able to deduce how yet. I have discovered that I can produce an xls file that is virtually identicle, has sample-specific information, and I've already written a proof of concept to parse the header information and import the file. Thus far, the XML import is working and I should have it moved to a production environment soon. After that, I can work on the xls version which will be less user intensive. The only issue is that the file name is not modifiable by the user. It is given a specific name, albiet, it's in the data folder for the particular sample that is currently being evaluated in ChemStation.

The macro language is formerly from HP from what I've researched so far. Some people have actually learned it and now charge for it in other areas of this forum. Lot's of ways to make a buck... LOL

Lastly, there are dll's in ChemStation that are recognized by MS Access so even further automation is possible, with proper instruction and opportunity to learn the tools...

Progress is slow sometimes, but it's still progress.