import csv file into table

[Yebach]

Hello

I would like to import csv file into a database table - user imports

how do you recommend to do it. I guess telling users to set the column names matching the headers in CSV but how do i match them after for import.

I also have to add two fields with user id and another one that is based on user id to be inserted

My table is 

CREATE TABLE workers

(

  id serial NOT NULL,

  w_organisation integer,

  w_user integer,

  w_status integer,

  w_first_name character varying(32),

  w_last_name character varying(32),

  w_nick_name character varying(32),

  w_email character varying(64),

  w_note text,

  CONSTRAINT workers_pkey PRIMARY KEY (id)

)

I am strugling with import function

Please help i need this fast 

Thank you

[Massimo Di Pierro]

if your CSV has col names that match a table field names (as defined in web2py's db.define_table) you simply do

db.define_table('workers',Field('w_organisation'),...., migrate=False)

db.workers.import_from_csv_file(open('filename.csv'))

migrate=False because I assume the table already exists in database, set to to True if the table is to be created.

[Derek]

I would first suggest you export to csv (even a blank table) and then you will know the headers you need for your file.

[Vid Ogris]

I forgot to saay that I have an sqlformgrid in whoch i am importing

--
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/FhgWebuuk4o/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.

[Yebach]

This worked nice

my code at the end was

formImport =  SQLFORM.factory(Field('csvfile','upload',uploadfield=False))

formImport.process()

if formImport.accepted:

try:

db.workers.import_from_csv_file(request.vars.csvfile.file, delimiter=";")

except: 

formImport.errors.csvfile = 'Invalid file format.'

One more question

How can I set maximum nb of records user can import?

thank you

[Yebach]

If i do the 

if formImport.accepted:

import csv

fle =  csv.reader(request.vars.csvfile.file.read().splitlines())

line_count = sum(1 for row in fle)

print line_count

if line_count <= 100:

          try:

 db.workers.import_from_csv_file(request.vars.csvfile.file, delimiter=";")

 #URL('settings','workers')

     except: 

formImport.errors.csvfile = T('Invalid file format.')

else:

formImport.errors.csvfile = T('File is too big. Max 100 rows')

The data is not inserted. In general, everything I write after import_from_csv prevent records to be inserted.

I also want the page to reload/grid to refresh after file is successfully imported