Question

Run SQL and export with pipe delimiter

  • 2 February 2023
  • 4 replies
  • 58 views

Badge +1

Dear All,

I’ve to run a query returning millions of record (no datatable usage possible) and export the result to a pipe separated CSV.

Do you know any solution/workaround to force a different CSV delimiter when exporting to excel?


4 replies

Userlevel 7
Badge +13

Hi @GabrieleC102784 ,

 

If you are working with the AA Database: Read action: the existing option within it to export the query result to CSV has only the option to change the encoding type. Other than that, I don’t think we can do much customization on it.

 

 

Badge +1

Thanks Padmakumar. 

This is the reason of my question, as no additional customization is possible on the action itself I was wondering if any workaround is possible (tweaking the configuration files for instance).

Userlevel 7
Badge +13

Thanks Padmakumar. 

This is the reason of my question, as no additional customization is possible on the action itself I was wondering if any workaround is possible (tweaking the configuration files for instance).

 

If you were using the Datatable action, there is an option to change the Delimiter while saving the file where you can mention the Pipe symbol under Other category.

 

Since you are dealing with huge data, please stick with Database action only. As a workaround, you need to some changes to your system to make this work.

 

1. Open the Control Panel on your computer. In the Control Panel window, please select Category from the View by drop-down list, then click the Clock, Language, and Region option.

2. In the Clock, Language, and Region window, click Region (or Region and Language) on the right side.

3. In the Region (or Region and Language) dialog box, click the Additional Settings… button.

4.Then the Customize Format dialog box is opening. Please remove the comma in the List separator box under the Numbers tab, then press the Shift + \ keys together to enter the | delimiter into the List separator box, and finally click the OK button.

 

If you don't want to do changes system wide, you may achieve the same through some inline scripts like Python or JS.

 

Badge +1

Finally I went for 4 lines python script to replace the delimiter without changing the parameters system wide.

Reply