- 0 0
When transferring tabular data from excel or csv into an SQL table you can run into truncation issues, this is usually due to SQL's importer only testing a few rows and predicting max field length, which can throw errors later on in the script.
For small ( less than 1m lines ) tables, I made a quick function that will loop through all cells (regardless of data type ) and return the maximum string length of the included range.
There are no additional references needed, so this can just be copied/pasted into a module.
This can then be used in Other VB scripts, or as a cell formula.
Function MaxLenRange(RNG As Range) As Integer Dim C As Range, MyLength As Integer MyLength = 0 'Set Length Measure to Zero. For Each C In RNG.Cells 'Loop through each cell in range and get string Length. If Len(C.Value) > MyLength Then 'If Cell's string length is greater than MyLength = Len(C.Value) 'current 'Max' then update variable to new Length. End If Next C MaxLenRange = MyLength ' Return Max Length to Function Variable. End Function
You can test it by creating two columns, in column B, is a random integer number :
Then in the second column ( column C ), referencing the first ( column B ) :
This will create a column of random length strings to test the function with.
As you can see, the max numeric value of B is the same as the MaxLenRange function result.
Go to the formula tab and hit 'Calculate Now' to test it.