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]

hi,

a .csv is a TEXT file. Turns out it's technically NOT a csv (COMMA separated values), but thats immaterial. The file extension is one of several TEXT file standard designations.

So are you opening in Excel? Are you using VB to OPEN for INPUT? Are you IMPORTING into Excel?

In some what you must OPEN the file before you can evaluate the "columns" and it depends on the METHOD that you use, what technique you can employ to do that evaluation.

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

 

[gmoorthy]

Is there an asy way to do this in VB ? I am too raw at macro's

 

[SkipVought]

You can do it in Excel!

Import your data

Use this formula entered as an ARRAY formula (shift+cntrl+enter)
[tt]
=MAX(LEN(A:A))
[/tt]
and copy across.

Do not enter the formulas in any of the columns containing your imported data.

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

 

[mintjulep]

On possibly way is to open the file in Excel, allowing the conversion process to transform it into a spreadsheet.

Then use conditional formatting to identify any cells that do not meet your criteria.

 

[SkipVought]

mint, Excel will not automatically parse ;, only ,!

Besides, it is possible to get unexpected results under certain conditions using OPEN oon a true .csv. I almost always IMPORT where I can control how each field is parsed.

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

 

[mintjulep]

Yes.

I can never remember if OPEN or IMPORT causes the dialog to fire.

 

[gmoorthy]

Thanks that helped now i have more tricky data there will be multple rows

1;2013;ABC ;
8;2013,XYZ ;
9;2014,MNY ;

column 1 has length of 1 and column 2 has a length of 4 and column 3 has a length of 16 so how can do this check for each column and then mark or highlight that cell with a different colour if something is wrong ( length not correc)

 

[SkipVought]

My original formula looked for the MAX length in each column.

You want 3 formulas for each row like...
[tt]
=left(a1)
[/tt]
and copy across then copy down thru all rows of data.

INSERT a heading row and add headings for each column

Turn on AutoFilter

Filter one column at a time on any length not equal to the specified length.

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

 

[gmoorthy]

I am sorry but i did no understand are u saying something like this

Column B , C , D
1;2013;ABC ;
8;2013,XYZ ;
9;2014,MNY ;


Aeu sayig in cell A1 under column A i put a formula =MAX(LEN(A:A))and then darg it down for all the rows ?

 

[SkipVought]

NO!

IMPORT & Parse to get
[pre]
A B C
1 2013 ABC
8 2013 XYZ
9 2014 MNY
[/pre]

Then in column D
[tt]
D: =len(A1)
[/tt]
Then drag the formula in D1 thru F1. Then copr down thru the rows of data

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

 

[gmoorthy]

Thanks , but what is happenin is tha when i import into excel data that is less than the actual field length is causing issue for example if

length of Column D s supposed to be 10 and my data is only 5 digits long then the remaining data does not come in clean .In the text file that i have it is semicolon delimited and fixed width file and if the data in the columns is less than the field length then there is a space in the text file. No sure how to handle this while importing

 

[SkipVought]

Please post an example of this instance with mixed field lengths.

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

 

[SkipVought]

In the text file that i have it is semicolon [highlight]delimited[/highlight] and [highlight]fixed width[/highlight] file

[highlight]These are mutually exclusive[/highlight]. You can EITHER parse on the delimiter, OR parse on a fixed width. One or the other. Can't have both.

Still need to see an cogent example of this dilema in context.

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

 

[mintjulep]

Perhaps there is some padding with SPACES?

In which case TRIM() could be invoked.

 

[gmoorthy]

Here is the sample - If you see when i import this into excel the 7 field is messing up maybe it is due to being a number ? The length of the 7th field is 15 and when i do a LEN it returns 5

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

 

[SkipVought]

Actually it should show that the 7th field length is 7 with a trailing space, if your ONLY delimiter is ;. And YES, the length of the DIGITS is 5.

SO WHAT is the problem that most of the lengths are 15 in the 7th column and some are not? WHY is that a problem?

You really have not demonstrated anything IN CONTEXT that would indicate a problem!

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

 

[SkipVought]

What is the ultimate objective here?

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

 

[gmoorthy]

So when try to run the LEN formula it returns a 5 for the 7th column , the width is 15th. I want to validate that column 7 is width 15 and because of the trailing spaces it is not doing anything like putting a ( at a spot less than 15.

 

[SkipVought]

Your text file is DELIMITED by SEMICOLON.

How was your text file generated, because it was NOT generated with ANY sort of concern for FIXED COLUMN WIDTH!!!

As it is, each row is delimited on whatever characters are there; 15 or not. That's all you have!

If you want each of your columns right padded with spaces to a specified with then there are a number of options.

option 1: Make sure that your text file contains FIXED WIDTH data with NO DELIMITERS like...
[pre]
1 2013 ABC 123456789012345more
[/pre]

option 2: post process your DELIMITED import data right padding each column with spaces with a formula like
[tt]
=LEFT(G1&" ",15)
[/tt]
where each column would have an appropriate number of spaces. Then you would COPY the formula columns and PASTE SPECIAL -- VALUES over the original columns, or you could write some VBA to pad each column in place.

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