Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Create a new file with delimiters

Inactive User
Not applicable

I have a CSV file which was output from the database. In this file, it contains some characters , URL's, apostrophes, parenthesis, commas, etc.  When I send this file to someone, they told me their system can not read/load my file. They suggest me to create the delimiters for the file. 

 

I would like to use Alteryx to create a new file (excel, csv, txt or any ) use either a comma, tab or pipe as the delimiter and all other punctuation should either be eliminated or quoted out. How can I do that? 

 

Or can Excel do it directly?

 

Thank you.

5 REPLIES 5
JordanB
Alteryx
Alteryx

@Inactive User

 

Using an output data tool at the end of your workflow. You can select CSV and in the options section within the configuration window to the left, choose which delimiter you want to output the file as.

10-11-2016 2-57-38 PM.png

Best,

 

Jordan Barker

Solutions Consultant 

Inactive User
Not applicable

@JordanB   if I change the delimiters,  do I need to change the file format?  I do not find the txt file format.

JordanB
Alteryx
Alteryx

Hi @Inactive User

 

I saw that you suggested CSV which will give you the choose of delimeter in the output tool. You can still open this up as a txt file. I just tested with notepad.

 

If you want to do txt you will have to choose Ascii .flat file option in the output tool but this will not give you the option for a delimeter. 

 

Best,

 

Jordan Barker

tom_montpool
12 - Quasar

If you want to change the delimiter, choose CSV as your output file type. Specify the delimiter -- the default is a comma, but you can use \t for a tab, | for pipe, or any other character (I've used and ~ as well.)

 

Just because you're using CSV as your output type doesn't mean your file has to have a ".csv" extension -- you can override this with a ".psv" if, for example, you're doing pipe delimited. In the screenshot of the Output Data configuration, you would just change "test.csv" to "test.psv". You could also change it to "test.txt" -- the extension specified on your output file really has no impact.

 

@JordanB is correct that if you are following conventional file extension use, you would use the "flat file" output option to create a file with the .txt extension. This is just conventional use, not a rigid standard.

 

The other thing you asked about was that "all other punctuation should either be eliminated or quoted out". If you want to use the "quoted out" option, change the "Quoted Output Fields" from Auto (default) to Always. This puts quotation marks around every record, regardless of the presence of special characters like punctuation. If you want punctuation eliminated, you would have to change your file contents before it gets to the output stage -- a Multi-Field Formula tool with a Regular Expression would be the way to go about this.

Inactive User
Not applicable

@tom_montpool Very detailed answer. give me lots of information. thank you so much!!!

Labels