niccross
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!!!
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!!!
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
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
ASKER
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
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.
ASKER
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\Add resses.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 ')'.
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.
'Excel 8.0;Database=C:\Source\Add
'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*
ASKER
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.x ls;IMEX=1' ,
'SELECT * FROM [Sheet1$]')
)
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
/*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.
'Excel 8.0;Database=C:\nbsprice.x
'SELECT * FROM [Sheet1$]')
)
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
ASKER
I still get this error:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
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)
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)
ASKER
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:
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
ASKER
i sent you a private message also.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
a great person and VERY experienced programmer!!
Thanks Niccross :). Glad to have helped.
To view the data from Excel
Open in new window
And the update
Open in new window