Skip to main content
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!

Best answer by giosp

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 )

View original
Did this help you find an answer to your question?

14 replies

giosp
Contributor
Forum|alt.badge.img+6
  • giospContributor
  • Contributor
  • Best Answer
  • April 8, 2020

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
  • diogosanto
  • Author
  • April 8, 2020
giosp wrote:

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
  • diogosanto
  • Author
  • April 14, 2020
giosp wrote:

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
Contributor
Forum|alt.badge.img+6
  • giospContributor
  • Contributor
  • April 14, 2020

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
  • diogosanto
  • Author
  • April 14, 2020
giosp wrote:

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
Contributor
Forum|alt.badge.img+6
  • giospContributor
  • Contributor
  • April 15, 2020

Hi @diogosanto,

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

D
  • diogosanto
  • Author
  • April 15, 2020
giosp wrote:

Hi @diogosanto,

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

Awsome! Thanks a lot!

giosp
Contributor
Forum|alt.badge.img+6
  • giospContributor
  • Contributor
  • April 15, 2020
diogosanto wrote:

Awsome! Thanks a lot!

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

D
  • diogosanto
  • Author
  • April 15, 2020
giosp wrote:

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
Contributor
Forum|alt.badge.img+6
  • giospContributor
  • Contributor
  • April 16, 2020
diogosanto wrote:

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
  • diogosanto
  • Author
  • April 16, 2020
giosp wrote:

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

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

giosp
Contributor
Forum|alt.badge.img+6
  • giospContributor
  • Contributor
  • April 17, 2020
diogosanto wrote:

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
  • diogosanto
  • Author
  • April 22, 2020
giosp wrote:

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
Contributor
Forum|alt.badge.img+6
  • giospContributor
  • Contributor
  • April 23, 2020
diogosanto wrote:

Problem solved! Congratulations man!

TOP, enjoy your workbench

Reply


Helpful Members This Week

Community Stats

31,182
Posts
118,182
Replies
38,791
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.