Forum OpenACS Q&A: Excel Spreadheet X Postgresql

Collapse
Posted by Iuri Sampaio on
Does anyone have a way, a script, or something like that, to import info from an excel spreadsheet to a db postgesql table?

I imagine a way out could be:
- import from excel to access (which is pretty easy, there is a tool for that on access)
- import from access to MS SQLServer (which i have no clue how to do it)
- then from MS SQLServer to Postgresql (which i have no clue either)

Collapse
Posted by Claudio Pasolini on
Fortunately this is much more simple:
  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

Collapse
Posted by Iuri Sampaio on
hum... very good to know that.
by the way... how to i save my excel as acvs file
Collapse
Posted by Claudio Pasolini on
In the 'Save as ..' dialog choose 'csv' as file type.
Collapse
Posted by Iuri Sampaio on
I knew you would say that! =)
It doesn't show this option!
Collapse
Posted by Iuri Sampaio on
oops, i got it!
wrong old version!! .
Thanks a lot
Collapse
Posted by Iuri Sampaio on
Hi Claudio,

who never dies, always pass by!! =)

I've tryed to understand this solution, but i'm stuck on the first steps,

on the MS Excel, when i save the file as, what type of CVS file has to be? CVS(comma delimited)
CVS (machintosh)
CVS (MS0DOS)
what the diference between them?

now the code,
I want this data, which are contacts info, inside the table of the contacts package.

So, i already know what fields i need to insert the data.
I'd like to know how the data is treated. I mean, how the script will treat the data to insert on the fields of the table of the contacts pkg. Is that make any sense?

I believe i will visualize that, how the data is treated, only when i see the results on the browser in a select command. But I cant figure out the steps to get there.

Where should i post and adapt this script? in a random tcl file, which i created in a random test directory?

Collapse
Posted by Claudio Pasolini on
on the MS Excel, when i save the file as, what type of CVS file has to be? CVS(comma delimited) CVS (machintosh) CVS (MS0DOS)


I always use CVS comma delimited.


now the code,I want this data, which are contacts info, inside the table of the contacts package.


Here things are more difficult, because my example inserts data into a plain table, while 'contacts' are stored into the content repository and moreover makes use of the 'ams' package to dynamically extend the contact's attributes. You will have to use some package specific proc to load your data, but I can't help you because I never used it.


Where should i post and adapt this script? in a random tcl file, which i created in a random test directory?


Any place where you can point your browser is good.
Collapse
Posted by Iuri Sampaio on
hi claudio,

i just finished my script to import the data from a cvs file. I'm on the tcl file right now and i have no clue how i will run it. from the browser?? from where???

sorry for my delay

Collapse
Posted by Claudio Pasolini on
i just finished my script to import the data from a cvs file. I'm on the tcl file right now and i have no clue how i will run it. from the browser?

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

Collapse
Posted by Iuri Sampaio on
sorry, i didn't express myself well. the question should be how do i run the script? Do i need an adp file?
if so...what should be in the adp file??

i'm asking because sometimes on oacs i waste too much time doing things that was totaly unnecessary and there was a way out a lot quickly. or even details that i don't know.for example the sql files always need to be reload if i do change something on it.

thanks

Collapse
Posted by Iuri Sampaio on
Claudio,

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

Collapse
Posted by Claudio Pasolini on
Hi Iuri,

the easyest way to run your script is to fire it from your browser. You could also run it from the Tcl shell, but only if you don't use any Aolserver or OpenACS API.

You can write a message confirming that the script has finished, along with some statistics, both with an ADP page or with a simple ns_return command, at your choice.

Collapse
Posted by Iuri Sampaio on
Hi Claudio,

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??

Collapse
Posted by Iuri Sampaio on
take a look on the data inserted. There's " and misplaced info

http://www.myybiz.net:8090/master-contacts/list-contacts