checking field length in CSV file 1

[gmoorthy]

I have a CSV file and i need to validate the column length for various fields for example

1;2013;ABC ;

How can i check that column 1 has length of 1 and column 2 has a length of 4 and column 3 has a length of 16 . I can also get this same file in text format where each data is seperated by a ";" any easy way to do it ?

 

[SkipVought]

But I still don't know WHY you would want SPACES in your data in Excel??????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[gmoorthy]

I want to validate that in the CSV the column width are correct hence i ws trying toimport them into excel and then write a formula to check the width.

 

[SkipVought]

So one row is 5 and the other row are 15.

Now what? What impact does that have to your application? I need to you your ratioal as to what this difference matters.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

rational

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[gmoorthy]

My application will error out. So if there are two rows the semicolon shifted I want to make sure for every row the column widths are consistent

1;2013;ABCMNYP ;RC;E;C;78290 ;AB
1;2013;ABMNYP ;RC;E;C;178290 ;AB

 

[SkipVought]

...and your application is?????????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[gmoorthy]

It is an age old proprietary system.

 

[SkipVought]

Ah, yes!

So this is not just to, "validate the column length for various fields" but also to create a fixed width file fo use of your "old proprietary system."

The "old" COBOL and TSO files I've work with, might have used FIXED WIDTH, but not BOTH FIXED WIDTH & DELIMITERS, but so be it.

I write a VBA procedure to OPEN #1 for INPUT and OPEN #2 for OUTPUT, read the imput, parse on the delimiter, right pad with spaces to build the output string and then output, a row at a time.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

If needing the data in the coumns to be a specific length size, why don't you write up a little macro to add the spaces you need? The following is a macro that can add spaces. The following macro lists 2 different ways of adding the spaces (e.g., before Cells(iRow, iCol) = item + Space; or after Cells(iRow, iCol) = Space +item, your current data) Currently, it is set for putting the spaces before the data. If you wish it the other way, remove the ' at the beginning of the line and place one on the next line. Note, the macro needs to change the format of the cells to a TEXT format (NumberFormat = "@") in order to add the spaces.

Sub spaces()
Dim Space As String, item As String

Dim i As Integer, iRow As Integer, iCol As Integer, Lcell As Integer, Lcol As Integer


' starting location of data
iRow = 1
iCol = 1

Do Until IsEmpty(Sheet1.Cells(iRow, iCol))
    Do Until IsEmpty(Sheet1.Cells(iRow, iCol))
        Cells(iRow, iCol).NumberFormat = "@"
        item = Cells(iRow, iCol)
        Lcol = Choose(iCol, 1, 4, 8, 2, 1, 1, 15, 2) ' This is the length each field in each column
        Lcell = Len(item)
        If Lcol > Lcell Then
            Space = ""
            For i = 1 To Lcol - Lcell
                Space = Space + " "
            Next i
'            Cells(iRow, iCol) = item + Space    ' This puts the spaces at the end of the cell
            Cells(iRow, iCol) = Space + item    ' This puts the spaces at the beginning of the cell
        End If
        iRow = iRow + 1
    Loop
iRow = 1
iCol = iCol + 1
Loop
End Sub

 

[xlbo]

If you have access to a sql database, and you have a lot of rows to deal with, a quicker option would be to import from your semi colon delimited text file into a sql table which is set to be char rather than varchar values for each column. This will automatically pad the loaded data to the correct length and then you can extract from the sql table to re-generate the file safe in the knowledge that your values will be the correct length

Whilst this can be done in excel, the requirement to process row by row will mean that if dealing with more than a few thousand rows of data, the process could take a while...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question