Pipeline Excel Export Treating Numbers Differently

Question

The collection of data for my Excel.cvs export for BOM item part numbers (PN) is working nicely, except Excel is treating some part numbers differently.
In QB, all PNs are TEXT, some PNs are alphanumeric while others are numeric. When building the excel export routine in Pipelines, I am using the TEXT PN field in the "Part" header and defined as a STRING field.

When opening the Excel.cvs after it's complete, the following occurs. Now, I have tried prefixing the PN with a hyphen (') since Excel recognizes everything thereafter as text - but this does not work.
When selecting the two out of place occurrences below in the excel.cvs and manually adding a hyphen prefix in the excel address bar - all is well again and displayed correctly both position and value. The PNs that are acting differently, all seem to be of a numeric value within the text field PN.

When Pipelines defines the table, there are few options to set attributes of each column

Any insight into this painful anomaly would be appreciated.
------------------------------v/r,Chuck------------------------------

ChayceDuncan · Answer

This isn't a Pipelines or QB item - that just Excel being Excel. Especially since you're downloading it as a CSV - Excel will never understand the formatting aspect given that CSV's by their nature don't have formatting - it's just the value.

You could try sending the value as a formula, I've seen some success with that. So when you pass it in - pass it in as a string that looks like ="{{part number}}" and see if that works to force Excel when it opens to render it as a number.

Really the only good option is to not actually open Excel by default - but to open a new worksheet and load data from a CSV so that Excel will understand when it reads the data that it needs to interpret it as text as opposed to trying to guess.

------------------------------
Chayce Duncan
------------------------------

Sign up

Login to the community