Ask questions via twitter! Message any question to @answers on twitter. We'll publish the question and send you a reply each time there's a new answer.
Next Question

Answered Question

ryanscott ryanscott
 
M$10 June 07, 2009 10:25 PM

Combine a bunch of XLS files into ONE CSV file. List of all nonprofits in the US.

First person to do it TODAY (sunday) gets the tip.

All the XLS files from this page: http://www.irs.gov/taxstats/charitablestats/article/0,,id=97186,00.html combined into one CSV file, fields all in the right place.

email it to fortressgame@techhouseliving.com and let me know you have done so.

tips: use 'downThemAll' or some other extension to get all the xls files at once.
Interesting Question?  Yes (1)   No (0)   

Interesting: jasoncalacanis

RSS
 
 

Best Answer  Decided by Votes

wdawe wdawe
 
 June 07, 2009 11:34 PM
Working on it, this may take some time. When does Sunday end?


Helpful Answer?  (0)   (0)    Tip wdawe for this answer
Permalink | Report
   Reply  
 
 
robbrown robbrown
 
June 08, 2009 12:34 AM
@Wdawe:

I don't know how you're doing this... but if it's taking you a ton of time, maybe this will help:

You're a web guy, right?

Do you have admin access to a mySQL database?

Save each file from that page as a csv and import each one mySQL (should take about 5 min of opening and saving... the import will only take a minute after converting to csv)

Then, export the combined database as a CSV.

All of this can be done pretty quickly with the point-and-click gui phpMyAdmin that's included with any cPanel host. Of course, the LOAD sql command will read a csv if you're on the command line.

I thought that I'd post this in case you were copying-and-pasting all that stuff... but you may have found a better way and if so... never mind :)

Report
 
 
silverhamm... silverhamm...
 
June 08, 2009 12:48 AM
The problem I've run into is that the fields are defined by position, not tabs so it's important to create the commas at the right point.

As a real world project this would take up to four hours (MD$10/4=$2.50/hr x $0.75 (real $) = $1.87/hr). Including a necessary accuracy check (cut and paste even to make one document to import is limited by max file size (depending on the text editor you use).

I'm an hour into it (doing it just for fun) and saw that wdawe is working on it so I'll step down.

I hope it's a generous tip. Good luck, wdawe!

P.S. Using the xls files is definitely the way to go. Easy to export as a CSV file too. :)

Report
 
 
robbrown robbrown
 
June 08, 2009 01:06 AM
Yeah, I just gave it a go... this is pretty quick and easy.

Giver WDAWE!!!!

You don't have to use a database... I thought about it more and that's overkill.

You could just save each file as a CSV and then (if you're using windows) use the copy command: copy *.csv master.csv

That'll merge all of the csv files into one master.csv file.

So really, the longest part of this task is opening them up and pressing save as. If you wanted to get really fancy and slip through this in under 10 minutes total, you could create a macro to save as, open all of the files and watch some TV while everything works away.

Report
 
 
wdawe wdawe
 
June 08, 2009 01:20 AM
I'm using a perl script to do the conversion, it takes about 5 minutes per file so I'm browsing the web while it does the work. The biggest problem is that I've managed to kick the plug out twice (don't ask). If you want the money silverhammer you can have it, the hardest problem I had was finding the perl script and waiting long enough for it to do the conversion. I killed it off a couple of times because I thought it was hung. Otherwise I probably would have been done by now. I currently have 31 of the 53 files completed. Oops I meant 84 files.

Report
 
 
silverhamm... silverhamm...
 
June 08, 2009 02:36 AM
Nah, you earned it. I was doing it the hard way. :)

Report
 
 
wdawe wdawe
 
June 08, 2009 03:31 AM
All done. The final file is 515M, zipped it's 99M. There certainly are a lot of nonprofits in the U.S.A.

Report
 
 
jasoncalac... jasoncalac...
 
June 08, 2009 03:56 PM
Wow! great job everyone!!!!

Report
 
 

Other Answers (1)

Sort By
robbrown robbrown
 
June 08, 2009 11:51 AM
Nice :)

Helpful Answer?  (0)   (0)    Tip robbrown for this answer
Permalink | Report
   Reply  
 
 
wdawe wdawe
 
June 08, 2009 05:20 PM
for everyone's edification the completed file is at http://wdawe.com/final.zip

Report
 
 

Answer this Question

How tips and payments work

This question has already been resolved. You may add an answer to it but you will not be eligible to win best answer or any associated tips.

Voted "No best answer": beast1oh1,


Related Questions

View All Technology & Internet Questions

Ask a Question


140 characters left
Top of Page
Buy Mahalo Dollars with Credit Card or PayPal

Top Members

This Week All Time
  • cfinke
    cfinke
    2nd Degree Black Belt
    26761 Points
    M$29.75 Earned
  • bunnyphuph...
    bunnyphuph...
    2nd Degree Black Belt
    20771 Points
    M$756.54 Earned
  • thisjustme
    thisjustme
    Green Belt
    1145 Points
    M$76.05 Earned
   See All
 

Most Popular Tags

mahalo(1764)
twitter(971)
music(493)
iphone(481)
google(382)
online(346)
food(336)
internet(309)
beer(284)
money(281)
movies(277)
facebook(260)
apple(257)
answers(241)
aotd(235)
health(227)
free(224)
video(221)
dog(211)
windows(203)
   See All
 

Categories

Welcome New Members


 
 
Mahalo Dollars are the currency of Mahalo Answers.

Each Mahalo Dollar costs $1.

Once you earn more than 40 Mahalo Dollars, you can request to be paid via PayPal. Each Mahalo Dollar is currently worth $0.75 when paid out via PayPal. Learn More

 
 

Please log in to use this function.

Answers Home | Ask a Question | Earn Points | How Tips Work | Widget