Export as a delimited file - can I specify a different row delimiter than the column delimiter?

Toad Data Point
1

I have a need to export tables from SQL Server to a delimited file using a column separator of ^ and row separator of @@@. So the flat file looks like this:

row1col1^row1col2^row1col3@@@row2col1^row2col2^row2col3@@@

and so on.

I'd dearly love to automate this with Toad Data Point, but I don't see any way to specify the row delimiter. Can this be done?

2

It looks like TDP can handle your column separator, but I'm not seeing a way to define the row separator char string. This would be a good enhancement request, if you could please define it in TDP's Idea Pond: https://forums.toadworld.com/c/toad-data-point/idea-pond

That said, you may need to automate this in TDP with a two-step process:

  1. Have the Export Wizard task within the Automation Designer export your table data as "delimited text" and define your column separator as "^"

  2. Use the Run Program task to invoke a shell script (batch file with editing commands, Python, etc.) that replaces the EOL characters in your exported file with your row delimiting phrase "@@@".

Hope this gives you some ideas.

3

Thanks for the suggestion.

4

Let me know if it works out for you!...

5

Can you use TOAD to export to a CSV file using a 2-character delimiter between values such as |^ ?