Tips and Tricks #5 – Update Item Pricing Using Excel, Access, and SQL
I have a terrible time with pricing updates. I do not mean price levels and margins, I mean plain ‘ole updating standard cost and list price. Scribe is too much of a pain, Integration Manager is too slow, and I always seem to have to tweak the templates I have saved each time I do an update.
After talking with our GP consultant today, Todd McDaniel, he gave me an idea. Instead of messing with all the import softwares and Excel to CSV conversions, I just need to get the data straight into a SQL table and run an update statement. Since updating cost and list price fields do not effect the GP business logic (***Unless you are using AVERAGE COSTING***), it is just as simple as updating their respective fields with and UPDATE statement.
Please make sure your Excel spreadsheet to have the following columns for updating your GP item pricing (Excel column names need to be exact):
Item_Number, Description, Cost, List
First we want to import the spreadsheet into Access. I am using Access 2007. Once imported as a table into Access, we are going to move the table to our SQL Server. Once the data in the spreadsheet has moved to our SQL Server, it is all “gravy” from there. Please follow the steps below to move your Excel spreadsheet to Access and then SQL Server:
Import spreadsheet into Access.
Choose External Data –> Excel Import Wizard (follow the wizard to import your spreadsheet to Access)
After importing, rename table to PriceUpdates
Next select Database Tools –> Move Data –> SQL Server
Following the Move Data SQL Server Wizard to move the database to SQL
Use an existing database or create a new one (I chose to use an existing database)
Create a connection to your SQL server if you don’t already have one setup on your computer
After setting up your connection, choose the defaults on the Upsizing Wizard screen 1
Select No application changes and then Finish
Your data will be moved to SQL Server
Once your data has been moved to SQL Server as a table, you can run the script below to update the items in your GP inventory and price list tables (IV00101 and IV00105) with the pricing from your Excel spreadsheet that we uploaded to SQL Server.
The following script will update the current and standard costs, item description, and list price for all the items that are in both your inventory table (IV00101) and your PriceUpdates table.
Execute the following script:
This script has code that will update data in the Dynamics GP database. While this is not “illegal” it may or may not be supported by Microsoft. I have not had any issues with this in our environment, but make sure you test this in your non production environment first, before deploying it to your production environment.
The USE [TSP] tells the script to only execute on my database named TSP. You will need to modify the [TSP] to be whatever your database name is.
SET STNDCOST=Cost, CURRCOST=Cost, ITEMDESC=SUBSTRING(description,1,101)
SELECT item_number, DESCRIPTION, list, cost, b.itemnmbr, itemdesc, stndcost, CURRCOST, c.LISTPRCE FROM PriceUpdates a
JOIN iv00101 b ON a.item_number=b.ITEMNMBR
JOIN iv00105 c ON a.item_number=c.ITEMNMBR
ORDER BY item_number
/*DROP TABLE PriceUpdates ***Remove the – before the DROP TABLE to delete the PriceUpdates database.***/
Update (3/30/2010): I have updated the query to make sure that the length of the description column does not exceed the 101 characters allowed by GP using the SUBSTRING() function.