Exporting to an Excel Spreadsheet

To export data from Pyxis to an Excel Spreadsheet you will

1. Click the File > Transfer(Import/Export)... option

1. Click the File > Transfer(Import/Export)... option

2. Select Transfer Out from the drop down selection and SpreadSheet. Then click Next.

2. Select Transfer Out from the drop down selection and SpreadSheet. Then click Next.

3. You will need to specify the file where the export will go. If you do not have a file created already, then enter (or browse to) the path and give the file a name. If the file exists, you'll select it. If it doesn't exist, enter the name you wish to use and the export process will create it. The file format supported is .xlsx only. If you do not include the .xlsx extension, the program will add it. Then click Next.

3. You will need to specify the file where the export will go. If you do not have a file created already, then enter (or browse to) the path and give the file a name. If the file exists, you'll select it. If it doesn't exist, enter the name you wish to use and the export process will create it. The file format supported is .xlsx only. If you do not include the .xlsx extension, the program will add it. Then click Next.

4. The first time you use the export you'll need to select the Study from the drop down menu. Then select the assets that you want to export data from.

4. The first time you use the export you'll need to select the Study from the drop down menu. Then select the assets that you want to export data from.

Clicking the Select Children button will select all children lower in the tree branch of the asset selected. In the screen shot example above the asset CHOCTAW was selected when the button was pushed so all leaf asset children were selected. The Include Ancestors option will make it so that the parent assets of the selected assets will also have their data exported. The Include Descendants option will make it so that all assets lower in the tree will be exported. If, instead of clicking the Select Children button the user checked the Include Descendants button then the assets CHOCTAW and HOUSLEY would also have been exported. In addition to selecting assets in the manner described above, you can also Ctrl click and Shift click to use the standard Windows customs for selecting multiple items..

Clicking the Select Children button will select all children lower in the tree branch of the asset selected. In the screen shot example above the asset CHOCTAW was selected when the button was pushed so all leaf asset children were selected.

The Include Ancestors option will make it so that the parent assets of the selected assets will also have their data exported.

The Include Descendants option will make it so that all assets lower in the tree will be exported. If, instead of clicking the Select Children button the user checked the Include Descendants button then the assets CHOCTAW and HOUSLEY would also have been exported.

In addition to selecting assets in the manner described above, you can also Ctrl click and Shift click to use the standard Windows customs for selecting multiple items..

5. When you click Next you will see the listing of Spread Sheet Profile(s). You can use the  Plus, Minus, and Duplicate icons to add, remove and duplicate for modification the profiles in the list.

5. When you click Next you will see the listing of Spread Sheet Profile(s). You can use the  Plus, Minus, and Duplicate icons to add, remove and duplicate for modification the profiles in the list.

6. Once you've added a profile, or if you want to modify and existing profile, or just see what the specific profile has configured to export, click the gear icon in the top right corner of the profile listing. The profile example shown here is a profile that will export OpCosts and a few pieces of identifying information. (Additional information in the Tips & Tricks section below) 

6. Once you've added a profile, or if you want to modify and existing profile, or just see what the specific profile has configured to export, click the gear icon in the top right corner of the profile listing. The profile example shown here is a profile that will export OpCosts and a few pieces of identifying information. (Additional information in the Tips & Tricks section below) 


 

6. Once you've added a profile, or if you want to modify and existing profile, or just see what the specific profile has configured to export, click the gear icon in the top right corner of the profile listing. The profile example shown here is a profile that will export OpCosts and a few pieces of identifying information. (Additional information in the Tips & Tricks section below) 

- You will use the  plus and minus buttons to add items to your list of data items to export. 

- Your list at the top appears as rows, but is a listing of what data will go into the various columns of the spread sheet.

- With each entry at the top you will see an example of the data in the table at the bottom. In the example above, column A row 1 is showing the label of the column that will appear on the spread sheet if you select that option (see item 7 below) and then each row below that shows that the data that will appear in that row is text. But you'll notice that column D has a label of Date and rows below that show that the data appears in a date format. Likewise for column E showing the label and the data type for that row as numbers. This data type is defined by the types in Pyxis.

- The Worksheet selection will show the names of any existing worksheets on the spreadsheet if it is an existing spreadsheet. Your profile can only export to a single worksheet. If you need data to be exported to more than one worksheet then you will need to create another profile for that worksheet.

- The Interval selection will determine how the data will be exported to the spreadsheet. Notice that Monthly is selected and in the example, column D with the date is showing monthly intervals. Please note that the data could be something like 200 bbl/day as how the data is stored in the database for a rate, but could be displayed as monthly and exported as monthly.

- To set the data that you want to export you will need to enter the type and instance of that data in Pyxis.

Type is the type of data used for the specific field.
Instance is the name of the field for that type.

When specifying the type and instance you would write the type first, then a # and then the instance name. So on our second item in this example we will specify: text#apicode

To find that information, before you have gone into the transfer wizard, go to the form where you find the data you want to export, put the cursor in the field where the data belongs, and press the F4 key. This will bring up a dialog box like this:


 

At the very top you see text#apicode and that is what you're looking for. You can copy from this dialog to paste elsewhere.

Unfortunately once you are running the Transfer Wizard and have begun defining your spreadsheet export profile, you will not be able to go back and copy/paste this type#instance. There are two tricks. First, if you have a previous version or secondary install of the software you can run it and have two copies of the software open. You can use the F4 and copy the type#instance from the one and then use the Transfer Wizard in your active install to paste. That is the easiest. 

The other thing you can do, if you don't have another installation of the program, is to identify all of the data that you are going to export, identify where it is in Pyxis, and then collect a list of type#instance needed from the F4 key and paste the list into a text file so that when you're creating the spreadsheet export profile, you have all of the type#instances you need on hand.
 

7. When you click the Options button you'll have options with respect to headers and asset path:




- If you check the option to Include Header when the spreadsheet is created the first row inserted into the file will have a header showing the Name entered in the profile. Note: If you use the option to append to an existing spreadsheet, the first row added to the spreadsheet will include the header information.

- The option Include Units in Header will add any unit specified for the data type into the header information.

- The option Units on Separate Row will separate the unit into a second row of header information.


- The option Include Asset Path(s) will add the path to the asset in the last column(s) in the spreadsheet.

- The option In Multiple Columns will use one column for each level in the path of the asset.
 

OpCost.JPG

8. After you save the profile you need to select which profiles to use for the export and specify if you want to append the data to the data already in the file. 



You need to select at least one profile. The option in the lower left corner to Append to Spreadsheet will allow you to determine if you want to append the exported data to rows immediately following the existing data or if you want to replace all data in the spreadsheet. If you check the box then the export will find the last used row (if its a new spreadsheet with no data yet then that means row 1) and add the data starting at that point. If you do not check the box you will get a warning that says that it will replace any data that is in the spreadsheet. If it is a new spreadsheet with no data, you lose nothing. If there is data, it will be replaced with whatever is being exported.
 

  1. When you click Next you are lead to a Transfer Summary. This will give you the opportunity to review information about the export

You can review the export. In this case the description line was edited to show what the column names are from the profile so that they will appear in this review.

When you click Next you'll see a final preview like this:



This example shows the final review with the Append to Spreadsheet option set. The data that is grayed out is showing what data appears on the spreadsheet and below that a sampling of what will be appended.

Click Finish and it will export the data to the spreadsheet. The speed of the export will be in direct relation to the amount of data being exported.

 

Tips and Tricks 

  1. Worksheets - When you create your profile you will want to make sure that you have the right worksheet specified. By default it will show whatever worksheets are in the workbook. If you want to have this data go to a new worksheet that doesn't already exist in the workbook then you can enter a new worksheet name here. When the export happens the new worksheet will be created to receive the exported data.
  2. Creating the file - If you don't have an existing spreadsheet to which you want to export your data, you can enter the path and file name that you want to use. If you don't put an extension on the file name it will do it for you. Again, the only file extension supported at this time is .xlsx.

 "Special Case" - There are two Data Item types that you might want to export that are "special case" items, they are ASSET_NAME and DATE. The ASSET_NAME is the name that is displayed in the tree. The DATE is needed for any stream data that is exported, so it is created automatically when you create the first Data Item that is a stream.

Saving Data When you create a profile many of the settings you make and Data Items you enter are written to .ini files and are remembered for future sessions.