Import tidy help

[RDF25087]

Hi all -

I am trying to import some data but some additional pipes in one of the columns is throwing my data out, and I can't seem to clean it up.

I have attached an example of the data. In the Product ID column the part number has additional pipes. So, in the example provided the number appears like this:

1| 23-4-5-00||609

The number should appear as:

1--23-4-5-00--609

Any help would be greatly appreciated.

RDF

[BS_THE_ANALYST]

@RDF25087

Just to clarify:

---

@RDF25087 wrote:

Hi all -

I am trying to import some data but some additional pipes in one of the columns is throwing my data out, and I can't seem to clean it up.

I have attached an example of the data. In the Product ID column the part number has additional pipes. So, in the example provided the number appears like this:

1| 23-4-5-00||609

The number should appear as:

1--23-4-5-00--609

Any help would be greatly appreciated.

RDF

---

How does this happen? You can replace all instances of a double pipe with 1 pipe using a replace formula. i.e. replace , "||", "|". I think that cleaning step is useful.

I struggle to see how the 1 at the start is dragged into this as it's separated by a single pipe.

All the best,

Ben

[apathetichell]

Is this an SAP extract? this looks yucky. agreed with @BS_THE_ANALYST - I think unless you have concrete rules what you are looking for requires psychic powers.

[gawa]

hi @RDF25087 

I understand context and your requirement as follows:

1) Need to parse data from .txt file into table format

2) .txt data contains | as a delimiter, but unfortunately a particular field(Product ID) contains | as a part of value

3) For this reason, # of | differs between header row and data rows that makes parse difficult so unnecessary | should be replaced with -.

Based on this understanding, here is my solution. Please note that this WF would work well as far as input data schema is always the same.

[RDF25087]

@apathetichell  Ha! Yes, this is a SAP extract and yes, it's a flippin' pain in the backside.

[RDF25087]

@gawa  Thank you for your post - which works brilliantly. However, my actual dataset has over 60,000 rows - when I try to plug this data into your workflow it only returns the first line?

Thanks

RDF

[apathetichell]

There should be a way to change some of how this outputs in SAP. I'm pretty removed from SAP right now - and I could tell you that on the DVW tools this can be fixed by setting the field headers. I think in RFC you can fix this as well. 

[gawa · Accepted solution]

@RDF25087 Thank you for your feedback. Now I made correction so it reads all records after row number 7, and exclude the row starting from -.

It should work on your dataset having 60,000 rows. Pls check it.

[Simon_Theobald_SW]

Hi RDF,

As you're extracting SAP data, I might suggest to have look at Xtract for Alteryx. It is an Alteryx plugin that allows you to extract SAP data using different options such as connecting to SAP Tables, Reports, Function modules, SAP Extractors (ODP), and more. It's easy-to-use, certified by SAP and has a free trial available at https://theobald-software.com/en/xtract-for-alteryx
If you have any questions, feel free to reach out to your Alteryx account team (it's the only SAP connector that can be bought directly from Alteryx thanks to a close partnership).