Using Astera's data export capability, you can extract data from your source document to a destination of your choice, such as an Excel file, delimited file, or a database table. Prior to extracting data from a report document, you need to create a report model that specifies the layout of your document. You can then re-use your model to extract data from any number of documents as long as they have the same layout. In order to streamline data extraction process, the software allows you to create data export settings and save them in your report model. A data export setting specifies the fields and regions to be extracted as required by your business scenario. The actual extraction happens when you click the Run icon on the toolbar. At run-time, the program reads the source document using the report model and saves the output in the target destination.
Data Export Toolbar
Report Model exports can be configured via the “Data Export Settings” toolbar. The icons are as follows:
The icon creates a new “Export to Excel” setting.
The icon creates a new “Export to CSV” setting.
The icon creates a new “Export to Database” setting.
The icon removes data export settings.
The icon is the “Edit Export Setting” tool and brings up the same screens you used to create the export setting.
The icon creates a dataflow for your report model. This automatically uses the correct report source as well as destination: the user adds the necessary transformations.
The icon previews your data export. This is shown in the preview screen at the bottom of the program.
The icon is used to run and re-run your data export.
Exporting to Excel
When exporting to an Excel file, you can either choose the destination to save your file to (such as a folder,) or choose an existing Excel file to populate with your extracted data.
First Row Contains Header tells the program whether or not to include the data field names as headers in an Excel spreadsheet.
Worksheet is where a title for the worksheet within the Excel file can be specified.
Append to File can be selected when the data in question needs to be added to an existing Excel file. Choosing this option will save the data in the same Excel file under a new worksheet tab.
Rule for Filtering Data
There are many Functions built into our software, should data need further editing before being exported to an Excel file. These can be found under the Functions section, and are further sorted by category.
Objects shows the hierarchy that will be exported to the destination, from record nodes to data fields.
The Expression box allows the user to write queries and edit function expressions.
Compile Status tells whether or not a function is successful.
Message shows what needs to be edited in an expression if there is an error.
The Layout Builder allows the user to customize what the data is exported as.
Source Field Path shows the name of the fields that are being extracted.
Name is the name of the field within the program, whereas Header is the text that will be exported as a header in the destination. One major difference between these columns is that names cannot have any spaces, whereas headers can.
Data Type specifies what kind of information is being exported. In the Layout Builder window above, the "Book_Title" and "Checked_out_by" fields have been predetermined to be "String" data, and the "Date" field has been predetermined to be "Date" data. Therefore, data determined to be a string will be exported as normal text. Dates will be exported in a date format.
If a field needs to be written in another way, the Format column offers different options for dates (MM/dd/yyyy, dd/MM/yy, etc.,).
This screen allows the user to append comments to the exported data.
Exporting to CSV
First Row Contains Header lets the program know whether or not to export your data field names as headers in the first row of Excel cells.
Field Delimiter allows the user to choose what kind of delimiter is used to separate their values.
As above, Record Delimiter lets the user select how their records are divided. Users can select from Carriage Return (CR), Line Feed (LF), or both.
If there’s any kind of encoding that needs to be applied to your exported data, select it here.
Text Qualifier/Apply Text Qualifier to All Fields is where the user selects which character determines where data starts and ends.
Null Text is where any text specified here will be seen as a null value.
Append to File (If Exists) allows the data to be exported to an existing Excel workbook as a new sheet within it.
Hierarchical Destination will allow the data to be exported in a parent-child format as opposed to a flat format, such as an Excel sheet, when checked.
Omit Byte Order Mark (only applies to certain UTF encodings) allows the user to export the data without a Byte Order Mark.
Exporting to Database
Exporting to a database works the same way as the other export settings above, with a database connection screen added to the connecting process. For more on the database connection screen criteria above, check the connection screen documentation.
When your report model is set up and the data previews successfully, it’s time to export! There are three export destinations available to choose from:
•Export to Excel
•Export to CSV
•Export to Database
In each case, the export process works very similarly. As the differences are in the settings, we’ll use an example for “Export to Excel.” For CSV and Database exports, follow the same steps while adjusting your export settings.
Under “Data Export Settings,” click “Create New Export Setting and Run (to Excel).”
The Excel Export Setting screen will pop up. Click on the icon to the right and select an export destination. You can either specify a folder to save your export file in and name it, or chose an existing file to export your data to. In the latter case, the “Worksheet” field above allows you to name the worksheet that your data will be exported as. For this example, we’ll make a new file.
Specify your export settings. There is a detailed breakdown of each screen in the “Properties” section of this article.
When you are ready to export your model, press “Ok”. You will able to monitor the “Job Progress” at the bottom of the screen.
Congratulations! You’ve exported your data to your destination and it is now ready to use.
Data Regions and Fields