Jump to content


Photo

Fix decimal places for parameters for excel export


  • Please log in to reply
10 replies to this topic

#1 Angela Dymond

Angela Dymond

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 15 February 2013 - 10:21 AM

How do I fix the number of decimal places for my PK parameters that can be exported to excel. In WNL v5.2, you can right click on the PK parameter name in the pivot final results and select "Format" then in the Number tab, select "fixed" and type in 0.000000000 - this function is not available in Phoenix WNL. Also, in WNL v5.2 you can create a table using the Table Wizard where there is the function to fix the number of significant figures or decimal places and the table created can be copied into excel with this fixed settings. The Table func in Phoenix WNL creates a html table and not excel ( I suppose I can use "Edit in Excel" and save as an excel file but seems a roundabout way to do this). Also in Phoenix, there is the option to fix the decimal places when creating a SAS export file - is there something similar to produce an excel file or alternative ways to fix the decimal places of the parameters that will be exported in that format to excel in a quick and simple way?



#2 Simon Davis

Simon Davis

    Advanced Member

  • Administrators
  • 1,316 posts

Posted 16 February 2013 - 10:04 PM

Hi Angela,
If you format a worksheet in Phoenix(double click on the source Phoenix work sheet to apply cell formatting) as you would like then when you export to CSV or txt you have an option to "use cell formatting" Posted Image

Does that help ?
Simon.

Attached Thumbnails

  • Cell_format.jpg


#3 Angela Dymond

Angela Dymond

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 18 February 2013 - 09:54 AM

Thanks Simon. When I export the file as csv and open this up in excel, the selected formats were not displayed, instead a random no. of decimal places were shown for the parameters (please see "csv export" attachment).

I can select to display certain cells to 9 decimal places in excel but that is not ideal as we don't want to make any changes to the file before sending to programming. In addition, the actual number copied into the cells for certain parameters are to various decimal places or significant figures when you fully expand the number out (ie. CLR in the "cvs export expand" attachment). Our working standards stipulate we submit the paramters to a fixed decimal place or significant figure. Is there a way to do this so the actual number exported is fixed, like the options provided when you do a SAS export but to a excel or excel compatible file?

Thanks,
Angela [file name=csv_export_expand.png size=27655]http://www.pharsight.com/extranet/media/kunena/attachments/legacy/files/csv_export_expand.png[/file] Posted Image

Attached Thumbnails

  • csv_export_expand.png
  • csv_export.png


#4 Wendt

Wendt

    Member

  • Members
  • PipPip
  • 20 posts

Posted 18 February 2013 - 10:26 AM

Hi Angela,

 

you can specify the number of required or optional decimal points by double-clicking on the worksheet, e.g. Final Parameters Pivoted. This way the worksheet gets undocked from the main Phoenix window and a panel for display format setttings will appear below the worksheet where you can adjust the decimal points.

If you export to csv-format and tick mark "Use Cell Formatting" the adjusted setting for decimal points will be used for the csv-file.

 

Best regards,

 

Bernd



#5 Angela Dymond

Angela Dymond

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 18 February 2013 - 10:48 AM

Thanks Bernd.

 

I tried it as you suggested from the Final parameters file, it seems to have worked to some extent,in that any numbers with >9 dp are restricted to 9 dp but there are some numbers with 8 dp (we still need the number to be to 9dp even if there is a few zeros at the end in the file to programming).

 

Also, we won't generally be exporting from the NCA final parameters results as we will likely need to do further activities such as calculate Vss/F or RAC and also merge in renal parameters. We will also likely need to do some formatting, such as column sorting, converting units, etc.

 

Would really appreciate any suggestions you may have.

 

Thanks,

Angela



#6 Simon Davis

Simon Davis

    Advanced Member

  • Administrators
  • 1,316 posts

Posted 18 February 2013 - 10:56 AM

Hi Angela, the formatting Bernd speaks of can be applied to any worksheet, so just choose the last sheet in your workflow that you've prepared for export and apply the formatting there. Be aware that Phoenix will NOT currently apply trailing zeros.

 

  Another way to format the cells if you are intending to do some data manipulation with e.g. Data Wizard anyway is to use a round Column Transformation to force a certain rounding - you could also with some small effort force trailing zeros into the cell and then force it to be text. Note this will result in the creation of new columns so you might want to use Filter to drop the original columns in the resulting worksheet and perhaps Column Properties to set then names/format of the new columns.

 

  Simon



#7 Wendt

Wendt

    Member

  • Members
  • PipPip
  • 20 posts

Posted 18 February 2013 - 11:00 AM

Angela,

 

please open the exported csv-file using a text editor (e.g. notepad++), you will see that Phoenix did export the figures with the right numbers of trailing zeros. Let me know if this is not the case.

 

Best,

 

Bernd



#8 Angela Dymond

Angela Dymond

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 22 February 2013 - 03:00 PM

Many thanks for your suggestions, Bernd and Simon.

 

I did the export as CSV file and opened up in notpad as you suggested, Bernd, and yes, I think it did copy through the no. decimal places with the trailing zeros. Thanks!

 

The only problem is that this file is in a format that the user can't easily check the transfer had been done accurately as all the numbers are bunched up. This final check is required to verify accurate export before we send the data to programming. Do you have any suggestions of other ways of openning this up so it is easiler to review the "true values" that are exported?

 

Appreciate your help.

Best regards,

Angela



#9 Simon Davis

Simon Davis

    Advanced Member

  • Administrators
  • 1,316 posts

Posted 22 February 2013 - 03:24 PM

Angela, Microsoft in their wisdom make this harder with every release, you used to be able to do something like Import as....

After a quick search I find they've moved this function to the "data ribbon"

Using Excel's text import wizard


1. Start Excel. I think you will need a blank workbook open.

2. From menu: Data, Get External Data, Import Text File. (If the menu options are greyed out this could be because you do not have a workbook open). Browse for the file you have saved. You may need to set 'files of type' drop-down box to 'All Files (*.*)' to see your file. Having selected your file click 'Import'.

3. It will start the Text Import wizard at step 1 of 3. Ensure that the 'Delimited' option is selected. Click Next.

4. Step 2 of 3. In the delimiters section tick 'Comma'. The text qualifier box should show the double-quote symbol. Click Next.

5. Step 3 of 3. (i think this is optional actually) You now need to set every column to be data format 'Text'. The first column will initially be highlighted. Move the horizontal cursor as far as it will go to the right, and holding the shift key down, click the column heading on the very last column. You should now have every column highlighted together.

Simon.

PS (the other work around is to save the CSV as an extension like .tab that will force excel to go into this text import wizard mode automatically when opening the file.)

#10 Angela Dymond

Angela Dymond

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 22 February 2013 - 04:19 PM

Hi Simon,

 

The csv file does allow me to open up in excel but it doesn't copy through the trailing zeros which are shown if opened up in Notepad.

 

Also, if I convert the cell type of a column to text in phx (so we can enter either NR or NC in empty cells with no data) before you do csv export , these columns coverted to text type do not export the formatted decimal places when the csv file is opened in Notepad. I can't use the MissingValue to enter the code NR or NC in the Data Export Options window (instead of converting the column to text in Phx) as I can only enter one code and we may have different codes to go in specific empty cells and also, that MissingValue code goes into ALL empty cells, even the ones in the units row. So, we have to modify the exported file outside of phx which is not what we want to do.

 

The only way I can see us keeping the defined decimal places, allowing the text entry into the empty cells and being able to view the "real value" exported in the final export file is by creating a table operational object with the specify the format there, then copy the result to the Table folder, then open it up in "edit in Excel" and save the file in excel. This is not a simple way to create an export file I was hoping for but to fullfuling our export criteria I think we may have to go that route. What do you think?

 

Many thanks,

Angela



#11 Simon Davis

Simon Davis

    Advanced Member

  • Administrators
  • 1,316 posts

Posted 22 February 2013 - 05:21 PM

Angela, did you try what I said about forcing Excel to import it to your preferences?

A standard import is producing the file you want fine, (as proven by opening it in Notepad). The problem you are having is Excel is too 'smart' and guesses how you want to see this data.

Please try opening either of the two attached files and you will see Excel prompts you with the following steps;

Step 1. (the .tab, .txt extensions force Excel to look at the file before import) Posted Image

Step 2. Specify the correct delimiter for your file, here it guess tab, because of the extension .tab but you could have chosen to make it comma and still named it .tab in PHX.
[file name=Table01.zip size=909]http://www.pharsight.com/extranet/media/kunena/attachments/legacy/files/Table01.zip[/file]
[file name=Table01.txt size=3009]http://www.pharsight.com/extranet/media/kunena/attachments/legacy/files/Table01.txt[/file] Posted Image

Step 3.

Posted Image

The resulting file in Excel Posted Image

If that's not clear give me a call on Monday,

Simon.

Attached Thumbnails

  • excel.jpg
  • step2.jpg
  • step3.jpg
  • step1.jpg

Attached Files






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users