Check string length for all rows in each column

WJHamel

Board Regular
Joined
Oct 7, 2011
Messages
129
What is the best way to accomplish this?
I work frequently with excel sheets, hundreds of thousands of rows deep, containing data that needs to be converted for and imported into SQL databases. The biggest obstacle in these processes is almost always the string length constraints on the SQL side that need to adjust the Excel data to. As of now, what i typically do is write the queries to import the data accordingly and then deal with the truncation issues after the first execution of the query within SQL, because, at that point, i get to see where my strings over-run.
What i would prefer to do, is to be proactive. I would like to identify each rown in a column that has a string length beyond a designated value. That way, i can make the necessary adjustments on the excel side before writing the query for SQL. Are there any recommendations out there as to what i can do in Excel to identify the string length in rows and specifically show me string lengths beyond what i identify as tolerable?

thanks

james
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
1) the len() function will give you the length of the strings in the cells

2) this should really be a job for the SQL import - You don't say which version of SQL you are using. If you are on MSSQL2000 then you should be using DTS packages. MSSQL2005/2008 then SSIS. If you are using mySQL, mSQL or Oracle then I am not sure what ETL (extract, transform, Load) tools are available. I suspect you are using Bulk Import which is what I normally use:cool:. The other option is to use OPENROWSET() but you will need this enabled on the SQL server surface area configuration as it is a potential security risk.
 
Upvote 0
Depending on how deep your records are, you can set a couple of pre-evaluations first to determine if you even need to truncate any data.

Example, this array returns the maximum length in a column of cell with most the characters:
array (Ctrl+Shift+Enter)
=MAX(LEN(A1:A1000))

And this array returns the actual value of the cell with the most characters:
=INDEX(A1:A1000,MATCH(MAX(LEN(A1:A1000)),LEN(A1:A1000),0))

So if the first formula is under your desired max, you need do nothing. Otherwise, apply your desired max to truncate the contents in your Excel sheet with a macro but also you might consider limiting the length of data entries in the first place.
 
Last edited:
Upvote 0
OBIRON:
I'm using SQL 2008 and the "job" does happen on the SQL side. For my inserts, when variables are beyond the allowed string length for a specific column, i generally use case expressions to move that data into another "dumping" column which is usually a varchar(max) structure. My mission here was to proactively identify any of those columns/rows for which i should start building those CE's as opposed to building the INSERT TO/SELECT statements and then parsing the query for the inevitable errors.

Tom: Thanks, i'll give your formulas a shot.
 
Upvote 0

Forum statistics

Threads
1,223,204
Messages
6,170,726
Members
452,352
Latest member
angleright

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top