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.
- The file is in a format that can be parsed by Excel (tab delimited, CSV, etc…)
- 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
- Open the file in excel (parse it if it’s text)
- Insert a new row above the header row
- Enter the following formula in every column of the new row that is being imported
- 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.
- Select both the row with the formula in it and the header row for all columns with values and press ctrl+c (copy)
- In a new sheet under the Home > Paste > Paste Special
- 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!