Replacing characters in a csv file from request.vars

[Gael Princivalle]

Hello all.

I want to replace some characters in a CSV file that I get from the user.

csvfile = request.vars.csvfile.file
#Replacing field separator ';' by ','
csvfile = csvfile.replace(';',',')
db.products.import_from_csv_file(csvfile)

Like that web2py give me this error message:

global name 'cvsfile' is not defined

Certainly I have to move the csvfile contents into a string variable, something like that.

Someone have an idea ?

Thanks, regards.

[Leonel Câmara]

Does this work?

csvtxt = request.vars.csvfile.file.read()

csvtxt = csvtxt.replace(';', ',')

from cStringIO import StringIO

db.products.import_from_csv_file(StringIO(csvtxt))

However, this is not necessary as you can send the delimiter to import_from_csv_file. So this may be enough:

db.products.import_from_csv_file(request.vars.csvfile.file, delimiter=';')

[Gael Princivalle]

Thanks Leonel

No it don't.

>However, this is not necessary as you can send the delimiter to import_from_csv_file. So this may be enough:

>db.products.import_from_csv_file(request.vars.csvfile.file, delimiter=';')

Great but I need to replace other characters.

----------------------

Gael Princivalle

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/rDIOmGTxSRs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Leonel Câmara]

How are you creating your csv file input?

Along with delimiter you can also change quotechar and quoting.

[Gael Princivalle]

I'm creating the csv file like that.

From a CRM I export data in the only format available, Excel.

From Excel I export in CSV.

After that I have to replace a lot of things inside the file.

First "," with "." for decimal separator.

";" with "," for field separator

"FALSO" with "0"

"VERO" with "1"

and all the first line (field names)

Do you have a solution ?

[Leonel Câmara]

No, what I mean is, can I see the form you use to upload. And can you show me more of that controller and/or more of the ticket error. The error you're getting "global name 'cvsfile' is not defined" doesn't make sense to me.

An alternative is to read the excel directly using xlrd ( http://www.python-excel.org/ ) however we should be able to make this work.

You can also provide a sample csv and your define table that's importing it and I might try to understand what's going on Sunday or so.

[Gael Princivalle]

Thanks Leonel.

Here is my form:

    form = SQLFORM.factory(

        Field('table',requires=IS_IN_SET(db.tables)),

        Field('csvfile','upload',uploadfield=False))

    form.process()

    if form.accepted:

        table = form.vars.table

        csvtxt = request.vars.csvfile.file.read()

        #Replacing decimal point ',' by '.'

        csvtxt = cvstxt.replace(',','.')

A csv file sample:

id_crm;code;description;price_list;special_offer

407;04050100851;DRFRR1;59.61;FALSO

408;04050100852;DRFRR2;59.61;VERO

----------------------

Gael Princivalle

--

[Leonel Câmara]

This worked for me:

Model:

db.define_table('products',

    Field('id_crm', 'integer'),

    Field('code'),

    Field('description', 'text'),

    Field('price_list', 'decimal(10,5)'),

    Field('special_offer', 'boolean'),

)

Controller:

def from_csv():

    form = SQLFORM.factory(

        Field('table',requires=IS_IN_SET(db.tables)),

        Field('csvfile','upload',uploadfield=False))

    form.process()

    if form.accepted:

        table = form.vars.table

        csvtxt = request.vars.csvfile.file.read()

        csvtxt = csvtxt.replace('VERO', '1')

        csvtxt = csvtxt.replace('FALSO', '0')

        from cStringIO import StringIO

        print csvtxt

        db[table].import_from_csv_file(StringIO(csvtxt), delimiter=';')

    return locals()

View:

{{extend 'layout.html'}}

{{=form}}

With this, I was able to easily import the csv sample you provided.

[Gael Princivalle]

Shame on me.

I've wrote cvstxt instead of csvtxt... Anyway you've teach me some tricks, thanks a lot.