Solved

Update a Postgres database from Excel Spreadsheet once a day

D

Hi everyone! I am new to the FME World and I am testing the following scenario:

Take a spreadsheet with lots of fields as input of several tables in a Postgres database. This unload should occur once a day.

Has anyone expertise on this?

Using FME Workbench 2019.1, my approach is to place the xlsx file as a reader. Then, use an attribute manager to remove certain fields and leave only those that are required for a specific table abc. After, I add an Postgres writer to to receive such information.

Is this a good approach? How/where do I drive the "insert" to a specific table? How it the scheduler implemented (if possible)?

Thanks a lot!

icon

Best answer by giosp 8 April 2020, 07:48

View original

14 replies

giosp
Rank
Badge

Hi @diogosanto,

I think your approach is correct. You can create a postgres writer for each table who you need to insert data. Each writer must have the specific field. Connecting it to the attribute manager do the job.

Do you have only fme desktop? You can schedule it on your computer by running the workspace from command line using the fme.exe and passing parameters ( example )

D

Hi @diogosanto,

I think your approach is correct. You can create a postgres writer for each table who you need to insert data. Each writer must have the specific field. Connecting it to the attribute manager do the job.

Do you have only fme desktop? You can schedule it on your computer by running the workspace from command line using the fme.exe and passing parameters ( example )

Hello @giosp! Thanks for the update and the clarifications provided!

I have only fme desktop at the moment. I'm gonna check example provided.

Best Regards!

D

Hi @diogosanto,

I think your approach is correct. You can create a postgres writer for each table who you need to insert data. Each writer must have the specific field. Connecting it to the attribute manager do the job.

Do you have only fme desktop? You can schedule it on your computer by running the workspace from command line using the fme.exe and passing parameters ( example )

I have setup this configuration, but where do I specify the table name in my writer? Not very clear. My database connection already configured.

giosp
Rank
Badge

If I understand correctly, you could add some postgres reader for each table that you need to update, add your postrgres writer and select the option to don't add tables at this moment and complete the adding procedure, select all your readers, right click and select the option duplicate on writer. Now all the tables are duplicated as the reader definitio. After that, delete the postgres reader and keep your writers. Connect them to the right input.

D

If I understand correctly, you could add some postgres reader for each table that you need to update, add your postrgres writer and select the option to don't add tables at this moment and complete the adding procedure, select all your readers, right click and select the option duplicate on writer. Now all the tables are duplicated as the reader definitio. After that, delete the postgres reader and keep your writers. Connect them to the right input.

Hello @giosp! Thanks for the reply, once again.

Here is my situation: I have a source that's a spreadsheet. I want to use this source as input to several database tables that are in my Postgres database. So I need to select which fields goes to which database table columns.

I'd very happy If i could see some documentation with examples, where the information is extracted (partially ou totally) and the inserted in a database table.

giosp
Rank
Badge

Hi @diogosanto,

in attach you can find an example. I hope that help you. example.zip

D

Hi @diogosanto,

in attach you can find an example. I hope that help you. example.zip

Awsome! Thanks a lot!

giosp
Rank
Badge

Awsome! Thanks a lot!

you're welcome. tell me if was what expected. bye

D

you're welcome. tell me if was what expected. bye

I think we are almost there. This is what I did. I took your example and I've created a corresponding table in my postgres database. After that, I have update some information accoding my database enviroment (like table name and table qualifier) and then executed. The execution is okay, but when I see the log (partially shown here):

Features Read Summary

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

==============================================================================

Total Features Read 0

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Features Written Summary

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

==============================================================================

Total Features Written 0

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

I was expecting to see rows inserted...

giosp
Rank
Badge

I think we are almost there. This is what I did. I took your example and I've created a corresponding table in my postgres database. After that, I have update some information accoding my database enviroment (like table name and table qualifier) and then executed. The execution is okay, but when I see the log (partially shown here):

Features Read Summary

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

==============================================================================

Total Features Read 0

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Features Written Summary

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

==============================================================================

Total Features Written 0

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

I was expecting to see rows inserted...

Why there aren't read features? Your excel sheet has data?

D

Why there aren't read features? Your excel sheet has data?

Yes, I am listing the one you have provided with four rows.

giosp
Rank
Badge

Yes, I am listing the one you have provided with four rows.

ah, ok. I thought that you've implemented your one. I've accidentally send you the workspace with the flag "redirect to inspector".

Please go to writer menu > click on Redirect to FME Data inspector . Now it should write in the db.

D

ah, ok. I thought that you've implemented your one. I've accidentally send you the workspace with the flag "redirect to inspector".

Please go to writer menu > click on Redirect to FME Data inspector . Now it should write in the db.

Problem solved! Congratulations man!

giosp
Rank
Badge

Problem solved! Congratulations man!

TOP, enjoy your workbench

Reply


Community Stats

28,634
Posts
109,182
Replies
36,514
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.