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.
Solved! Go to Solution.
@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.
Best,
Jordan Barker
Solutions Consultant
@JordanB if I change the delimiters, do I need to change the file format? I do not find the txt file format.
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
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.
@tom_montpool Very detailed answer. give me lots of information. thank you so much!!!