Alteryx designer Discussions

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

TFS to Excel - Removing Selected Carriage Returned

Highlighted
6 - Meteoroid

We don't know how they do it, but our Data & Analytics team finds carriage returns (CRs) in fields in TFS.  This pushes the data into different columns when copied and pasted into Excel or opened in Excel because every CR means "new row" to Excel.

 

Because the data in TFS is not uniform from project to project or query to query and because we don't know how many CRs will be in a single field, if any, we don't know how to identify which is the real "new row" CR.  As you've guessed, in order to get the data in the correct columns, we want to remove the the CRs in a single field and retain the CRs that mean "new row."

 

However, we're open to other fixes that don't concern CRs, if you know of any.

 

To summarize:

1) We want to move data from TFS to Excel

2) The data MAY have carriage returns for paragraphing in fields

3) Carriage returns are also used to signify a new row in Excel

4) The combination of paragraphing and new rows pushes data into the wrong columns/onto other rows

5) Is there a way to run the data through Alteryx to ensure the fields go into the correct columns on the correct rows?

 

Any help is greatly appreciated and I'm happy to answer questions to clarify my request.

 

Thanks!

Highlighted
Inactive User
Not applicable

Use RegEx replace for '\n' (newline/carriage return) or a formula tool with Trim([Field]) to remove these issues. Once you remove these you should be able to put to Excel and not have any issues. You can also put quotes at the beginning and end of each value before going to Excel so that it does not create a new line: Formula tool> '"'+[Value]+'"'

Highlighted
6 - Meteoroid

Thank you!  I'll give that a try.  But how do I get Alteryx to recognize which carriage returns really are new rows (new TFS entries) and which are being used for paragraphing in a free text field?

Highlighted
Alteryx Partner

If you can attach a sample of your data we can play with it and understand what is the best solution for your use case.

6 - Meteoroid

Makes sense.  Attached is a Word document of the data copied and pasted from TFS.  I chose Word because it easily identifies the carriage returns.  Of course the data can be imported into Excel or copied and pasted into text.

 

Thanks again for the help!

Highlighted
Alteryx Partner

@jbarnabyThe reason why you have more CR than expected is because they come from the column comment.

 

User insert CR in the comment and those will appear in the data.

 

The best solution to solve this problem would be to export the comment between quotes (e.g. if your comment is good, it should be in the file as "good".

 

Then you can easily read your file using this setting:

Capture.JPG

 

The delimiter is not being represented properly in your data, so I configured the input tool to read the data as a non delimited file, or "\0".

 

I have changed the format of the file in txt (csv will work as well).

 

 The workflow attached might work for you, however it is based on the assumption that the column "changed date" will never be blank.

 

Let me know if you need any clarification.

 

 

Highlighted
6 - Meteoroid

Hi!

 

Ok.  I am ASTOUNDED that you were able to do this.  This will take much studying on my part to figure out how you did it.

 

Since I didn't export the comment in quotes, did you do it in your workflow?  That's the part I'm not getting just from first read of your comment and workflow.  Your sentence, "The best solution to solve this problem would be to export the comment between quotes (e.g. if your comment is good, it should be in the file as "good"."  I don't understand how you accomplished that.  If you can give me some more insight, I will be grateful.

 

Thank you again, so much!  You're going to save my team LOTS of time.

 

Jenna

Highlighted
Alteryx Partner

@jbarnabysorry I might not explain myself well. I did not put quotes in comments.

That should be done by who is providing you the file.

As best practice, in txt, csv or Flat files, comments and text should be provided between quotes.

 

I try to explain what I did in my workflow.

 

1 - I have red the file without delimiters, excluded the first line (headers) and concatenating all the rows in the file. in this way I created an unique fields which contains all your data.

2 - I have created a row for each single character in the file. A row is a character.

3 - I have used a multi-row formula, which is saying: if the character is a CR then check the two character before. If they are "PM" or "AM" means that is the last columns (Changed Date) so leave the CR. Otherwise delete the CR.

4 - Re-concatenating the file, now it has not useless CR.

5 - Split the file to row, based on CR. At this point the rows are correct, but there is just one column.

6 - Split the columns to rows using the /t.

7 - giving at each column a number

8 - using the Dynamic tool to give the correct name at each columns.

 

I hope this is a bit more clear now.

5 - I have separated the row based on

Highlighted
6 - Meteoroid

Thank you for the explanation.  I agree that best practices should put the comment in quotes, but we won't be able to get the many users who enter the information to do that.  We will have to think on this.

 

Still, you were able to do it without the quotation marks.  :)

Highlighted
ACE Emeritus
ACE Emeritus

Hi,

Out of curiousity, does line 1 of Work Item Type always end up being  "Change Request"?  I think I have a potential solution if so, or if you have a short list of valid values.

Labels