[Eug-lug] Importing a .csv file into MySql

[larry price]

I don't usually do people's homework for them, but I thought I'd share
my template script here, this is very basic code, it is a starting
point and nothing more.

Actual production code would use a sturdier parser,  and usually does
some safety and consistency checks, and may go so far as to check
uniqueness of some fields.

Ron, if you change it to read your file in and write your own output
template to match the labels of your columns this can do what you
want.

Python is on most every current linux distribution by default and is
extensively documented, it shouldn't take you more than a couple of
hours to figure out the bits you want.

#!/usr/bin/env python


def read_raw_csv(lines):
    separator = ','
    lines = [l.strip() for l in lines] #strip trailing '\n'
    lines = [l.split(separator) for l in lines] #assumes no quoting
    labels = lines[0] #assumes the first line is column labels
    rest = lines[1:] #data rows
    def dict_generator(labels,rest):
        for line in rest:
            yield dict(zip(labels,line))
    return dict_generator(labels,rest)


if __name__=='__main__':
    #lines = open(filename,'r').readlines()
    lines = ['foo,bar,baz\n','1,2,3\n','1,3,3\n','2,1,3\n'] #this is test data
    cooked_lines = read_raw_csv(lines)#lines is now a list of
dictionaries
    output_template = "%(baz)s|%(foo)s|%(bar)s"
    # %(name)s is python for insert variable _name_ as a string here
    for d in cooked_lines:
        print output_template % d
    print "and just for comparison"
    for line in lines: print line,


On 8/22/07, Ron LeVine <levine.ron at gmail.com> wrote:
> Well, unfortunately, there is no money for this project until she is
> actually able to go live and I am not making anything off of this. Just
> trying to assist a friend.
>
> Thanks again,
> Ron
>
>
>  On 8/22/07, Ben Barrett <stircrazyben at gmail.com> wrote:
> > Or if you are in a business pinch, you could say you're willing to pay $o
> many dollar$
> > to get this done, and that the CSV contains so many columns/fields, also
> give a sense
> > of how complicated the job is.... or that you need someone to check it out
> to give you
> > an estimate.  It sounded like you were seeking DIY-type help at first; I'm
> sure you can
> > find paid "get're done" help here too :)
> >
> > ben
> >
> >
> >
> >
> > On 8/22/07, Allen Brown <abrown at peak.org> wrote:
> > > The answers you are getting are geared toward telling you where
> > > to learn how to do the job.  If you want more specific help
> > > you may want to be more specific with your question.  Posting
> > > the exact format of the data and how you want it to be
> > > organized under MySQL will empower folks to give you a better
> > > answer.
> > > --
> > > Allen Brown  abrown at peak.org
> http://brown.armoredpenguin.com/~abrown/
> > >    Always forgive your enemies -- Nothing annoys them so much. ---Oscar
> Wilde
> > >
> > > Ron LeVine wrote:
> > > > Well, guys, you both are talking way over my head on this.
> > > >
> > > > Oh well.
> > > >
> > > > Thanks anyways,
> > > > Ron
> > > >
> > > > On 8/22/07, *larry price* < laprice at gmail.com
> > > > <mailto:laprice at gmail.com>> wrote:
> > > >
> > > >     On 8/22/07, Ron LeVine < levine.ron at gmail.com
> > > >     <mailto:levine.ron at gmail.com>> wrote:
> > > >      > Greetings all,
> > > >      >
> > > >      > I have a .csv file from a spreadsheet that I want to import
> into
> > > >     a MySQL db.
> > > >      > Unfortunately, the format of the file is not the same as the
> > > >     field list in
> > > >      > MySQL so I think I need a script to parse the file and then do
> > > >     the MySQL
> > > >      > commands to place the data where it goes.
> > > >      >
> > > >
> > > >     This is a very straightforward data filter script, the general
> pattern
> > > >     goes like this
> > > >
> > > >     using your favourite scripting language
> > > >
> > > >     read each line of the file into a hash table (dictionary)
> > > >
> > > >     write the output function that does whatever formatting,
> interpolation
> > > >     and calculation you need to produce the database table row you
> need
> > > >     from the hash structure you have.
> > > >
> > > >     apply the output function to each line, and either stuff the
> result
> > > >     into the database directly, or into a file for importation later.
> > > >
> > > >     (for larger data sets the copy function your DBMS has is
> significantly
> > > >     faster than inserting row by row)
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >      > I am not much of a script guy. Anyone care to assist on this???
> > > >      >
> > > >      > Thanks in advance,
> > > >      > Ron
> > > >      >
> > > _______________________________________________
> > > EUGLUG mailing list
> > > euglug at euglug.org
> > > http://www.euglug.org/mailman/listinfo/euglug
> > >
> >
> >
> > _______________________________________________
> > EUGLUG mailing list
> > euglug at euglug.org
> > http://www.euglug.org/mailman/listinfo/euglug
> >
> >
>
>
> _______________________________________________
> EUGLUG mailing list
> euglug at euglug.org
> http://www.euglug.org/mailman/listinfo/euglug
>
>