This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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]+'"'
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.
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.
@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.
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. 🙂