Link to home
Start Free TrialLog in
Avatar of niccross
niccrossFlag for United States of America

asked on

SQL Data + Price Update in Great Plains Dynamics

Hi! I have a problem. I have this excel spreadsheet with over 2K rows.
I need to update a price list in SQL / Great Plains Dynamics. The table is IV00108

Current the table has item number, item description, and four (4) price levels:
1-9, 10-49, 50-99, 100+

The excel spreadsheet i have - has the item number, item description, and four (4) NEW price levels:
1-10, 11-50, 51-100, 101+

Is there any easy way to do this? Should I do it thru SQL Management Studio 2012 and how? Should I do it thru integration manager and how?

I am a noob and trying to learn. If ANYONE can help I would be appreciative!!!
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

You can use the OPENROWSET to read your Excel as if it were just any other SQL View/Table.

To view the data from Excel
/*Typically uses your first row as column names. A good idea to do a SELECT * so yo u can see the column names*/
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
		'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
		'SELECT * FROM [Sheet1$]')

Open in new window


And the update
WITH ExcelData(
        /*Typically uses your first row as column names. A good idea to do a SELECT * so yo u can see the column names*/
        SELECT ItemNumber, ItemDescription, PriceLevel1, PriceLevel2, PriveLevel3, PriceLevel4
        FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                        'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                        'SELECT * FROM [Sheet1$]')
)
UPDATE IV00108 SET
	PriceLevel1 = ExcelData.PriceLevel1,
	PriceLevel2 = ExcelData.PriceLevel2,
	PriveLevel3 = ExcelData.PriveLevel3,
	PriceLevel4 = ExcelData.PriceLevel4
FROM IV00108 
	INNER JOIN ExcelData ON ExcelData.ItemNumber = IV00108.ItemNumber

Open in new window

Avatar of niccross

ASKER

Sorry my mistake that I did not give the detail that in the actual SQL table the field is: PRCLVEL and that has the description of 10-49 ... ETC

The excel sheet has the columns 1-10, 11-49 , etc
WITH ExcelData(
        /*Typically uses your first row as column names. A good idea to do a SELECT * so yo u can see the column names*/
        SELECT ItemNumber, ItemDescription, [1-9] as PriceLevel1, [10-49] as PriceLevel2, [50-99] as PriveLevel3, [100+] as PriceLevel4
        FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                        'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                        'SELECT * FROM [Sheet1$]')
)
UPDATE IV00108 SET 
	VALUE_COLUMN = CASE PRCLVEL
					WHEN '1-9' THEN ExcelData.PriceLevel1
					WHEN '10-49' THEN ExcelData.PriceLevel1
					WHEN '50-99' THEN ExcelData.PriceLevel1
					WHEN '100+' THEN ExcelData.PriceLevel1
					ELSE VALUE_COLUMN
				END
FROM IV00108 
	INNER JOIN ExcelData ON ExcelData.ItemNumber = IV00108.ItemNumber

Open in new window

MlandaT ... if I may be so bold as to ask you to look at this excel spreadsheet. The first sheet is the actual export from SQL ... the second excel spreadsheet so that you have the picture of it all. I think i blew an error when I tried the code.
Workbook1.xlsx
Please check this fiddle (http://sqlfiddle.com/#!3/4d8ef/1) and let me know if the UPDATETO column is reflecting the correct thing.
WOW didn't know SQLFiddle existed!
It does reflect on what it needs to be.

I get this when I run the EXCEL command from SQL:

WITH ExcelData(
        /*Typically uses your first row as column names. A good idea to do a SELECT * so yo u can see the column names*/
        SELECT *
        FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                        'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                        'SELECT * FROM [Sheet1$]')
)

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
Ooops! Sorry.. please make that first line: WITH ExcelData as (. i left out the *as*
WITH ExcelData as (
        /*Typically uses your first row as column names. A good idea to do a SELECT * so yo u can see the column names*/
        SELECT *
        FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                        'Excel 8.0;Database=C:\nbsprice.xls;IMEX=1',
                        'SELECT * FROM [Sheet1$]')
)

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
I still get this error:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
Into which IV00108 column do you wan to put the value from the Excel file?

Here is a fiddle with the update: http://sqlfiddle.com/#!3/38e1b/2
It shows the BEFORE and AFTER. You can BUILD SCHEMA (on the LEFT to reset the tables and start again)
The UOMPRICE column in SQL is the one that is getting replace with the new pricing that is dictated by the PRCLEVEL.
Incorrect syntax near ')'.
A WITH (or CTE) creates an in-memory (sort of) data table. You actually need a SELECT after the WITH, coz it's meaningless on its own. The syntax in SQL Server is:
WITH tablename AS (
     ---a query to return the data from somewhere
     SELECT ... FROM. ...
)
SELECT * FROM tablename
i sent you a private message also.
ASKER CERTIFIED SOLUTION
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
a great person and VERY experienced programmer!!
Thanks Niccross :). Glad to have helped.