Universal Connections Part 3: Excel

Welcome to part three of our multipart pasTransfer Universal Connection series.  Today, I am going to introduce you to the pasTransfer Excel Spreadsheet connections and give you an overview of how the connections can be used to read and transform Excel spreadsheets.  The Excel Spreadsheet connections consist of a source and destination connection that are capable of processing the vast majority of Excel files without the need for a custom connection.

The Excel Spreadsheet source connection uses Microsoft OLEDB ACE and JET providers to query excel documents.  Microsoft updates have resulted in the JET provider no longer functioning properly, so we have updated pasTransfer and wrote a blog article explaining the steps we have taken to help get around this issue.  You can read more about it in the pasTransfer 4.25.2.0 RTM blog article in pasUnity Products. 

Excel Spreadsheet Source Connection

The Excel Spreadsheet source connection allows you to import data from many Excel workbooks by querying the workbook as a database using OLEDB.  We query a worksheet, range, or named region in the workbook, and read each row of data one at a time.  Unlike other connections, we are able to read data from the workbook without having to open Microsoft Excel in the background.  With that said, you do need Excel for some features, such as opening the workbook in Excel. 

Once a workbook has been selected for processing, you must provide an Excel provider and a data region to process.  The data providers vary across machines and platforms, so it is important to have the correct providers and version of Excel installed.  For pasTransfer 32 bit, you must have Excel 32 bit and the Microsoft Access Database Engine 32 bit installed.  If you’re using pasTransfer 64 bit, then you must have the 64 bit versions installed.  The data region can be a worksheet, a range within a worksheet, or a named range.  Clicking the refresh button will display the worksheets and named ranges in the workbook.  If nothing appears in the dropdown when you click refresh, the provider you have chosen will not work with the selected workbook.
 
In the following example, my workbook contains a region named Employees and I have used this named range for my data region.





After you have selected your data region and chosen the correct provider, you can begin mapping fields.  All columns detected from your data region are listed on the left side.  If you don’t have column names in your source data, we generate generic names to help identify each column.  From here, you can click and drag the column names to macro-enabled fields to generate a basic COLUMN macro command.

Below, you can see how I have mapped two columns from my source file to be imported using the General wizard.  The general wizard does not have many requirements, so this screen may contain different options depending on which wizard you are in.


 
After you have provided values for each field, click next to provide any additional details required for the wizard, and finally import your data.  After data has been imported, you can apply a summarizer, mappings, and many other additional features.  Finally, it is time to move on to the destination configuration.
  
Excel Spreadsheet Destination Connection

The Excel Spreadsheet Destination Connection allows us to create a flat-structured Open XML spreadsheet that Excel and multiple other applications can read.  This means that we can create a file without having to open Excel.

The configuration for this connection is fairly simple.  First, you need to provide a name for your spreadsheet, and various other options such as whether you want to include column names and filters.  After you choose these basic configuration options, you can begin mapping fields to the new spreadsheet.  On the left hand side is a list of all attributes that have been detected from the source data.  Each row in the grid on the right defines a column on the excel spreadsheet, and the macro command defines how that column is populated.  You can drag the detected macros from the left hand side over to the grid to copy the macro command.
Below, you can see how I have defined 2 columns for my spreadsheet and the macro commands associated with each one.
 

 


Conclusion

We have now introduced you to the pasTransfer Excel Spreadsheet universal source and destination connections.  You have learned how to use pasTransfer to import data using the Excel Spreadsheet source connection and transform that data into a completely different Excel spreadsheet with filters using the Excel Spreadsheet destination connection.  We hope you found this part of series educational and informative.  Come back next week for part four of our series introducing the SQL Server universal connections.