Adding one column country for each file uploaded using macro

[SH_94]

Hi Community,

I had done importing the multiple file  (contain multiple subfolder - xlsx format) using macro . However, i would like to ask if we able to use Alteryx to perform the following  :

1. For all the file imported using marco,may i know how can i adding one column for country into the each file that had been uploaded. The screenshot below is the result that i wanted. I know how to add one column country if only one excel file imported However, i not sure how to build a dynamic workflow to accommodate all the excel file so that i can just add one shot for all the excel file with different country ? I Can anyone share the workflow if experience that before.

[AkimasaKajitani]

Hi @SH_94 

How decide the Country? Is it from File name or sheet name or any other information?

[jamielaird]

Hi @SH_94 ,

If the country is in the filename you can get this by choosing one of the options in "Output Filename as Field". This will add a column containing the Filename or Filepath, which you can then parse to get the country name.

If you can provide more detail on your exact data structure me or someone else will be able to help you.

[SH_94]

Hi @AkimasaKajitani ,

From the file that i have, it is from file name. I have attached the file below for your reference. 

I have more than 3 files and below are just the examples of the file name.

Basically i want to split the country and company code as shown below into two different column for each file.

 Singapore     1290

However, i not sure how to do the rename and how to do it in bulk

[SH_94]

Hi @jamielaird ,

You may refer to the attached file below for your reference.

[jamielaird]

Hi @SH_94 ,

Thanks for the examples. Here's what I suggest:

1. Read in the Filename as shown in my first post
2. Use Text to Columns with the delimiter set to a single space to split out the Country and Code into two columns

As long as all your country names are single words this will work fine. If you have multi-word countries we can do this with Regex instead.

[MarqueeCrew]

@jamielaird 

Before RegEx, you might explore right(filename,4)

and left(filename,length(filename)-5) if country is fixed length of 4. 

cheers,

 mark

[AkimasaKajitani]

Hi @SH_94 

Thank you for additional information.

The following Workflow will help you.

Basically, you have to use the option of "File Name Only" at Input Data tool, and then you have to use RegEx tool.

InputData Setting:

RegEx Expression: If you use the File Name Only at Output File Name as Field option, you can use this expression.

(.*)\s(\d+)

RegEx Setting