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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Microsoft Excel Legacy inconsistent field output (character loss)

Meteoroid

I'm working with two .xlsx templates with similar invoice formats on each,  ####-#######-## (4digit-6digit-2digit) all with numbers.

 

When I input template 1 I must change to Microsoft Excel Legacy as the Input tool's file format in order to keep raw data invoices as ####-######-##

When I input template 2, again I change Legacy file format, ####-######-## loses the '-'s and becomes ############.

 

All other excel file formats used (.xlsm, .xls, .xlsb) loses the '-' in both workflows.  Legacy works in template 1's workflow but not template 2's workflow.

 

I have several examples of files for both templates and they act as I mention above.  Each time resulting much like:

     
TEMPLATE 1  TEMPLATE 2 
     
RAW DATAAlteryx Legacy Input RAW DATAAlteryx Legacy Input
Invoice NumberInvoice Number Invoice NumberInvoice Number
2018-61133-002018-61133-00 2018-62233-0020186223300
2018-61127-002018-61127-00 2018-62212-0020186221200
2018-61110-002018-61110-00 2018-62203-0020186220300
2018-61110-002018-61110-00 2018-62203-0020186220300
2018-60648-002018-60648-00 2018-61753-0020186175300
2018-60648-002018-60648-00 2018-61753-0020186175300
2018-60614-002018-60614-00 2018-61752-0020186175200
2018-60614-002018-60614-00 2018-61752-0020186175200
2018-60605-002018-60605-00 2018-61751-0020186175100
2018-60605-002018-60605-00 2018-61751-0020186175100
2018-60300-002018-60300-00 2018-61309-0020186130900
2018-60300-002018-60300-00 2018-61289-0020186128900
2018-60300-002018-60300-00 2018-60813-0020186081300
2018-60298-002018-60298-00 2018-60813-0020186081300
2018-60298-002018-60298-00 2018-60813-0020186081300
2018-60298-002018-60298-00   
2018-59970-002018-59970-00   
2018-59970-002018-59970-00   

 

Note: Each template has it's own workflow.

 

Any help is appreciated, thanks.

Meteoroid

I should also mention that when viewing the raw data that both templates see the cell format as custom.

Quasar

Can you share sample data from both templates? I suspect the problem is related to Excel formatting. I.e. number stored as 12345678901 but formatted as ####-#####-## or similar.

 

One approach may be just to read in as .xlsx and format appropriately in your workflow into a new string field with a formula like

 

REGEX_Replace(Tostring([Invoice Number]),"(\d{4})(\d{5})(\d{2})", "$1-$2-$3")

 

 

 

Meteoroid

I do, ivoller, see attachments. 

 

I need to refrain from any regex/manipulation of the data.  I'm trying to see if on input the '-' doesn't drop on one template and not the other.

Labels