Remove special characters - different excel files except slash (/) in dates

Hello Alteryx Team,

I have a workflow that converts excel file to .dat extension format using csv. The workflow already generate an output in .dat format however, it does not have yet cleanse the output. I need some help in the cleanse tools or any tool that will remove all special characters except to dates with /slash (date format is 01/01/2000) and except for period inside the amount (ex. 500000.25).

I only use cleanse tool when column headers are there. However, in this case, the excel file has no header that is permanent and the first row contains data that is dynamic, changes every month.

The question is, are there any tools other than cleanse tool that eliminates all special characters captured in excel file whatever data or number of columns/fields the excel file has as long as the slash(/) in all dates are retained? Also, is it possible to use one workflow to various excel files with different number of columns or different data set? We have different files that need to be converted from xlsx to .dat extension file and all these files may have different columns and dates are located in one or 2 of these columns.

Attached is the dummy input (excel) and workflow for your reference. Thank you in advance.

.dat file extension.yxmd

Workflow

Output

Common Use Cases

Dynamic Processing

Accepted answers

@PassION_es you need to make changes in the Dynamic Input tool like below

@PassION_es sorry for the delay in response. I removed the filter tool and updated the workflow. Can you try to run your input file and see if you are getting the values properly? If not can you create a dummy file very similar to the above file and send it to me?

Remove_Special_Char_1024.yxmd

All comments

Hey @PassION_es,

You can try using a RegEx Function and restrict the Characters to the ones specified. Everything else will be dropped.

When you want to do this for multiple columns you can use this regex-function in a Multi-Formula Tool.

Thanks @FrederikE ,

Would you mind to share in yxmd please? I tried using below tool, and tried running the workflow but did not remove the special characters inside the file.

Please see below highlighted in yellow.

@PassION_es Looking for something like below

Remove_Special_Char_1024.yxmd

hello @binu_acs , Thank you. This is what I am looking for. It seems that I will be using only one workflow to run and convert different file with dynamic number of columns. I only have a few questions to ask from the workflow and the output:

First, may you interpret the reg-ex or if condition you put in the formula so I may understood them correctly. I added some special characters in the 2nd formula. For the first formula, not sure why only the ! and * is trimmed.

In the output part, the result is correct. It converted from excel file to .dat extension format and it removes the special characters indicated in the formula. However the only one I noticed is that all blank fields/cells in the first row in the output has F10,F11,F12,etc. This should be in blank cells same with the original file. All excel input files converted to .dat file format have no column headers on it so we will assume that there are blank cells in the first line items and with data on the next line items. Please see snapshot of the output:

Thanks.

@PassION_es you can ignore the first Replace formula, i already added the same in the second ReplaceChar() formula, also in the output tool can you uncheck the option first name contains fields names

below is my output

Hello @binu_acs , The output did not captured the first line item when I untick the "First row contains field names" in the output configuration. Please see the attached workflow for your checking .

Dat File Conversion.v2.yxmd

@PassION_es Can you check if your input file tool has the below option checked?

Hello @binu_acs , Yes it has. The only difference with our input tool is that it is full path and the sheet is dynamic (you can refer to the yxmd I attached today).

@PassION_es Your Dynamic input tool output the records with the field name F1, f2 etc?

yes it is:

@PassION_es when you check the input tool First row contains data then the output column names of the dynamic input tool should be shown like below, starting F1, f2 etc but seeing your output heading it is taking first row as heading and assigning F12, F13 for the blank columns. Attaching my workflow

Remove_Special_Char_1024.yxzp

@PassION_es you need to make changes in the Dynamic Input tool like below

Hi @binu_acs, I forgot that there is need to tick mark when clicking the edit of dynamic input tool. And now it solves the issue.

Apologies, I just compared the current manual output of .dat file and the Alteryx output, and I only just noticed that if each of the dates have formulas, it will never be converted as dates:

Alteryx Output:

Current/ Manual - Should be Output:

First question, is there a tool that will remove the formulas in the current input file before removing the special characters. Please be informed that I already added in the formula the regex-'\d+\/\d{4}' for dates with format 06/2022.

Second question is, if you notice in the manual output, the commas before the highlighted date is counted as 4 (commas), but the generated output is only 2 commas, Is this has something to do with the code page used? I switch any code pages and tested the output (CSV) of each from ISO 8859-1 Latin 1 but same number (2) of commas is generated. Although, I haven't confirmed with the process owners if this is ok and accepted by the system for uploading. Just curious if you know the answer why its only generating two commas before dates.

3rd question is, the last line item is the total amount (this is with formula also).. In the Alteryx, it resulted to more than two decimal points but when generating manually, it only resulted to up to two decimals only.

Manual output: Sum total (last line item):

Alteryx output:

Thank you so much .

@PassION_es Can you provide a sample input file with all these scnario?

Hi @binu_acs , please see attached dummy input file. I just change the company names, dates and amounts and others but they have the same column arrangement, formatting and formulas on the scenarios I mentioned. Highlighted in blue are cells with formula.

dummy_alphal.xlsx

@PassION_es That is a strange behavior from Alteryx which converts the formula into a 1900-01-01 date format. Does your file always a .xlsx format or can be .csv? also the column with the date (F10 field ) is dynamic or stataic? I mean in all your input files the position of this date field will be in column F10?

Hi @binu_acs , the file is always .xlsx format. For this type of file, all date filed in column F10. But we have different type of files where in the field position (dates, amount, each) are different to each other files. I hope to use one workflow to convert all of them (regardless of amount and dates position) from xlsx format to .dat format.

Does Alteryx has the paste values that eliminates the formula and retain the values only? If none, then can I advise our tax team to eliminate manually the formulas in input file before running the workflow?

Here's the background or problem statement of this mini automation project:

Problem Statement: The process of saving a copy of .dat file is totally manual and time-consuming. Upon signatories' approval of file for BIR submission, Tax team will need to save a copy of file into a .dat file extension format in order for the data to be accepted in the BIR site/BIR system. From excel file, they manually save a copy into a csv format first, then opens the csv file to copy and paste data to a Notepad before it gets converted to a DAT file. This .dat file will then be used for uploading, however, the frustration starts when an error occurs (invalid vendor TIN, etc.). Tax team will need to update the excel file and they need to repeat the process of saving the data to a .dat file format over and over again until there are no errors detected from uploading.

@PassION_es If you can ask the Tax team to manually enter the date instead of using the formula will remove the data issues. I updated the regex formula to identify the format mm/yyyy and maintain the same in the output file. attaching the updated workflow. in the mean while i will also check is there is a way to get the values from the formula field.

Remove_Special_Char_1024.yxmd

hello @binu_acs , Apologies for the late response we're currently celebrating Philippines holidays.

I have yet to discuss with tax team with regards to manual elimination of formula in a the input data before running the workflow.

Our workflow runs fine with the comma as the delimiter. However, as what I noticed before, the difference in output is that number of commas for after each fields whether blank or not.

Take a look on below manual output from tax team ( Manual conversion from excel to csv to notepad -.dat file). You can see in the first line there are 7 commas at the right corner and on the succeeding rows you can see that there are 4 commas before 06/2022 dates.

However, in the Alteryx output, they differ in the number of commas which makes the output becomes invalid when importing or uploaded to the tax site.:

Here's the error from the site when uploading our output from Alteryx:

Is the error (or incorrect number of commas) is related to the transpose tool we used? as the transpose tool ignore blank fields?

@PassION_es sorry for the delay in response. I removed the filter tool and updated the workflow. Can you try to run your input file and see if you are getting the values properly? If not can you create a dummy file very similar to the above file and send it to me?

Remove_Special_Char_1024.yxmd

@binu_acs I received a confirmation from one of the tax team that there are no longers errors during uploading. Thank you so much I overlooked the filter tool.

@PassION_es good to know that the workflow is working fine, please let me know if you face any issues.

Sure, thanks

Hi @binu_acs , need your assistance again. I am trying to add "apostrophe (') in the regex formula as one of the characters to be removed but I am getting error when inserted that charcter to the regex formula.

current regex:

regex error when I try to include ('):

Also can we add in the regex or replace Ñ as N? Thank you.

@PassION_es use the below regex formula to remove the character '

IIF(REGEX_Match([Value],'\d+\/\d+\/\d{4}'),[Value],IIF(REGEX_Match([Value],'\d+\.\d+'),toString(toNumber([Value]),2,0),IIF(REGEX_Match([Value],'\d+\/\d{4}'),[Value],ReplaceChar([Value],"/'.,*!;",''))))

@PassION_es for the second question you can use the ReplaceChar() function like below

Thank you @binu_acs , it validated successfully now.

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703