27 February 2012

Weekly Dynamic: SmartConnect and XLSM files Part 1

I’m going to do a couple of Weekly Dynamics on SmartConnect based on some work I did recently.

Let’s start with XLSM files. These are Excel files with macros included. It’s not unusual to get a CSV or Excel based file from a outside source. This could be a vendor, a customer, a partner (like ADP or bank Purchasing Card files) whatever. Often these files require manipulation prior to integration and a common way to deal with that is with an Excel macro. A macro provides a consistent way to manipulate what should be a consistent file.

When the time comes to integrate these files using SmartConnect there is a problem. SmartConnect doesn’t support XLSM file natively. If you pick that you want integrate and Excel file, you can’ t pick an XLSM file as the source. Integration Manager actually has this same issue. So what’s the answer? Well, there are few options. We’ll tackle two this week and one next week.

Option one is simply to save the file in an XLS or XLSX format and integrate it. Saving that way however destroys the macros and creates some additional steps for the user.

Another option is to point your data source to an ODBC connection in SmartConnect. Select an Excel connector and point it to the XSLM file. Because the ODBC connector reads the data out of the file the extension isn’t an issue. User can simply save their XLSM file and integrate normally using the SmartConnect interface.

Next week we’ll look at another option.