Change of Column Order

  • 69Views
  • Last Post 30 July 2021
0
votes
Nicole Varner posted this 13 July 2021

Hello. My package # 120832 has custom column names, and I would like to seek your assistance for several changes. Can we please order the columns as follows?

A. 18 Digit ID
B. Organization Name
C. AKA
D. Telephone
E. Website
F.  Eligibility Start Date
G. Eligibility End Date
H. Address
I.  Address (line 2)
J.  City
K. State
L.  Province
M. Zip
N. Country
O. Organization Mission
P. iPhi Payee ID
Q. Tax ID

I hope to change the field source of the Address column to BillingAddressLine1, and for the column Address (line 2), I would like to add the field BillingAddressLine2. Thank you very much for your help!

Order By: Standard | Newest | Votes
0
votes
Olena Romanchuk posted this 30 July 2021

Hi Nicole, 

Thank you for your feedback! 
Please contact us in case of any questions. 

0
votes
Nicole Varner posted this 29 July 2021

This has worked perfectly! Thank you very much for all your support.

0
votes
Olena Romanchuk posted this 29 July 2021

Hello Nicole,

Yes, in this case you have to build the same query separately.

To do this, you have to create new query in the Query Builder



There you have to select your Salesforce connection first and build the same query as you have in your Export package.
Then you have to switch to the SQL tab and the script will be opened.
There please update the script: put replace(Account.Missionc, CHAR(10),' ') instead of Account.Missionc.
After that please copy the script, return to your package, select the Execute command Action and paste the script to the command field.
Please do not forget to save the package.

Please inform us about the result.


0
votes
Nicole Varner posted this 27 July 2021

Thank you for your help. Would you be able to provide more instruction on where to input the replace(Account.Mission_c, CHAR(10),' ') expression? Is it somewhere in this screen, in the Query section? I was also unable to find the SQl script/tab.

Do I have to build the initial Query separately from the Export package?

Thank you!

0
votes
Olena Romanchuk posted this 23 July 2021

Hello Nicole,

 

In the Advanced mode of your Export task you need to specify the target file name in the Target File Name box, otherwise the task will not be saved. We are currently investigating the opportunity to add timestamp to the preset filename in future and will inform you if it will be implemented.

 

You can remove the line breaks using SQL statement only.

To do this you can use Skyvia Query:

  • Please build your Query there in the Query Builder

  • then switch to SQL tab and replace the Account.Mission_c with the following expression replace(Account.Mission_c, CHAR(10),' ').

  • Then please copy the whole SQL script from Query, return to your package, switch the mode to Execute Command and paste the script into the Command field.

 

Please do not hesitate to contact us if any assistance is needed. 

 

 

0
votes
Nicole Varner posted this 20 July 2021

Thank you! I believe I have successfully been able to implement this on Package 134738. I do have two other questions I hope you may be able to assist me with. 

1. The Advanced Editing mode forces me to provide a target name. When I do so, I no longer am able to append the Date Time stamp. Ideally, the file name would be Account_{date}_{time}.

2. I have a field, Mission__c, that is a long text field and returns a block of text on separate lines. Is there any way to transform/map this into a field that would force all the text online the same line? i.e.:

      "This is the field line 1.

       And this is the field line 2"

to

       "This is the field line 1. And this is the field line 2"

Thank you!

 

0
votes
Olena Romanchuk posted this 14 July 2021

Hello Nicole, 

The opportunity to change the column names and columns order  in the target CSV file is available on the new runtime. 
Please refer to the updated documentation on how to perform actions in the export task here https://docs.skyvia.com/data-integration/export/how-to-create-export-task.html 

If you need further assistance, please feel free to contact us. 
 

Close