Skip to main content
Question

Map List to SQL Server Table

V

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.

3 replies

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Contributor
  • 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.

V

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+20
venkatakondepat wrote:

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.

 

Reply


Helpful Members This Week

Community Stats

31,372
Posts
118,993
Replies
39,004
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.