Import tidy help

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

Data Issue.TXT

Topic of Interest

Accepted answers

gawa

@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.

1243330_gawa_Rev1.yxzp

All comments

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.

1243330_gawa.yxzp

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

@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.

1243330_gawa_Rev1.yxzp

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).

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280