Exporting to an Excel Spreadsheet
To export data from Pyxis to an Excel Spreadsheet you will
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.
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.
- 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
- 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.
- 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.