Exporting an item list to CSV format turns my MPNs into a formula

SteveJMB · ‎March 22, 2023

I need the MPNs to be able to be imported correctly. For example, 666692123115 gets changed to

6.66692E+11 after exporting an item list to CSV. I have 3000+ items. Is there a way to just import the exact value of the MPN field? Thank you!

Mich_S · ‎March 22, 2023

Thanks for keeping us updated, @SteveJMB.

I can walk you through fixing the MPN format.

In most cases, you can only enter up to 11 digits per cell, and if you enter more, Excel automatically changes the number format. That's why you're getting an MPN figure of 6.66692E+11 instead of 666692123115.

Make sure to select the Number format to display the data you want. I'll show you how:

1. Open the Excel file.

2. Highlight the MPN column.

3. Click on the number format drop-down.

4. Select Number MPN.

5. Once done, click on the Decrease Decimal icon until you see the whole number on the column.

If you have additional concerns regarding Excel, I recommend getting in touch with your IT expert or reaching out to the Excel support. This allows them to take a look at your issue further.

I'll also include this extra module to learn about other export ways: Things to remember when you import and export data in QuickBooks Desktop.

Additionally, you can read these articles about keeping track and personalizing reports in QBDT:

Please let us know if this goes. We'll make sure to respond as soon as we can. We're happy to help further regarding data management or QuickBooks. Take the best care!

View solution in original post

GebelAlainaM · ‎March 22, 2023

It's a pleasure to help you correct the MPNs format, steve.

In your case, you'll have to expand the column of the MPNs in the CSV file so that the formula will turn into the exact value of the MPN field. Either way, you can turn off the formula feature of that cell.

Once everything is corrected, and you're ready to import your data to QuickBooks Desktop (QBDT), refer to this article for the detailed steps: Import/export CSV files. This article includes the formatting styles when importing your CSV file successfully to QBDT.

I've also added this article to learn about other data types you can import and export to QBDT: Import and export data in QuickBooks Desktop.

I'll be around if you have clarification about the process or additional concerns with managing items to QBDT. Keep safe always!

SteveJMB · ‎March 22, 2023

I’m slightly familiar with excel automatically converting long numbers to formula when cell width is too short, but after widening the cell width with a click and drag, the formula does not change into the mpn. No matter how wide I make any cell, it does not change.

thanks for your help!

SteveJMB · ‎March 22, 2023

I understand - however, widening the cell does not change it back to its original form.

thanks for you assistance!

Mich_S · ‎March 22, 2023

Thanks for keeping us updated, @SteveJMB.

I can walk you through fixing the MPN format.

In most cases, you can only enter up to 11 digits per cell, and if you enter more, Excel automatically changes the number format. That's why you're getting an MPN figure of 6.66692E+11 instead of 666692123115.

Make sure to select the Number format to display the data you want. I'll show you how:

1. Open the Excel file.

2. Highlight the MPN column.

3. Click on the number format drop-down.

4. Select Number MPN.

5. Once done, click on the Decrease Decimal icon until you see the whole number on the column.

If you have additional concerns regarding Excel, I recommend getting in touch with your IT expert or reaching out to the Excel support. This allows them to take a look at your issue further.

I'll also include this extra module to learn about other export ways: Things to remember when you import and export data in QuickBooks Desktop.

Additionally, you can read these articles about keeping track and personalizing reports in QBDT:

Please let us know if this goes. We'll make sure to respond as soon as we can. We're happy to help further regarding data management or QuickBooks. Take the best care!

BigRedConsulting · ‎March 23, 2023

From the thread, the CSV file is not messed up, but when you open the file in Excel, the display is changed. It's not a formula, really, but scientific notation.

To change this, highlight the column, right click and pick Format Cells... and then format the cells as numbers with no decimal:

SteveJMB · ‎March 23, 2023

This fixed it for me. I tried several other formatting choices that didn't work. For some reason it reverts back to the original number form even after saving. Closing and reopening the document reverts the formatting back to the original number form. Very frustrating. Thanks!

Topics

Training

Community

Resources

Exporting an item list to CSV format turns my MPNs into a formula