CSV extractor/separator (not even sure what to call it)

S

seoserpent

meh
Apr 4, 2009
9
0
0
Roanoke, VA, USA
www.seoserpent.com
Can anyone advise a script, etc. or method to extract data from a CSV file based on a keyword in a specified column?

Basically what I'm looking to do is grab only the records in a gargantuan CSV file from an affiliate site and pull out only the products that are in a particular category or maybe have a particular keyword in their description, etc.

I've searched, googled, etc. but haven't found anything, but that could be because I don't know what the hell I'm searching for. :)

Cheers!
 


It's usually easier to just write an importer to import said records into a MySQL database and then just pull based on the results of specific columns.

Example:

7 fields for each row, make mysql database with
data1, data2, data3, data4 etc

then
SELECT * FROM `dataset` WHERE `data2` = 'CATEGORY'

That way if you need the other data later, and it's already ready in a MySQL DB, you can easily pull it later.

That's how I do it at least, so I can minimize the number of data entry points that I have and maximize the response rate.

How many records are we talking? 1 mil?
 
^ What Bofu said. Also, you'll have a lot more control over what you pull out with SQL. Just off the top of my head, you can do things like group by, order by, select distinct, pattern matching and 'like' / 'not like'...which will give you the ability to get exactly what you need, presented how you need it without much hassle.

Some examples of the kind of stuff you can do if you use SQL:

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
MySQL :: MySQL 5.1 Reference Manual :: 3.3.4.7 Pattern Matching
http://www.w3schools.com/SQl/sql_distinct.asp
 
If you can pull it into Excel and sort by category then delete all the other categories is the easy way to do that part.

The other is more complicated.
 
Theres a couple php functions that does this the whole script would be probably 5 lines.
 
bb_wolfe said:
You kids seriously need to learn Excel. Filter, bitches.
Click to expand...

Sure. You can do it with filters, formulas etc. If it gets complicated then you need to start fucking with pivot tables and macros. Fuck that. SQL is a lot cleaner and easy to deal with once you know the ropes. On top of that, OP said he had a huge set of data. Excel shits out at 65,536 records.
 
nvanprooyen said:
Sure. You can do it with filters, formulas etc. If it gets complicated then you need to start fucking with pivot tables and macros. Fuck that. SQL is a lot cleaner and easy to deal with once you know the ropes. On top of that, OP said he had a huge set of data. Excel shits out at 65,536 records.
Click to expand...

Not on 2007 it doesn't! Excel FTW no matter what.
 
seoserpent said:
Brilliant gentleman. A classic example of trying to overthink the situation! Thanks a million!
Click to expand...

ungrateful little turd....

ok I'm feeling nice so I'll tell you an easy method. You'll need two monitors for this.

Open your file in notepad on one monitor. Open a new notepad file on the other. Find the first line with the category you want and carefully type it out in the new file exactly as it is in the original (being extra careful not to miss any commas).

Repeat several thousand times until you've got them all then go fuck yourself.
 
Jizzlobber said:
CSV file editor, for Windows

Click on the 'misc' tab, enter your keyword and filter settings, click 'Save Filtered Items'
Click to expand...

Or you could just use Multiplan. It's dated 1985 and has been updated a few times. Updates in 95, 97, 2000, 2002, XP, 2003, and 2007. I hear there is a 2010 release soon.

Oh, they call it Excel now! lol


J/k man, but seriously, there is very little Excel can't do with a CSV, and filtering is super easy! Simply filter, click the values you want to show, and bam, done. All mouse-driven!
 
No doubt Excel can do the job, however CSVed doesn't sound an alarm and give me a retard box every time I save a CSV file.
 
bb_wolfe said:
Or you could just use Multiplan. It's dated 1985 and has been updated a few times. Updates in 95, 97, 2000, 2002, XP, 2003, and 2007. I hear there is a 2010 release soon.

Oh, they call it Excel now! lol


J/k man, but seriously, there is very little Excel can't do with a CSV, and filtering is super easy! Simply filter, click the values you want to show, and bam, done. All mouse-driven!
Click to expand...
Nick wants to have Excel's babies.
 
You must log in or register to reply here.
Top Bottom
Back