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

Trouble exporting data into SPSS

Dear all,

Last week I asked you to help me with the challenge I face exporting data. I'm still having trouble exporting the data: I've tried all the earlier mentioned solutions (see below) but it still doesn't work properly and I have not managed to figure out myself what the cause is.
For now I send you the CRFs, it might be that there are any mistakes/incompatibilities that cause the error?

I hope we can fix this, it would be a pity if I cannot use this database, after the hours of work spend to it.....

Thank you so much in advance!

Kind regards,



Robin Wijsman, PhD student Radboudumc, Nijmegen, The Netherlands.


From: Tina D Purnat
Date: 2013/12/5
Hi Robin,
this might be a stupid question -- but how do you enter decimals? If you are on a US (default) language, then the decimals must be with period -- like 2.65
OC does not accept well any commas for thousands in the number...
If you know which fields might be the issue -- see how you defined them in Excel and compare with how they get exported. It might be that OC database is expecting something different from what you are typing in -- and the web application is accepting it when you enter the data even if it should not...
Tina
2013/12/4 Tina D Purnat
Probably a field ypu included on the extract has some character that the OC export engine doesn't like. The export works as a transformer of one data format into the spss, excel, etc. If the data format comong into the export contaons something the transform script can't handle, it will stall.
Your error doesn't like a decimal number value... Bit you would need to find out in which field.
One way of trying to quick test is, try first to create a new dataset and tey to export it. If it doesn't work, then make a small fataset and keep trying if export works. Then add more fields and keep yesting if export works. This way you will eventually be able to deduce what field is causing the issue. Next step is to review the eCRFs and the data in this foeld, and try to figure out what is improper.
This is long wonded, bit it is the only way to figure out what is the problem and how you can avoid it on future...
Tina
-----------
On Dec 4, 2013 6:04 PM, Robin Wijsman wrote:
Dear all,
I have a question concerning the export of my 'dummy' OC database into SPSS. It is possible to create a dataset, however when I try to export the dataset to SPSS a message shows-up: "the extract data job failed with the message: for input string "3.9". More information may be available in the log files."
The funny thing is that the numbers change every time I try to extract the database. See attached Word file for a few sequential attempts. I was even more surprised when, after doing so a few times, a file was created (only once, there after the same errors occured) that could be opened in SPSS. All this seems quite strange to me.The same goes for Excel files. Extracting the CDISC files seems to work properly.
I'm using OC 3.1.4 and I'm just a simple MD without hardly any (untill recently....) background in computer science. Please help me out! :-) In the attachment the logfiles.
Thank you very much in advance.
Kind regards,


Robin.
Attachments:
CRF_Patienttest_1.xls 93.5 KB
CRF_Tumortest.xls 80.0 KB

Comments

  • ColinSuttonColinSutton Posts: 30
    e umlaut in CRF_Pateinttest_1 Item C7?
    Regards,
    Colin
    Sent: Friday, 13 December 2013 9:08 PM
    To: [email protected]
    Subject: [Users] Trouble exporting data into SPSS
    Dear all,

    Last week I asked you to help me with the challenge I face exporting data. I'm still having trouble exporting the data: I've tried all the earlier mentioned solutions (see below) but it still doesn't work properly and I have not managed to figure out myself what the cause is.
    For now I send you the CRFs, it might be that there are any mistakes/incompatibilities that cause the error?

    I hope we can fix this, it would be a pity if I cannot use this database, after the hours of work spend to it.....

    Thank you so much in advance!

    Kind regards,



    Robin Wijsman, PhD student Radboudumc, Nijmegen, The Netherlands.


    From: Tina D Purnat
    Date: 2013/12/5
    Hi Robin,
    this might be a stupid question -- but how do you enter decimals? If you are on a US (default) language, then the decimals must be with period -- like 2.65
    OC does not accept well any commas for thousands in the number...
    If you know which fields might be the issue -- see how you defined them in Excel and compare with how they get exported. It might be that OC database is expecting something different from what you are typing in -- and the web application is accepting it when you enter the data even if it should not...
    Tina
    2013/12/4 Tina D Purnat
    Probably a field ypu included on the extract has some character that the OC export engine doesn't like. The export works as a transformer of one data format into the spss, excel, etc. If the data format comong into the export contaons something the transform script can't handle, it will stall.
    Your error doesn't like a decimal number value... Bit you would need to find out in which field.
    One way of trying to quick test is, try first to create a new dataset and tey to export it. If it doesn't work, then make a small fataset and keep trying if export works. Then add more fields and keep yesting if export works. This way you will eventually be able to deduce what field is causing the issue. Next step is to review the eCRFs and the data in this foeld, and try to figure out what is improper.
    This is long wonded, bit it is the only way to figure out what is the problem and how you can avoid it on future...
    Tina
    -----------
    On Dec 4, 2013 6:04 PM, Robin Wijsman wrote:
    Dear all,
    I have a question concerning the export of my 'dummy' OC database into SPSS. It is possible to create a dataset, however when I try to export the dataset to SPSS a message shows-up: "the extract data job failed with the message: for input string "3.9". More information may be available in the log files."
    The funny thing is that the numbers change every time I try to extract the database. See attached Word file for a few sequential attempts. I was even more surprised when, after doing so a few times, a file was created (only once, there after the same errors occured) that could be opened in SPSS. All this seems quite strange to me.The same goes for Excel files. Extracting the CDISC files seems to work properly.
    I'm using OC 3.1.4 and I'm just a simple MD without hardly any (untill recently....) background in computer science. Please help me out! :-) In the attachment the logfiles.
    Thank you very much in advance.
    Kind regards,


    Robin.
    Scanned by MailMarshal - M86 Security's comprehensive email content security solution. Download a free evaluation of MailMarshal at www.m86security.com
  • GerbenRienkGerbenRienk Posts: 802 ✭✭✭
    Hi Robin,
    >From your log files it appears that Tina's guess was right:
    sun.misc.FloatingDecimal.readJavaFormatString is the common factor with
    "7,3", "3,4" etc as culprits.
    To avoid this in th future, make sure that your java/tomcat is running
    under the right locale (see
    http://en.wikibooks.org/wiki/OpenClinica_User_Manual/PopupCalendar)
    I can think of no other way to get out of your deadlock than identifying
    all the errors and manually changing them.
    Do you have access to the backend? If so, issue a query like
    select * from item where item_id in (
    select item_id from item_data where value like '%.%' group by item_id)
    and item_data_type_id=7;
    This will give you the items of type REAL that contain one or more comma's.
    If your OC is small, you might have enough information: go through your
    Subjects and change the values.
    If this is not a feasible option, consider writing a Rule for each item
    with expression:
    if ItemOID ct ","
    and run the Rule manually. All affected items will be in your list of
    Discrepancies.
    Hope this helps,
    Gerben Rienk
    > > Dear all,
    > >
    > > Last week I asked you to help me with the challenge I face exporting data.
    > > I'm still having trouble exporting the data: I've tried all the earlier
    > > mentioned solutions (see below) but it still doesn't work properly and I
    > > have not managed to figure out myself what the cause is.
    > > For now I send you the CRFs, it might be that there are any
    > > mistakes/incompatibilities that cause the error?
    > >
    > > I hope we can fix this, it would be a pity if I cannot use this database,
    > > after the hours of work spend to it.....
    > >
    > > Thank you so much in advance!
    > >
    > > Kind regards,
    > >
    > >
    > >
    > > Robin Wijsman, PhD student Radboudumc, Nijmegen, The Netherlands.
    > >
    > >
    > > From: Tina D Purnat
    > > Date: 2013/12/5
    > > Hi Robin,
    > >
    > > this might be a stupid question -- but how do you enter decimals? If you
    > > are on a US (default) language, then the decimals must be with period --
    > > like 2.65
    > >
    > > OC does not accept well any commas for thousands in the number...
    > >
    > > If you know which fields might be the issue -- see how you defined them in
    > > Excel and compare with how they get exported. It might be that OC database
    > > is expecting something different from what you are typing in -- and the
    > > web
    > > application is accepting it when you enter the data even if it should
    > > not...
    > >
    > > Tina
    > >
    > > 2013/12/4 Tina D Purnat
    > >
    >> >> Probably a field ypu included on the extract has some character that
    >>> >>> the OC export engine doesn't like. The export works as a transformer
    >>> >>> of
    >>> >>> one data format into the spss, excel, etc. If the data format comong
    >>> >>> into the export contaons something the transform script can't handle,
    >>> >>> it
    >>> >>> will stall.
    >>> >>>
    >>> >>> Your error doesn't like a decimal number value... Bit you would need to
    >>> >>> find out in which field.
    >>> >>>
    >>> >>> One way of trying to quick test is, try first to create a new dataset
    >>> >>> and tey to export it. If it doesn't work, then make a small fataset
    >>> >>> and
    >>> >>> keep trying if export works. Then add more fields and keep yesting if
    >>> >>> export works. This way you will eventually be able to deduce what
    >>> >>> field is
    >>> >>> causing the issue. Next step is to review the eCRFs and the data in
    >>> >>> this
    >>> >>> foeld, and try to figure out what is improper.
    >>> >>>
    >>> >>> This is long wonded, bit it is the only way to figure out what is the
    >>> >>> problem and how you can avoid it on future...
    >>> >>>
    >>> >>> Tina
    >>> >>>
    >>> >>> -----------
    >>> >>> On Dec 4, 2013 6:04 PM, Robin Wijsman wrote:
    >>> >>>
    >> >> Dear all,
    >>> >>>
    >> >> I have a question concerning the export of my 'dummy' OC database
    >>>> >>>> into SPSS. It is possible to create a dataset, however when I try to
    >>>> >>>> export
    >>>> >>>> the dataset to SPSS a message shows-up: "the extract data job failed
    >>>> >>>> with
    >>>> >>>> the message: for input string "3.9". More information may be available
    >>>> >>>> in
    >>>> >>>> the log files."
    >>>> >>>> The funny thing is that the numbers change every time I try to extract
    >>>> >>>> the database. See attached Word file for a few sequential attempts. I
    >>>> >>>> was
    >>>> >>>> even more surprised when, after doing so a few times, a file was
    >>>> >>>> created
    >>>> >>>> (only once, there after the same errors occured) that could be opened
    >>>> >>>> in
    >>>> >>>> SPSS. All this seems quite strange to me.The same goes for Excel
    >>>> >>>> files.
    >>>> >>>> Extracting the CDISC files seems to work properly.
    >>>> >>>> I'm using OC 3.1.4 and I'm just a simple MD without hardly any (untill
    >>>> >>>> recently....) background in computer science. Please help me out! :-)
    >>>> >>>> In
    >>>> >>>> the attachment the logfiles.
    >>>> >>>>
    >>>> >>>> Thank you very much in advance.
    >>>> >>>> Kind regards,
    >>>> >>>>
    >>>> >>>>
    >>>> >>>> Robin.
    >>>> >>>>
    >>> >>>
    > >
  • I had this problem a while ago. But my problem was that I translated openclinica to Portuguese and my server's os was in English, so I think it had trouble with the decimals, as in Brazil we use comma for decimals and dots for thousands, and in the us it's the other way around. So I reinstalled openclinica and did not translate it. I never had that problem again.
    Regards
    Leandro
    On Friday, 13 December 2013, Robin Wijsman wrote:
    Dear all,

    Last week I asked you to help me with the challenge I face exporting data. I'm still having trouble exporting the data: I've tried all the earlier mentioned solutions (see below) but it still doesn't work properly and I have not managed to figure out myself what the cause is.
    For now I send you the CRFs, it might be that there are any mistakes/incompatibilities that cause the error?

    I hope we can fix this, it would be a pity if I cannot use this database, after the hours of work spend to it.....

    Thank you so much in advance!

    Kind regards,



    Robin Wijsman, PhD student Radboudumc, Nijmegen, The Netherlands.


    From: Tina D Purnat
    Date: 2013/12/5
    Hi Robin,
    this might be a stupid question -- but how do you enter decimals? If you are on a US (default) language, then the decimals must be with period -- like 2.65
    OC does not accept well any commas for thousands in the number...
    If you know which fields might be the issue -- see how you defined them in Excel and compare with how they get exported. It might be that OC database is expecting something different from what you are typing in -- and the web application is accepting it when you enter the data even if it should not...
    Tina
    2013/12/4 Tina D Purnat
    Probably a field ypu included on the extract has some character that the OC export engine doesn't like. The export works as a transformer of one data format into the spss, excel, etc. If the data format comong into the export contaons something the transform script can't handle, it will stall.
    Your error doesn't like a decimal number value... Bit you would need to find out in which field.
    One way of trying to quick test is, try first to create a new dataset and tey to export it. If it doesn't work, then make a small fataset and keep trying if export works. Then add more fields and keep yesting if export works. This way you will eventually be able to deduce what field is causing the issue. Next step is to review the eCRFs and the data in this foeld, and try to figure out what is improper.
    This is long wonded, bit it is the only way to figure out what is the problem and how you can avoid it on future...
    Tina
    -----------
    On Dec 4, 2013 6:04 PM, Robin Wijsman wrote:
    Dear all,
    I have a question concerning the export of my 'dummy' OC database into SPSS. It is possible to create a dataset, however when I try to export the dataset to SPSS a message shows-up: "the extract data job failed with the message: for input string "3.9". More information may be available in the log files."
    The funny thing is that the numbers change every time I try to extract the database. See attached Word file for a few sequential attempts. I was even more surprised when, after doing so a few times, a file was created (only once, there after the same errors occured) that could be opened in SPSS. All this seems quite strange to me.The same goes for Excel files. Extracting the CDISC files seems to work properly.
    I'm using OC 3.1.4 and I'm just a simple MD without hardly any (untill recently....) background in computer science. Please help me out! :-) In the attachment the logfiles.
    Thank you very much in advance.
    Kind regards,


    Robin.
  • ccollinsccollins Posts: 378 admin
    Hi Leandro,
    OpenClinica will support your translation quite well and most other
    localized settings, but it does not support using commas for decimals.
    See https://docs.openclinica.com/3.1/technical-documents/openclinica-and-internationalization
    for more detail. Also the server os does need to be set to English.
    Best,
    Cal
    PS - if anybody does have OC working using commas for decimals, please
    share what you had to do for this to work!
    On Fri, Dec 13, 2013 at 6:12 PM, Leandro Amparo wrote:
    > > I had this problem a while ago. But my problem was that I translated
    > > openclinica to Portuguese and my server's os was in English, so I think it
    > > had trouble with the decimals, as in Brazil we use comma for decimals and
    > > dots for thousands, and in the us it's the other way around. So I
    > > reinstalled openclinica and did not translate it. I never had that problem
    > > again.
    > > Regards
    > > Leandro
    > >
    > >
    > > On Friday, 13 December 2013, Robin Wijsman wrote:
    >> >>
    >> >> Dear all,
    >> >>
    >> >> Last week I asked you to help me with the challenge I face exporting data.
    >> >> I'm still having trouble exporting the data: I've tried all the earlier
    >> >> mentioned solutions (see below) but it still doesn't work properly and I
    >> >> have not managed to figure out myself what the cause is.
    >> >> For now I send you the CRFs, it might be that there are any
    >> >> mistakes/incompatibilities that cause the error?
    >> >>
    >> >> I hope we can fix this, it would be a pity if I cannot use this database,
    >> >> after the hours of work spend to it.....
    >> >>
    >> >> Thank you so much in advance!
    >> >>
    >> >> Kind regards,
    >> >>
    >> >>
    >> >>
    >> >> Robin Wijsman, PhD student Radboudumc, Nijmegen, The Netherlands.
    >> >>
    >> >>
    >> >> From: Tina D Purnat
    >> >> Date: 2013/12/5
    >> >> Hi Robin,
    >> >>
    >> >> this might be a stupid question -- but how do you enter decimals? If you
    >> >> are on a US (default) language, then the decimals must be with period --
    >> >> like 2.65
    >> >>
    >> >> OC does not accept well any commas for thousands in the number...
    >> >>
    >> >> If you know which fields might be the issue -- see how you defined them in
    >> >> Excel and compare with how they get exported. It might be that OC database
    >> >> is expecting something different from what you are typing in -- and the web
    >> >> application is accepting it when you enter the data even if it should not...
    >> >>
    >> >> Tina
    >> >>
    >> >> 2013/12/4 Tina D Purnat
    >>>> >>>>
    >>>> >>>> Probably a field ypu included on the extract has some character that the
    >>>> >>>> OC export engine doesn't like. The export works as a transformer of one
    >>>> >>>> data format into the spss, excel, etc. If the data format comong into the
    >>>> >>>> export contaons something the transform script can't handle, it will stall.
    >>>> >>>>
    >>>> >>>> Your error doesn't like a decimal number value... Bit you would need to
    >>>> >>>> find out in which field.
    >>>> >>>>
    >>>> >>>> One way of trying to quick test is, try first to create a new dataset
    >>>> >>>> and tey to export it. If it doesn't work, then make a small fataset and
    >>>> >>>> keep trying if export works. Then add more fields and keep yesting if
    >>>> >>>> export works. This way you will eventually be able to deduce what field is
    >>>> >>>> causing the issue. Next step is to review the eCRFs and the data in this
    >>>> >>>> foeld, and try to figure out what is improper.
    >>>> >>>>
    >>>> >>>> This is long wonded, bit it is the only way to figure out what is the
    >>>> >>>> problem and how you can avoid it on future...
    >>>> >>>>
    >>>> >>>> Tina
    >>>> >>>>
    >>>> >>>> -----------
    >>>> >>>> On Dec 4, 2013 6:04 PM, Robin Wijsman wrote:
    >>>> >>>>
    >>>> >>>> Dear all,
    >>>>> >>>>>
    >>>>> >>>>> I have a question concerning the export of my 'dummy' OC database into
    >>>>> >>>>> SPSS. It is possible to create a dataset, however when I try to export the
    >>>>> >>>>> dataset to SPSS a message shows-up: "the extract data job failed with the
    >>>>> >>>>> message: for input string "3.9". More information may be available in the
    >>>>> >>>>> log files."
    >>>>> >>>>> The funny thing is that the numbers change every time I try to extract
    >>>>> >>>>> the database. See attached Word file for a few sequential attempts. I was
    >>>>> >>>>> even more surprised when, after doing so a few times, a file was created
    >>>>> >>>>> (only once, there after the same errors occured) that could be opened in
    >>>>> >>>>> SPSS. All this seems quite strange to me.The same goes for Excel files.
    >>>>> >>>>> Extracting the CDISC files seems to work properly.
    >>>>> >>>>> I'm using OC 3.1.4 and I'm just a simple MD without hardly any (untill
    >>>>> >>>>> recently....) background in computer science. Please help me out! :-) In the
    >>>>> >>>>> attachment the logfiles.
    >>>>> >>>>>
    >>>>> >>>>> Thank you very much in advance.
    >>>>> >>>>> Kind regards,
    >>>>> >>>>>
    >>>>> >>>>>
    >>>>> >>>>> Robin.
    >> >>
    >> >>
    > >
    > >
  • pherdpherd Posts: 18
    Why are you sending me harrasing emails... Please remove me from your emailing list.


    Phill Herd
    Data Warehouse Manager
    email:[email protected]
    ext: 3007
    Sent: 13 December 2013 23:12
    To: [email protected]
    Subject: Re: [Users] Trouble exporting data into SPSS
    Warning: This message contains unverified links which may not be safe. You should only click links if you are sure they are from a trusted source.
    I had this problem a while ago. But my problem was that I translated openclinica to Portuguese and my server's os was in English, so I think it had trouble with the decimals, as in Brazil we use comma for decimals and dots for thousands, and in the us it's the other way around. So I reinstalled openclinica and did not translate it. I never had that problem again.
    Regards
    Leandro
    On Friday, 13 December 2013, Robin Wijsman wrote:
    Dear all,

    Last week I asked you to help me with the challenge I face exporting data. I'm still having trouble exporting the data: I've tried all the earlier mentioned solutions (see below) but it still doesn't work properly and I have not managed to figure out myself what the cause is.
    For now I send you the CRFs, it might be that there are any mistakes/incompatibilities that cause the error?

    I hope we can fix this, it would be a pity if I cannot use this database, after the hours of work spend to it.....

    Thank you so much in advance!

    Kind regards,



    Robin Wijsman, PhD student Radboudumc, Nijmegen, The Netherlands.


    From: Tina D Purnat
    Date: 2013/12/5
    Hi Robin,
    this might be a stupid question -- but how do you enter decimals? If you are on a US (default) language, then the decimals must be with period -- like 2.65
    OC does not accept well any commas for thousands in the number...
    If you know which fields might be the issue -- see how you defined them in Excel and compare with how they get exported. It might be that OC database is expecting something different from what you are typing in -- and the web application is accepting it when you enter the data even if it should not...
    Tina
    2013/12/4 Tina D Purnat
    Probably a field ypu included on the extract has some character that the OC export engine doesn't like. The export works as a transformer of one data format into the spss, excel, etc. If the data format comong into the export contaons something the transform script can't handle, it will stall.
    Your error doesn't like a decimal number value... Bit you would need to find out in which field.
    One way of trying to quick test is, try first to create a new dataset and tey to export it. If it doesn't work, then make a small fataset and keep trying if export works. Then add more fields and keep yesting if export works. This way you will eventually be able to deduce what field is causing the issue. Next step is to review the eCRFs and the data in this foeld, and try to figure out what is improper.
    This is long wonded, bit it is the only way to figure out what is the problem and how you can avoid it on future...
    Tina
    -----------
    On Dec 4, 2013 6:04 PM, Robin Wijsman wrote:
    Dear all,
    I have a question concerning the export of my 'dummy' OC database into SPSS. It is possible to create a dataset, however when I try to export the dataset to SPSS a message shows-up: "the extract data job failed with the message: for input string "3.9". More information may be available in the log files."
    The funny thing is that the numbers change every time I try to extract the database. See attached Word file for a few sequential attempts. I was even more surprised when, after doing so a few times, a file was created (only once, there after the same errors occured) that could be opened in SPSS. All this seems quite strange to me.The same goes for Excel files. Extracting the CDISC files seems to work properly.
    I'm using OC 3.1.4 and I'm just a simple MD without hardly any (untill recently....) background in computer science. Please help me out! :-) In the attachment the logfiles.
    Thank you very much in advance.
    Kind regards,


    Robin.
    ********************************************************************************************************************
    This message may contain confidential information. If you are not the intended recipient please inform the
    sender that you have received the message in error before deleting it.
    Please do not disclose, copy or distribute information in this e-mail or take any action in reliance on its contents:
    to do so is strictly prohibited and may be unlawful.
    Thank you for your co-operation.
    NHSmail is the secure email and directory service available for all NHS staff in England and Scotland
    NHSmail is approved for exchanging patient data and other sensitive information with NHSmail and GSi recipients
    NHSmail provides an email address for your career in the NHS and can be accessed anywhere
    ********************************************************************************************************************
  • pherdpherd Posts: 18
    Why are you sending me harrasing emails... Please remove me from your emailing list.
    Phill Herd
    Data Warehouse Manager
    email:[email protected]
    ext: 3007
This discussion has been closed.