Forum OpenACS Q&A: Re: Excel Spreadheet X Postgresql

1. save your spreadsheet as a csv file
2. write a simple script to read the file and load its rows into your PostgreSQL tableYou may want to have a look at ns_getcsv or simply change to your needs the following sample script:

   ad_page_contract {
       Bulk loads pay_banks from file.
       @author Claudio Pasolini
   } {
   }
   set user_id    [ad_conn user_id]
   set input  [ah::package_root]/import/banks.csv
   # open input file
   set file_inp [open $input r]
   # read the input file splitting it into lines
   set lines [split [read $file_inp] \n]
   close $file_inp
   set i   0
   db_transaction {
   
       foreach line $lines  {
   
   	set record $line
   	set line [split $line {,}]
   
   	# skip blank lines and bogus records
   	if {[string equal $record ""]} {
   	    continue
   	}
   
   	incr i
   
   	# decode input fields
           util_unlist $line \
               bank_code \
               bank_name \
               abi_cab \
               acct_plan_1 \
               acct_1 \
               acct_plan_2 \
               acct_2
   
   	# creates pay_banks
           db_dml query "
           insert into pay_banks values (
               :i
               ,:bank_code
               ,:bank_name
               ,:abi_cab
               ,:acct_plan_1
               ,:acct_1
               ,:acct_plan_2
               ,:acct_2
               )"
       }
   }
   
   set html "
   Tutte le $i righe del file sono state elaborate senza errori."
   ns_return 200 text/html $html

Yes

from where?
As already said, you can put your script almost anywhere under the service root, e.g. in the www directory of your package

i got the script quite working. It's missing just to test on the real db.

sorry for the dumbest question about how to run the script. But OACS sometimes drives me crazy with few detaisls and i end up wasting too much time on easy little things that i didn't even tought it was needed

anyway, i'm learning and i believe this is a process that takes time whem we talk about OACS

i run the script and it's working fine, I mean the data has been inserted.
The only problem is some data comes with " or missplaced. I'm quite sure it's because of there's something wrong in the csv file.

Is there another way to get a good csv file??