Skip to main content
Question

Map List to SQL Server Table

  • April 25, 2018
  • 3 replies
  • 45 views
venkat
Participant
Forum|alt.badge.img+1

I am reading table name and attribute values from text file. I do not have column names but only table name and values in the list.

I would like to insert the values into SQL Server table. How do I map list to destination table schema. The order of column values match with table structure.

Following is the example of text file row.

I~6000000~PDEN ~A058951~PI~PI~DEHYDRATOR~~261384~~~B&C; COMPRESSOR STATION~802103~~~~~~~~~~~~A~~A~USA~17~027~~61~022033~~~~~~20171001~~~~~~~~N~~~~CONGRESS_PRD~~3~~~~~~~~~~~~~~~~G~LA~~L~~~~~~~~~~~~~~~~~~~~

'I' is the transaction type.

6000000 is table id

PDEN is the table name.

Rest of them are Attribute Values.

Text file can contain values for multiple tables and it can have update or deletes also.

For now I am trying to find a solution for inserts.

Please let me know your thoughts.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

3 replies

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Contributor
  • 311 replies
  • May 1, 2018

You can use the AttributeSplitter to split up the text on the tilde (~) character, and you can choose to "Drop Empty Parts" to deal with all those consecutive tildes. Or if consecutive tildes mean empty columns, don't "Drop Empty Parts".

Do you know the column names? If so, you can map the values to the correct columns in the AttributeManager, picking up the relevant element in the list that AttributeSplitter produces.

Tester or TestFilter can filter out just the features to insert.

You can use the table name attribute as the Fanout on the Writer to insert the record into the correct table.

I'm unclear what the table id is - is this the primary key value in the table you are writing to?

If you then want enhance the Workspace to deal with updates, you can use the I, U, D values to set the fme_db_operation value for the Writer.

venkat
Participant
Forum|alt.badge.img+1
  • venkatParticipant
  • Author
  • Participant
  • 1 reply
  • May 2, 2018

Hi Tim, Thank you for your answer. I am trying to download textlinenone.fmw but it throws an error.

Can you please re upload the file again?

Regards,

Venkat

debbiatsafe
Safer
Forum|alt.badge.img+21
  • debbiatsafeSafer
  • Safer
  • 648 replies
  • May 2, 2018

Hi Tim, Thank you for your answer. I am trying to download textlinenone.fmw but it throws an error.

Can you please re upload the file again?

Regards,

Venkat

Hi @venkatakondepat

 

If you right-click on textlinenone.fmw and select 'Save Link As' (or your browser equivalent), you should be able to download the workspace. Hope this helps.

 

Community Stats

32,087
Posts
121,850
Replies
39,729
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.