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.&nbsp;

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.&nbsp;

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.&nbsp;------------------------------Chayce Duncan------------------------------

charlesdulaney · Answer

Chayce,
Thanks, that didn't work either. I may try Google Sheets and then bring it back into QB.
------------------------------v/r,Chuck------------------------------

chayceduncan · Answer

What is the end result you're looking for? Are you sending out an Excel file for someone to make edits to then pass back into Quickbase? Just curious what role Google would play in this scenario to see if there might be a third door to consider as well.

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

charlesdulaney · Answer

Chayce,
When we are done adding PNs to our BOM, we generate the BOM in Excel.cvs with headers the manufacturer wants to see. When we are ready, we click a button to generate the BOM, ECN and all drawings.
Recently, the manufacturer sent the BOM back to us claiming incorrect PN for a select few.
Google Sheets may not create this anomaly. As I mentioned earlier, some PNs are alphanumeric, and some are numeric. Meanwhile, numeric PNs are correct until they are added to an Excel sheet.

------------------------------v/r,Chuck------------------------------

chayceduncan · Answer

Interesting. So in the case that the PN's are wrong in the BOM they sent back - is the the numeric codes that were the sole issue. Google Sheets would probably work then - or I might suggest Box as well. You could just load the CSV file to a shared folder / directory for them to access. If they choose to download it and get it out of either system though the problem is likely going to be the same.

This is a more complex option - but you could consider making a codepage that pulls your BOM and makes an actual XLSX file as opposed to a CSV. This would ensure that you could control and enforce the formatting of the columns in question versus leaving it to the whims of Excel doing Excel things.&nbsp;------------------------------Chayce Duncan------------------------------

Forum Discussion

Pipeline Excel Export Treating Numbers Differently