Importing inventory from Excel

Godfrey99 · ‎January 23, 2024

We are a small auto repair shop. We used to use Quick Book Desktop to manage our business and are now on the 2021 Premier version. Oct 1, 2022 we converted to Protractor (which includes accounting) and stopped using QBD. Protractor has not met our needs and we are switching back to QBD for accounting and different shop management software that does not include accounting.

QBD has the parts inventory for 2022-09-30 which was out of date then as we had not done a physical recount in years. I have a spreadsheet with an accurate inventory as of 2023-12-31 but I cannot load it into QBD

The part numbers include colons which QBD inserted when I extracted the file. The colons are the field separators for the QB hierarchy. When I try to load a sample from my spreadsheet I get an error that part numbers cannot include colons. How do I get around this? I want to keep the hierarchy.

Also I can only load part number, description and price. I cannot load average cost and count. How do I get around this?

Thanks

Maybelle_S · ‎January 23, 2024

Hello there, Godfrey99. Let me explain how importing CSV files works in QuickBooks.

QuickBooks Desktop (QBDT) has limitations when importing spreadsheets or CSV files, especially with characters, which may lead to import errors. You can review the data and remove any special characters before importing it into the program.

Additionally, you may want to format the data in a way that QuickBooks can recognize, such as using standard naming conventions and avoiding special characters. It should help prevent errors during the import process.

For more information, check these links:

Furthermore, you may want to explore a third-party application that can help import average cost and count into the system. You can find one by visiting this website: https://desktop.apps.com/home.

Visit this page to copy multiple transactions from one QuickBooks company file to another.

Should you need further assistance importing Excel files to QBDT, feel free to post here in the Community. We've got you covered. Have a good day!

4Gal · ‎January 23, 2024

For your existing inventory item, you can prepare Inventory Adjustment data with the following template

RefNumber	Account	Transaction Date	Memo	Item	New Qty	New Value	Adj Qty	Adj Value	Customer	Class
1231-1	Cost of Goods Sold	31/12/2020	Year End Inventory Adjustment	Hardware	10
1231-1	Cost of Goods Sold	31/12/2020	Year End Inventory Adjustment	Door Frame	10
1231-1	Cost of Goods Sold	31/12/2020	Year End Inventory Adjustment	Cabinets			-1
1231-2	Cost of Goods Sold	31/12/2020	Year End Inventory Adjustment	Wood Door		1000
1231-2	Cost of Goods Sold	31/12/2020	Year End Inventory Adjustment	Interior Door Kit				500

For your new inventory item,

You can prepare the following data

Item List

Item Name

Item Type

Is Active

Sales Description

Purchase Description

Tax Code

Account/Income Account

Expense/COGS Account

Asset Account

Cost

Preferred Vendor

Price or %

Reorder Point

Manuf Part Number

Unit of Measure

Max

Green Widget

Inventory Part

Y

Widget Sales

Widget Purchase

Tax

General Sales

General Cost of Goods Sold

Inventory Asset

10

ABC Vendor

15

10

12345

30

General Services

Service

Y

Services Sales

Services Purchase

Non

Specialized Sales

Specialized Expenses

8

DEF Vendor

9

General Parts

Non-inventory Part

Y

Part Sales

Part Purchases

Tax

General Sales

General Cost of Goods Sold

7

GHI Vendor

8

Misc Charge

Other Charge

Y

Misc Income

Misc Charges

Non

General Sales

General Cost of Goods Sold

General Expenses

12

GHI Vendor

15

Purchase Order

Vendor

Transaction Date

PO Number

Class

ShipTo Entity

BillTo Line1

BillTo Line2

BillTo Line3

BillTo Line4

BillTo City

BillTo State

BillTo PostalCode

BillTo Country

ShipTo Line1

ShipTo Line2

ShipTo Line3

ShipTo Line4

ShipTo City

ShipTo State

ShipTo PostalCode

ShipTo Country

Terms

Due Date

Expected

Ship Method

FOB

Vendor Message

Memo

Vendor Acct No

Item

Quantity

Description

Rate

Customer

Service Date

To Be Printed

Template Name

Vendor Email

To Be E-Mailed

Items Class

Other1_Header

Other2_Header

Other1

Other2

Unit of Measure

Manuf Part Number

Currency

Exchange Rate

John's Lumber

01/09/2020

8

John's Lumber

1 Main Street

Newton

NY

12345

USA

John's Lumber

1 Main Street

Newton

NY

12345

USA

Cabinets

10

Cabinets

200

John's Lumber

01/09/2020

8

John's Lumber

1 Main Street

Newton

NY

12345

USA

John's Lumber

1 Main Street

Newton

NY

12345

USA

Hardware

20

Hardware

10

Jane's Landscape Design

02/09/2020

9

Jane's Landscape Design

200 Smith Street

Anytown

NY

11111

USA

Jane's Landscape Designe

200 Smith Street

Anytown

NY

11111

USA

Door Frame

1

Door Frame

1000

Baker, Chris

Y

ABC Roofing Company

03/09/2020

10

ABC Roofing Company

1 North Street

Newton

NY

12345

USA

ABC Roofing Company

1 North Street

Newton

NY

12345

USA

Black Tiles

100

Black Premium Tiles

25

ABC Roofing Company

03/09/2020

10

ABC Roofing Company

1 North Street

Newton

NY

12345

USA

ABC Roofing Company

1 North Street

Newton

NY

12345

USA

Red Tiles

100

Red Premium Tiles

25

Item Receipt with Bill

Vendor

Transaction Date

RefNumber

Memo

Vendor Acct No

Receive Against PO #

Items Item

Items Qty

Items Description

Items Cost

Items Class

Items Customer

Items Billable

AP Account

New Builder

01/09/2011

100

Hardware

1

Hardware

20

N

New Builder

01/09/2011

100

Cabinets

1

Cabinets

200

N

John's Landscaping Supply

01/09/2011

105

Loom

50

Loom

100

John Doe

Y

Ajax Lumber

02/09/2011

106

Planks

100

Planks

50

N

then you can use an importer tool to proceed. It's a one time license.

https://get.transactionpro.com/qbd

Godfrey99 · ‎January 23, 2024

Thanks. I will explore this tomrrow

Godfrey99 · ‎January 23, 2024

Thanks. I will explore this tomorrow

Topics

Training

Community

Resources

Importing inventory from Excel