Excel to Excel
Kitty Geiger asked 6 years ago
I have a need to create an Excel to Excel map that translates data.
My problem is that I have 1 row that needs to be replicated based on If a specific column has data. For example my fields are:Transaction Number, Date, Amount, UserDef1, UserDef2, UserDef3
My output file needs to be but only if data exists in those fields
.Transaction Number, Date, Amount, UserDef1
Transaction Number, Date, Amount, UserDef2
Transaction Number, Date, Amount, UserDef3
What is the best way to handle this scenario within SmartConnect without having the user to manually do it in Excel?
My problem is that I have 1 row that needs to be replicated based on If a specific column has data. For example my fields are:Transaction Number, Date, Amount, UserDef1, UserDef2, UserDef3
My output file needs to be but only if data exists in those fields
.Transaction Number, Date, Amount, UserDef1
Transaction Number, Date, Amount, UserDef2
Transaction Number, Date, Amount, UserDef3
What is the best way to handle this scenario within SmartConnect without having the user to manually do it in Excel?
Answers
Patrick Roth Staff answered 6 years ago
you should use an odbc data source on the excel file. then write a query for it and UNION ALL them together. Something like:
select [Transaction Number], Date, Amount, UserDef1 as UserDefined from [Sheet1$] where UserDef1 <> ”
UNION ALL
select [Transaction Number], Date, Amount, UserDef2 as UserDefined from [Sheet1$] where UserDef2 <> ”
UNION ALL
select [Transaction Number], Date, Amount, UserDef3 as UserDefined from [Sheet1$] where UserDef3 <> ”
This should put all non-empty user def fields on their own row.
select [Transaction Number], Date, Amount, UserDef1 as UserDefined from [Sheet1$] where UserDef1 <> ”
UNION ALL
select [Transaction Number], Date, Amount, UserDef2 as UserDefined from [Sheet1$] where UserDef2 <> ”
UNION ALL
select [Transaction Number], Date, Amount, UserDef3 as UserDefined from [Sheet1$] where UserDef3 <> ”
This should put all non-empty user def fields on their own row.