Apply filter to a csv file in DOS

October 20, 2010 at 07:27:59
Specs: Windows XP
Hi,

I have a csv file with the below columns,
date | mobileno | place | dob | etc.
now, if i want to apply filter on date column how do i do it using dos commands?
Would request to help me out in this.
Thanks in advance.


See More: Apply filter to a csv file in DOS

Report •


#1
October 20, 2010 at 08:05:08
find "20101010" < my.csv > new.csv


=====================================
Life is too important to be taken seriously.

M2


Report •

#2
October 20, 2010 at 08:22:37
>> find "20101010" < my.csv > new.csv

That will find the date in other column as well, eg the dob column. Better to set tokens and check the first column (date col) specifically.

GNU win32 packages | Gawk


Report •

#3
October 20, 2010 at 08:24:06
download gawk for windows then do this

C:\test> gawk.exe -F"|" "$1==\"20101010\"{print}" file.csv 

GNU win32 packages | Gawk


Report •

Related Solutions

#4
October 20, 2010 at 09:06:23
Hi ghostdog,

You're right that it will FIND anywhere on the lin.

OP didn't bother to say the date layout in ANY column.

And there's no tokens in DOS. LOL


=====================================
Life is too important to be taken seriously.

M2


Report •

#5
October 20, 2010 at 17:39:16
>> And there's no tokens in DOS

Nah, its cmd.exe. I am 99.99% sure. :)

GNU win32 packages | Gawk


Report •

#6
October 21, 2010 at 01:31:31
thankyou soo much.
i tried it with a small file and it went through smoothly.

but when am trying the same thing with a big csv file of approximately 10lacs rows, it takes toooo much time.
is there any solution for this?


Report •

#7
October 21, 2010 at 02:24:20
[1] What is "10lacs"?

[2] Post a few lines.

[3] Are you using XP?


=====================================
Life is too important to be taken seriously.

M2


Report •

#8
October 21, 2010 at 02:36:05
Yes, am using xp

the data in csv file is very huge, i tried doing it with mobile number, and it takes time but its ok, my purpose is getting solved.

the date format in the csv file is 10/20/2010, and am not able to pull out data with date.


Now i tried to "type" the csv file, and i found the date format as 20-Oct-2010.
And then i tried the same find command with the exact text (20-Oct-2010) and it worked out.
Its case sensitive. Thats gr8.
Thanks anyways.


Report •

#9
October 21, 2010 at 03:11:34
Now that I can filter and seperate data belonging to a date,,,
can I apply something like this >19-Oct-2010 (i.e greater than 19-Oct-2010) ?

Report •

#10
October 21, 2010 at 03:34:05
Maybe. But we still haven't seen any of the file.


=====================================
Life is too important to be taken seriously.

M2


Report •

#11
October 21, 2010 at 04:07:23
DATE Mobileno Place DOB NAME ADDRESS Status AGE
19-Oct-2010 9223205185 05-Dec-1980 Rajesh S Iyer Mumbai 30

These are the columns in the csv file.
the total data is more than 10,00,000

and i wanted to filter out data with dob >01-Jan-1970

the find command actually helped me in taking out data by entering one particular date.
Thanks


Report •

#12
October 21, 2010 at 05:47:44
if you want a native solution with better date comparison functions, use vbscript (or powershell if you desire), here's a vbscript solution

strFile = WScript.Arguments(0)
strDate = WScript.Arguments(1)
Set objFS = CreateObject( "Scripting.FileSystemObject" )
Set objFile = objFS.OpenTextFile(strFile)
Do Until objFile.AtEndOfStream
	strLine=objFile.ReadLine	
	s = Split(strLine,"|")
	If  CDate(s(2)) > CDate(strDate) Then
		WScript.Echo strLine
	End If 
Loop

usage :

C:\test> more file
19-Oct-2010|9223205185|05-Dec-1980|Rajesh S Iyer|Mumbai|30
19-Oct-2010|9223205185|05-Jan-1990|Rajesh Fryer|Delhi|29
19-Oct-2010|9223205185|05-Feb-1960|Rajesh Dryer|New|28

C:\test> cscript //nologo test.vbs file 01-Jan-1970
19-Oct-2010|9223205185|05-Dec-1980|Rajesh S Iyer|Mumbai|30
19-Oct-2010|9223205185|05-Jan-1990|Rajesh Fryer|Delhi|29

GNU win32 packages | Gawk


Report •

#13
October 21, 2010 at 06:07:58
could you pls help how and where to save the vbscript, and how to use it through dos...

Report •

#14
October 21, 2010 at 06:18:08
save it as any name you want. In my example, its test.vbs.
if you are a computer user, you should know how to save a file already.
look at my post again to see how its used. Whatever you type on the command line, you can put it into a file and call it a "batch" file.

GNU win32 packages | Gawk


Report •

#15
October 21, 2010 at 06:39:30
thanks for your response.
Have tried it, but getting error:
c:\test\test.vbs<8, 2> Microsoft VBScript runtime error: Subscript out of range: '[number: 2]'

since its a csv file, have tried the "," instead of "|"

by doing this, getting the below error:
c:\test\test.vbs<8, 2> Microsoft VBScript runtime error: Type mismatch: 'CDate'


Report •

#16
October 21, 2010 at 06:45:42
how did you execute it ? my script needs 2 arguments, the first is the file, the second is the date. Did you read properly my posts?

GNU win32 packages | Gawk


Report •

#17
October 21, 2010 at 06:52:18
the date was in 4th Column, so had to use CDate (s(3)), also the seperator was changed to "," since it was a csv file. This solved the problem.

Now, the data is displaying, but can i store it in another csv?

Thanks


Report •

#18
October 24, 2010 at 23:44:25
Guys, can anyone help me in storing the data in csv file.

Report •

#19
October 25, 2010 at 00:06:04
did it myself,


cscript //nologo test.vbs file 01-Jan-1970 f > newfile.csv


thanks anyways.


Report •

#20
October 25, 2010 at 03:47:10
Guys, can you help me in getting the same data, but i want to skip the first line from the source file

Report •


Ask Question