[ale] CSV file parser or alternative

[Mike Fletcher]

On Aug 5, 2006, at 9:49 PM, Jim Seymour wrote:

> On Sat, 2006-08-05 at 14:10 -0400, Mike Fletcher wrote:
>> On Aug 4, 2006, at 8:46 PM, Jim Seymour wrote:
>>
>>> Hi All,
>>>
>>> I am looking for a way to pull certain row/column values from a csv
>>> file
>>> to save in a text file that I can load into MySQL. I have not seen a
>>> direct way using MySQL to pull the data and load into the correct
>>> columns. The file is 15 lines long and is in this format;
>>>
>>> "ABIAX",20.63,"2006-08-03","6:00pm",-0.09,N/A,N/A,N/A,N/A
>>> "ACEIX",8.78,"2006-08-03","6:00pm",-0.03,N/A,N/A,N/A,N/A
>>> etc.....
>>
>> mysqlimport should handle CSV with the right options.
>>
>> http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html
>
> Thanks Mike,
>
> I will take another look at it. From what I had read before, I  
> couldn't
> figure out how to import selected row/column combinations. I am just
> trying to find the easiest, fastest way to import the data on a daily
> basis.

mysqlimport (or whatever DB native bulk load utility) is usually  
going to be the fastest option for getting stuff into the database.   
If your file doesn't exactly fit what it'll take it'd should be  
trivial to use Perl's Text::CSV_XS (or Ruby's fastercsv) to reorder  
columns or tweak values to match your schema.


http://search.cpan.org/~jwied/Text-CSV_XS-0.23/CSV_XS.pm

http://fastercsv.rubyforge.org/


Now if you need fancier munging (say you need to consult another  
table in the database to determine a column), you'd want to look at  
using DBI (Perl's or Ruby's; there's Rails' ActiveRecord as well for  
Ruby; or you could use something that sits on top of DBI like  
Class::DBI for Perl) to write a custom loader script.  This option  
isn't going to be as fast as the bulk load utility, but it has the  
advantage of allowing you to use your application logic to affect  
what's loaded.