community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Loop through files and use Regex

Asteroid

Hi,

 

Let's say I have 5 different excel workbooks, and I want my regex function (ex: phone number regex) to read through all these workbooks based on their coloumn name and then parse it

 

Can I do that?

ACE Emeritus
ACE Emeritus

Sure.  You may need to read each workbook individually and union them together, then send them through a formula that applies the regular expreission, but yes - this is certainly possible. If you have sample data, we can help figure out the specifics.

Meteor

Shure you can!!

 

Youn only need to make sure (for make the things easier) that the field that you want to transform with Regex have the same 'field_name' for all the excel files:

 

The steps will be then (I will put images):

 

1) Call the excel files with a wildcard(*), I will suggest you to put the files that you need to process together in one folder (only to avoid to call other files) and make sure that the 'sheet' that contain the phones is the same for all the files.step1_wildcard.png

step1_phonefiles.png

 

With the (*.xlsx) I will call in my input folder any file with the extension .xlsx

 

2) Apply the regex formula, this could be using tthe formula tool or the regex tool from the Parse Section, in the picture I'm using a formulatool.

 

step2_regex.png

 

Hope this will help you, attached you will find the module and the excel files.

 

Have a nice week!!

Asteroid

I'm attaching an excel file that has 3 worksheets, but each sheet must be taken (i could not find a way to upload 3 excel files so I put them all in one) as seperate workbook (ex: sample1=workbook1, sample 2=workbook2, sample3=workbook3

 

my regex code is : 

^\d{5}(-\d{4})?$    (ZIPCODE)

 

I want to go through all files (workbook1,2,3) ->find "ZIPCODE" coloumn name -> then apply regex

 

Thanks

Asteroid

I'm trying this. Thanks! WIll update you

Asteroid

Ok,

 

1-)I put all 3 files in a folder where folder properties are C:\Users\fomenter\Desktop

2-)I opened alteryx, select "input data" then from "connect a file or database" I clicked on filebrowser

3-)A window pops out which commands "Open a data file" where I see my 3 excel files and they all have xlsx extension.

4-) Then, what do I choose here?

Untitled.jpg

 

5-) I chose one of the excel files (sample1), then clicked on Open, then changed the format to *xlsx. However, it still get to see sample1 headers

 

Untitled2.jpg

 

Thank you for all your help

Meteor

Try having the same columns in the 3 files with same schema, if you need to create columns in one or more file then just add the column name, isn't necessary fill up those fields with data.

Asteroid

Hmm interesting. I mean it works but always read through the first excel file

Labels