Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Issue with .xls having a ' (single quote in front of all data)

MJohnson10
6 - Meteoroid

Having an issue when doing an output to .xls

Capture.JPG

Each column has that single quote ' and it is hidden as you see it can only be seen in the highlighted area but not in the data cell. and i need the file format to be .xls but this hidden character has been failing to upload due to this character. any help would be greatly appreciated!

I am at a loss and any help would be appreciated!

- Michael Johnson

10 REPLIES 10
JoeS
Alteryx
Alteryx

Sorry, not quite sure what you are looking to achieve. 

 

Is this that your input data has these or the data you output from Alteryx?

 

 

MJohnson10
6 - Meteoroid

output from alteryx to an .xls format

JoeS
Alteryx
Alteryx

Alteryx will output any string fields with this value on the front to tell Excel that it's a string value contained in the cell.

 

What's the issue that it then causes?

MJohnson10
6 - Meteoroid

Hello,

the file is to be uploaded into a shipping terminal.

JoeS
Alteryx
Alteryx

Ah, and that then rejects them for having the single quotes?

 

Is there no chance they could accept the XLSX instead? (it has been around now for 12 years!)

 

If so, I think the only work around I'd know would be to use VBA\VBS. Alteryx uses Microsoft drivers (Access Database Engine) to create an XLS file, so it's something that is handled by that I believe.

JoeS
Alteryx
Alteryx

There is a VBA macro in here that should help.

 

Sub QuoteKleaner()
    Dim s As String, r As Range
    Dim rBig As Range

    Set rBig = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeConstants)
    For Each r In rBig
        If r.PrefixCharacter = "'" Then
            r.Value = "//" & r.Value
        End If
    Next r
End Sub
estherb47
15 - Aurora
15 - Aurora

Hi @MJohnson10 

 

If at all possible, save from Alteryx as an .xlsx format, and then try using Excel to save as an .xls.

 

Easiest solution that has worked in the past for me.

 

Cheers!

Esther

MJohnson10
6 - Meteoroid

Yes, i currently do that and i can confirm it works. 

But i do want to remove that manual step 🙂

MJohnson10
6 - Meteoroid

interesting, i have not used VBA before in alteryx

Labels