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

[PassION_es]

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.

[binuacs]

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

[PassION_es]

Hi @binuacs , 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.

[binuacs]

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

[PassION_es]

Hi @binuacs , 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.

[binuacs]

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

[PassION_es]

hello @binuacs , 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? 

[binuacs · Accepted solution]

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

[PassION_es]

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

[binuacs]

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

[PassION_es]

Sure, thanks :)