CSV File - Converting the Time Value

[StockMarket]

Hello

I have got data in few csv files which have these 3 issues, which needs to be fixed.

1. Sometimes the [Time] column has got a LEADING ZERO and sometimes not, ex -

09:15:59

9:15:59

I need to STANDARDIZE the data such that either all the rows will have 09:xx

or only 9:xx

but it should not be mixed up, as in the current state, where some rows have 09:xx while others have 9:xx

Please Note - my files have the data from 9 AM to 4 PM and this particular issue applies to only those data rows which belongs to the "hour 9 AM", because after 10 AM, this problem is no longer there for any other hours.

If you open the attached sample csv file in excel, then you will not see the LEADING zeroes, please open the file in normal text editor to see those zeroes.

2.  My file has got the [Time] column data as HH:MM:SS - Hours, Minutes and Seconds.

But I want to REMOVE the Seconds Part Completely, so that it just have the HH:MM in the output. Which formula will correctly remove only the SECONDS portion from the data and leave the HH:MM portion intact ?

3. In some data rows, there is an ADDITIONAL COMMA present at the Very End, which is a mistake. I do not want any data inside my file, after the last column named [Open Interest]. How do I make sure that all other data after this column is automatically deleted, whether it is just a simple comma, or anything else, I just want it to be deleted.

I have attached the Sample CSV File and Altreyx Workflow File.

Thanks

 

[afv2688]

Hello @StockMarket ,

would this work for you?

Regards

[StockMarket]

Thanks for the prompt reply @afv2688 

I am new to Alteryx and learning it slowly. The solution that you have given looks very complicated for me to figure out, what is it actually doing and how. My intention is to actually learn the simple ways of solving these problems, so that I can then start to do all this on my own. I would really hope that there is some easier methods out there to solve my current problem in a manner that I can understand it and replicate it on my big size csv data files.

Can someone please provide alternative solution to my problem?

Thanks and Best Regards

[afv2688]

Hello @StockMarket ,

I added a simple solution now. Hope it's easier to understand 🙂

Regards

[StockMarket]

Thanks again @afv2688 . I have been trying to understand the LOGIC behind the solution that you have suggested, because I would be replicating these steps into a Much Bigger Workflow, having many other tasks as well. I am not posting all those extra steps, just to keep things as simple as possible.

You seems to have made use of a "New Row of Data" manually entered, that simply have the Column Names inside them, right ? Will this create problems when I replicate this method on other csv file which has few differences in the column names etc.?

Is this method used to make sure we can delete all other data columns which are present beyond the Last Column of "Open Interest" or what? or if that step is being done only inside the Formula Tool having this value - Trim([Open Interest],',')

You also seems to have corrected both the issues related to the Time Column, but I could not figure out which particular Alteryx Tool is doing those 2 corrections inside the Time Column! Can someone please shed some light on this, because I have to replicate these steps into much bigger workflow and if I do not get the proper logic behind them, then simply copying them blindly will create problems into the workflow, down the line.

Regards

[afv2688 · Accepted solution]

Hello @StockMarket ,

Here I have added again a workflow with some explanation about what is being done in the process.

The second solution may be easier to understand. I use this format of reading the data to avoid any issues with the cells, by using the text to columns and the rename.

Next one comes the formula which removes the trailing commas. Agter that, the numbers were changed into number data type.

The following formulas will, change dates into date value, add the 0 when needed to the time and last will take the first 5 values from the time column.

Hope it helps

Regards

[StockMarket]

Hello @afv2688 

Thank you so much for making it easy to understand for a new guy like me, by adding small explanations to the Alteryx Tools being used in this workflow. I have learned many new tricks through your answer. And I deeply appreciate your kind help. Adding such small explanations, goes a long way for newbies like me. Otherwise everything goes over the head.

I have used these "Text to Columns Tool" and "Dynamic Rename Tool" for the first time in my workflow.
And this concept of using "Text to Columns Tool" for this work is very new to me. If possible, can you please elaborate a bit, why would you prefer using this tool and what kind of data issues can be avoided if we use this trick. Like in what type of user cases, this tools comes out handy.

One problem in using this trick is that first of all I will have to ADD this "Field_1" manually to each csv file, in order to make this tool work. Or if there is some Alteryx tool which could do this for me. Can you please suggest some method for adding a New Row at the very beginning of each of my csv files, which will have only this single value "Field_1" in it, because I do not want to add this to each csv files manually.

Regards

[afv2688]

Hello @StockMarket ,

When trying to open csv files sometimes you get an error which is well documented:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Error-reading-FILEPATH-MYFILE-txt-T...

To avoid the problems with it I do read the csv files directly formatted as shown on the input file tool.

If you check it you will see I have deselected the file to have headers, which will give you the "Field_1" automatically. I also think I increased the field length but not really sure. The renaming is then after used to get the headers back.

Regards

[StockMarket]

@afv2688Thank you so much for further explanations.

I initially failed to notice the new settings that you have done inside the INPUT data tool. I thought you have added the "Field_1" MANUALLY into the file. Now I see, that it is being added automatically, because of the settings that you did.

The article links is also very good for solving such csv files errors and it also gives a nice tip for handling the error data rows like this - "we can simply use a filter tool where the Record ID will be set equal to the record number that popped up in the error."

Thank a lot for helping me in improving my knowledge about Alteryx Tools.

Best Regards