Ben Sullins Tech

Sometimes files are received such as client feeds that contain values in columns that exceed the maximum number of characters allowed in the destination database. When these records are imported an error occurs when trying to fit this value into the column with the character limitation. In these cases it is helpful to have a quick way to identify which fields and specific records are causing the issue. Ideally the ETL process that loads the data would provide this information to us but, in the spirit of catching errors before they occur, here is a quick way to accomplish this in Excel.

Assumptions

1. The file is in a format that can be parsed by Excel (tab delimited, CSV, etc…)
2. The number of rows does not exceed the maximum allowed by the version of MS Excel being used.
   • Excel 2003 = 65,536 rows
   • Excel 2007 = 1,048,576 rows

Steps

1. Open the file in excel (parse it if it’s text)
2. Insert a new row above the header row
3. Enter the following formula in every column of the new row that is being imported
   • =MAX(LEN(A2:A65636))
   • The range inside the forumla should cover all of the rows in the column (eg. A2:A100) for records 2 - 100
   • In order for this formula to work properly you must press ctrl+shft+enter after typing in the formula.
   • The best way to get this into all the additional columns would be to simply copy and paste.

At this point you will be able to see the max length of the values in the columns with the formula entered. To make this more readable and even easier to identify try completing these additional steps below.

Additional Steps

1. Select both the row with the formula in it and the header row for all columns with values and press ctrl+c (copy)
2. In a new sheet under the Home > Paste > Paste Special
3. From the paste special dialogue box check ‘Values’ and ‘Transpose’ then click ok

You will now see the fields and their max lengths stored vertically instead of horizontally as they were on the original sheet which I’ve found to be the easiest to read.

Hope this helps!

This is an interesting challenge that I recently explored during a forum discussion on SQLServerCentral.com. The situation in which the user presented wasn’t exactly ideal so I will first present the other two options I proposed besides the final one that appears to work for him in his environment.

Challenge: When extracting data from an Excel source I would like to add a row number.

Solutions:

1. Include an identity column in the staging table which would add the row number for future data flows that consume the data.
2. Write the Excel data to a transient table (which has an identity column) that gets truncated at the beginning of the package (resetting the identity column).

Both of these options are expensive I/O operations that would slow things down. Another option presented by Jack Corbet was to use a script task to increment a package variable row by row and adding that to the output.

The final solution I proposed seems to be suitable for this case and I may look to implement this more in my future SSIS requirements. Here goes:

1. Added an OLE DB Source to my data flow.
2. Selected a SQL 2005 DB Server for my connection manager.
3. Chose ‘SQL Command’ as my Data access mode.
4. Used the RowNumber() function combined with the OPENROWSET() connection method to compose a query which pulled all the data in from the Excel sheet adding in the RowNumber.

In order for this to work your SQL 2005 Surface Area Configuration must allow the OPENROWSET() connection method and you’ll most like want to define this SQL Statement dynamically through a package configuration or some other method.

Here is the query:

SELECT

                ROW_NUMBER() OVER (order by [VendorAccountNo]) AS [RowNumber]
        ,       [VendorAccountNo]
        ,       [LocalAccountNo]
        ,       [CompanyCd]
        ,       [CUSIP]

FROM

        OPENROWSET(
                'Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\temp\MySheet.xls;HDR=0; IMEX=1;',
                'SELECT * FROM [Sheet1$]'
        )

I’ll probably write more on this later as to which method provides the best performance and maintainability but for now feel free to comment on what you think…

Cheers!,
Ben