How to ignore rows with a specific character in a csv file

ziggy25 · 03-11-2010, 05:03 AM

Hi,

I have a csv file that has around 3 million rows. The file format is shown below

Code:

"fd!","sdf","dsfds","dsfd"
"fd!","asdf","dsfds","dsfd"
"fd","sdf","rdsfds","dsfd"
"fdd!","sdf","dsfds","fdsfd"
"fd!","sdf","dsfds","dsfd"
"fd","sdf","tdsfds","dsfd"
"a2!","sdf","tdsfds","dsfd"
"faaav","sdf","tdsfds","dsfd"
"b","sdf","tdsfds","dsfd"
"fd!","sdf","dsfds","dsfd"
"fd","sdf","dsfds","dsfd"

I want to process this file so that it creates a new file that contains only the rows that have 2 characters or less in the first column column. The resultant file should look like this

Code:

"fd","sdf","rdsfds","dsfd"
"fd","sdf","tdsfds","dsfd"
"b","sdf","tdsfds","dsfd"
"fd","sdf","dsfds","dsfd"

At the moment i am using SQL Loader but its taking too long so im wondering whether this would be easier if done in Unix?

Thanks

druuna · 03-11-2010, 05:18 AM

Hi,

Would this help:

sed -n '/^"[[:alpha:]]\{0,2\}",/p' csvfile

Hope this helps.

ghostdog74 · 03-11-2010, 05:23 AM

Code:

awk -F"," 'length($1)<=4' file

ziggy25 · 03-11-2010, 05:48 AM

Thanks. Both work perfectly. Does that mean the semi colon is included as part of the characters?
And also, how can i modify the awk solution so that it only selects the ones that dont have the "!" at end of the first column so that the output looks like this .

Code:

"fd","sdf","rdsfds","dsfd"
"fd","sdf","tdsfds","dsfd"
"faaav","sdf","tdsfds","dsfd"
"b","sdf","tdsfds","dsfd"
"fd","sdf","dsfds","dsfd"

I spent a whole week trying to achieve this with SQL loader and now you guys are telling me its do'able with a simple unix command. I feel stupid!

druuna · 03-11-2010, 05:57 AM

Hi,

Glad I (we) could be of help!

Quote:

Does that mean the semi colon is included as part of the characters?

Which semicolon [;] (do you mean comma[,])? Both examples do output the comma[,].

Quote:

I spent a whole week trying to achieve this with SQL loader and now you guys are telling me its do'able with a simple unix command. I feel stupid!

Thats not being stupid!! You "just" aren't around long enough to know what is possible

I'm still learning new stuff as I go along.

Hope this helps.

ziggy25 · 03-11-2010, 05:59 AM

Quote:

Originally Posted by druuna

Hi,

Glad I (we) could be of help!

Which semicolon [;] (do you mean comma[,])? Both examples do output the comma[,].

Thats not being stupid!! You "just" aren't around long enough to know what is possible

I'm still learning new stuff as I go along.

Hope this helps.

Sorry i meant the double quote.

druuna · 03-11-2010, 06:02 AM

Hi,

This is what the output looks like (input file contains your example in post #1):

Code:

 $ sed -n '/^"[[:alpha:]]\{0,2\}",/p' input 
"fd","sdf","rdsfds","dsfd"
"fd","sdf","tdsfds","dsfd"
"b","sdf","tdsfds","dsfd"
"fd","sdf","dsfds","dsfd"

The found lines are shown without any changes.

Hope this clears things up.

ghostdog74 · 03-11-2010, 08:43 AM

Quote:

Originally Posted by ziggy25

Thanks. Both work perfectly. Does that mean the semi colon is included as part of the characters?
And also, how can i modify the awk solution so that it only selects the ones that dont have the "!" at end of the first column so that the output looks like this .

Code:

"fd","sdf","rdsfds","dsfd"
"fd","sdf","tdsfds","dsfd"
"faaav","sdf","tdsfds","dsfd"
"b","sdf","tdsfds","dsfd"
"fd","sdf","dsfds","dsfd"

I spent a whole week trying to achieve this with SQL loader and now you guys are telling me its do'able with a simple unix command. I feel stupid!

why is this line

Code:

"faaav","sdf","tdsfds","dsfd"

selected ?
to check for "!" at end of column1, (as well as length 4)

Code:

awk -F"," 'length($1)<=4 && $1!~/\!\"$/' file

ziggy25 · 03-13-2010, 12:38 PM

Quote:

Originally Posted by ghostdog74

why is this line

Code:

awk -F"," 'length($1)<=4 && $1!~/\!\"$/' file

hi, i understood the first condition where it checks the length but how exactly does the second condition work?