Background
Microsoft Excel has many built-in number formats, and the default format is General, where values are displayed just the way that you type them in. However, this is not what we would always want when we perform an integration. Data would need to be formatted with respect to many aspects depending on what we are writing. In such cases, we can select the Custom category in the Number tab of the Formal Cells dialog box and fill in the specified format in Type to define a new format. But how would we handle this when we run an automated job? When we create new excel sheets dynamically, how would we make sure that it's always formatted as required by the business?
In this blog post, we will be demonstrating how to use KingswaySoft Premium Excel Destination component to write the data with defined format (currency format) into Excel file. The components used in the below example are as follows:
- Premium ADO.NET Source component - For source data
- Premium Excel Destination component - To write to the excel sheet
The Swiss franc is the currency of Switzerland etc., and the official symbols Fr. and fr. are widely used. In this business use case, we would be seeing how to format and write these input number values into Excel destination file as below using the SSIS package.
Demonstration Data
For our example, let us generate the sample data in the database and retrieve as input data using Premium ADO.Net Source component, as shown below. The Source data is as shown below.
And in the table below, the “Expected Data in excel” is how it should be properly formatted with the separator and we expect to see this in the excel sheet.
It might appear very easy to achieve when you look at the above screenshot, but it is actually a pretty difficult task in an ETL process without a proper tool and some creative thinking. It is particularly a challenge when your operating system has a specific regional setting which is not necessarily the exact way how you would like to write the Excel file. This particular situation is one of the client cases that we have assisted with a client of ours recently, we thought it is interesting to share, which is the reason for this blog post. Let's see how we can properly address this requirement leveraging the power of the Premium Excel Destination component. Our goal is to achieve a consistent writing irregardless of the regional setting of the operating system.
Configure the Excel Thousands separator
Before we start to configure your SSIS data flow and related, we need to make sure that the Excelthe data is stored and displayed with proper “Thousands separator” (for this case, it is single quote '), we would need to configure the Excel sheet setting as shown below. Note that this is a one time configuration.
Open your Excel sheet and Navigate to File > Options > Advanced, uncheck the Use system separators check box. This will allow you to specify a custom one. And then, type new separators in the Thousands separator box.
Configure Excel Destination
After configuring the General page in the Premium Excel Destination component, the component will read the meta data from the specified Excel file automatically (click “Refresh Component” if there is metadata change for the Excel), and the default Data Type is “DT_WSTR”, which is character string as below.
.png "Currency Field in Premium Excel Destination Component (as Text Field)")
To write the sample numbers into Excel specified column, we map the input column “Number” into the respective field in Excel. The customized Number format in the Excel as requested will be "Fr." #,###.00 (note: the thousand separator “,” will be replaced to single quote ' according to the Excel global thousand separator configuration). And we need to manipulate the data format in Excel Destination component accordingly so the value can be converted to required format. to achieve this, we can click “Data Type” and choose “DT_CY- Currency” from the drop down list and change the “Format String” to "Fr." #,###.00 in the field.
With above configuration, after executing the package successfully, these input values will be loaded into destination Excel Column “Number in Currency” and the format is as expected as below.
Conclusion
You can efficiently write in different formats when working with Excel file using the Premium Excel Destination component, regardless of the data format of source data, there is no need to manually handle the data format in the Excel file post integration, thus making SSIS data integration much more streamlined. All KingswaySoft components used in the above example, both (Premium ADO.NET Source and the Premium Excel Destination) can be found in our SSIS Productivity Pack. The SSIS Productivity Pack is a collection of advanced SSIS components to enable greater developer productivity. Feel free to give it a try and see how our components can make your integration easier and efficient.