Ashish Kumar's Blog

Repository of C#,.NET articles

Script task for converting Excel to CSV

Solution

In my last tip, the easiest solution was to make a change in the registry but in many scenarios you might not have control to make this change for varous reasons. Even if you have control in changing this setting, this change might cause your other SSIS packages to perform poorly (based on the amount of data you have in an Excel worksheet) and it may impact other systems as well wherever this registry key is being referenced. So I have another solution for this issue.

The basic idea of this solution is to convert the Excel worksheet to CSV file and use the Flat File Connection Manager/Source adapter to import data from the CSV file. With the Flat File Connection Manager/Source Adapter we have more control to define the data type and length of each column.

These are questions that come to mind when thinking about converting an Excel worksheet to a CSV file:

• When saving an Excel worksheet using a csv extension does this make it a CSV file?
• A CSV file uses a comma as column separator, but what if there are commas in the data itself?
• What is the impact of converting an Excel worksheet to CSV file?

Well, simply saving the Excel worksheet using a CSV extension will not make it CSV file as the storage format is different for both of these file types. Rather we can use the Excel Object Library to save the Excel worksheet as a CSV file using the Script Task in SSIS and then we can import the data directly from the CSV file as shown below.

In the Script Task, when writing code for conversion, first of all you need to add a reference to Microsoft.Office.Interop.Excel under .NET components as shown below:

After adding the required reference, the References node in the Solution Explorer will look like this:

Once you have added the required reference, you can add these lines of code. The complete list of code for converting Excel worksheet to CSV file is provided below. You need to provide the location and name of the Excel worksheet along with the name of the worksheet itself and then the location and name for the CSV file which will be created:

Below you can see the Excel worksheet which I am using as the source file for conversion. You will notice row number 15 has a long string and also the description of this row contains commas in it as well.

After the conversion, the CSV file will look like this. You will notice where there were commas we now have some special characters “|#|”:

The idea behind this is, before conversion replace all the commas with some special characters and after importing the data from the CSV file update the special characters back to a comma. This can be done using code such as this.

In order to use this approach, we need to have extra storage space for having both a CSV file along with an Excel file. Apart from that, the Excel object library will take a few seconds to save the file as a CSV file. I haven’t tried it on very large file, though I think this should not take much longer. Once you have the data loaded you can do anything else you need to at that point.

Share this:

Related

Post navigation

One Comment Add yours

Thank you for the post. I have just implemented the script. It is perfect for our setup. Much better than setting IMEX=1 in connectionstring and changing the TypeGuessRows registry key.