We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

MS Powershell and the extraction procedures

kristiakkristiak Posts: 1,338 ✭✭✭
Hi Lindsay,
I have been looking at your scripts for converting the CDISC ODM XML 1.3 Full with OpenClinica extensions to either CSV for later import into an ACCESS database or to use or the other scripts to convert directly to ACCESS. Unfortunately my experience in more modern scripting and programming is rather limited, my experience goes way back to Fortran 4, 7 and Basic 35 years ago. I found POWERSHELL ISE on my WIN 10 PC and I tried to run you CSV script and struggled with the error messages to figure out where it would read my CDISC ODM XML file. Is there any possibility to get a more detailed step by step guideline how to do the magic o:) ?
Best regards
Krister

Comments

  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Well I have good news, Access still uses VBA which has been the same for
    nearly 20 years. I can see though that the readme is a little bit vague on
    how to use these scripts.

    The sas_singular script should just be, replace extract.xml with your file
    and run the bat script. If it doesn't work then you need to change the bat
    script so instead of just "java", it is "
    C:\path\to\your\jre\bin\java.exe". It will spit out 3 files; run the
    "load.sas" in SAS and check the WORK library for the datasets.

    For the Access XSL, you can use Saxon instead of Powershell / msxml to run
    it. The syntax is the same as the sas_singular bat script, but change the
    name of the XSL file accordingly, and add an output argument to the end,
    like " -o my_outfile.xml".

    The CSV and R scripts are more complex, since the Powershell script is
    doing some of the work. The main hurdle in running them is to
    set-executionpolicy, which by default blocks you from running ps1 scripts.
    Some alternatives are at [1].

    Hope this helps. I want to polish this up, but also I have kept Gerben
    waiting on some datamart enhancements for a while now :(

    [1]
    https://blog.netspi.com/15-ways-to-bypass-the-powershell-execution-policy/
  • kristiakkristiak Posts: 1,338 ✭✭✭
    Hi Lindsay,
    I have been studying your detailed instruction to set it up and I will now try it as per you instructions "OpenClinica Community DataMart" and I will test it using WIN 10. If that doesn't work I will try it on a WIN 2008 R2 server. I will probably bombard you with stupid questions, hope you don't mind too much.
    Best regards

    Krister
  • kristiakkristiak Posts: 1,338 ✭✭✭
    One thing pussels me, why do you have to install PG 9.02. As far as I know that version does not work without manipulating OC. Can I use the regular PG backup file from Admin III, which I run every night?
    Best wishes

    Krister
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Install postgres 9.3 or later for datamart. 9.5 is the the latest and
    greatest and will work too. This is required because it uses postgres
    features that were added in 9.3.

    So you would have an 8.4, which has OpenClinica in it, and 9.5, which has
    datamart. The 9.5 connects to the 8.4 and pulls data in.

    If you are testing locally using a backup file, you can restore it into the
    same 9.5 instance as you have datamart in. They will just end up as
    different databases in the same postgres instance.
  • kristiakkristiak Posts: 1,338 ✭✭✭
    If I understand you correctly I can install the data mart on a computer without having to install OpenClinica and just load it from the backup file?
  • kristiakkristiak Posts: 1,338 ✭✭✭
    clarification ....from the OC backup file...
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    That's correct
  • kristiakkristiak Posts: 1,338 ✭✭✭
    That's great, I think I understand how it will work. I will strat testing next wee,
  • kristiakkristiak Posts: 1,338 ✭✭✭
    Hi Lindsay,
    I'm trying to make the ACCESS mart work but I lost the reference to GitHub where you published all the details. Would you please remind me where I find your excellent scripts again!
    Best
    Krister
Sign In or Register to comment.