How to determine Field Length-Column Width for delimited data records

[Bryan45]

For a delimited file of 10,000 rows with columns:

CUST_ID,FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP

How can the max Width for each column be determined?

I am currently working in Visual Fox Pro.

 

[Geates]

If the file is delimited, there isn't really a max length defined.

However, I think you're asking: " How do I find the a columns longest value?"

1. Read each row
2. Split the row into an array using the delimiter
3. Get the length of each value in the array
4. Compare it's length with the previous max length
5. If it's longer, it because the new max length

UNTESTED

set objFile = objFSO.OpenTextFile("data.txt", 1)

strDelimiter = ","
arrLongest = array(0,0,0,0,0,0) 'number of elements equals number of colums

do while not objFile.AtEndOfStream
   '1. Read each row
   strRow = objFile.ReadLine

   '2. Split the row into an array using the delimiter
   arrRow = split(strLine, strDelimiter)

   for i = 0 to ubound(arrRow) - 1
      '3. Get the length of each value in the array
      intLength = len(arrRow(i))
       
      '4. Compare it's length with the previous max length
      if (intValue > arrLongest(i)) then
          '5. If it's longer, it because the new max length
          arrLongest(i) = intLength
      end if
   next
loop

'Print the longest values for each column
msgbox join(arrLongest, ", ")

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

 

[guitarzan]

Also, if you can open a recordset, you can use SQL aggregate functions to get the info.

For example, if it was a CSV file, something like this would work.

Option Explicit
Dim Conn, rs, sql
Set Conn = CreateObject("ADODB.Connection") 'Server.
Conn.Open _
	"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
	"DBQ=C:\PathToCSVFile" & _
	";Extensions=asc,csv,tab,txt;HDR=YES;Persist Security Info=False;" 

sql = "SELECT " & _
   " MAX(Len([LAST_NAME])) As MaxLast, " & _
   " MAX(Len([FIRST_NAME])) As MaxFirst " & _
   " FROM mycsvfile.csv"

Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, Conn
Wscript.echo "Longest last name is: " & rs("MaxLast") & ""
Wscript.echo "Longest first name is: " & rs("MaxFirst") & ""
rs.Close