Updating tables from CSV file or excel

UPDATE Product_master
SET  S_current_sp  = A.Selling_Price
FROM Product_master p
INNER JOIN (
SELECT Prod_Code, Selling_Price
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')..Sheet$1  
) A
ON A.Product_Code = p. Prod_Id

but its two tables i have to update

and can you explain your query a bit please

and its returning error INVALID OBJECT NAME SHEET 1

Regards,

I am  updating the master table by joining the result of the Excel file and the master table.

run this Query


SELECT Prod_Code, Selling_Price
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')..Sheet$1  


remember to replace the path of the xls and the sheet number at the last

Same error that INVALID OBJECT SHEET$1


Regards

Error: Invalid object name '.Sheet$1'. (State:S0002, Native Code: D0)

aneeshattingal,
> ..Sheet$1  

You need to replace the Sheet$1 with the actual sheet name...

Sheet name is sheet1 and i replace it

SELECT style, Sale
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\markdown\womens.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')..sheet1


This is what i am using
and this is the error i am geting now

Error: ConnectionWrite (send()). (State:01000, Native Code: 2746)
Error: General network error. Check your network documentation. (State:08S01, Native Code: B)

ammartahir1978,

SELECT Prod_Code, Selling_Price
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...Sheet1  




Aneesh

The sheet name must be suffixed by a '$'

SELECT Prod_Code, Selling_Price
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

make sure your account.xls is on the actual sql server and not on your pc ...if you have client sql install on your pc and do c: i think you need to put the file on main SQL

Another way is in sql em do tools data transformation serviers, import.......select excel....and select table..

okay i have Query Analyser installed on my laptop and i am running the query from my laptop so should i copy everything on to the server or my C:\

can anyone please explain step by step

Regards,


Ammar Tahir

is anyone going to help here or should i close the connection

go to SQL Enterprise...
   Go to your database
   Then go to tools dts import .....excel to table.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.  
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider ' microsoft.jet.oledb.4.0' IDBInitialize::Initialize returned 0x80004005:   ].


i tried to run the query as follow

SELECT Prod_Code, Selling_Price
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

it returns me errror

I went to ENtriprise manager and selected the database to which i want to import the data

but if you can please explain more step by step will me appreciated

Regards

Did you try my last post ?


SELECT Prod_Code, Selling_Price
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

remember there will be 3 dots and  a '$' will be followed after the Sheet name

yes i did n i hav posted the error


Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.  
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider ' microsoft.jet.oledb.4.0' IDBInitialize::Initialize returned 0x80004005:   ].

Can you post the exact Query you ran...

SELECT Prod_Code, sale
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\markdown\womens.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

Can you get into SQL EM ...if so go to the database then do this..
Tools , data transformation services, import database, Data Source set this to Microsoft Excel your version 5.0
here you select your file name c:\markdown\womens.xls

This way you are picking the right file.....up..

i did that but what are the next steps when i pick the file up

can you explain step by step please

Ok.....good..
You got the file...Data Source excel 5.0 and file name ..so do next

choose destination........(your servername, sa login,,,select your database, next  copy table view..
Next........here your see your s/s source select it...
where it says destination..(click this to select your tablename)...then in the transform
you map columna to table.columna.

.......Very important make sure your spreadsheet has no blank rows....

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319951

Right click on tables choose "All tasks" in that "Import Data"
DTS Wizard will appear click on "Next"
Choose data source as "Microsoft Excel 97-2000" and the File Path click "Next"
Choose destination as "Microsoft OLE DB Provider for SQL Server" Choose the server, authentication and database click "Next"
Choose the option "User a query to specify the data transfer" click "Next"
You can write your own query or you can use "Query Builder" Query builder is better as you can see the excel columns and it writes the query automatically
Click on "Next" once the query is completed
Choose the source as "Query" and destination as your desired table click on transform to check valid transformations click "Next"
You can check the "Save DTS Package" option so the same can be used in future
click on "Next" and "Finish" the data get transfers and result will appear
Thanks,
Raj

Hi Rajkumarrg
the solution you have told me is fine but i have two tables to update

Product master and product details

and the joining key between these two tables is

pm.prod_id = pd.product_id

i would suggest you bring the excel s/s into one table call it tempproducts then
when the s/s works and you get the data in then...do a store procedure that reads from the tempproducts to update the other ones.....do not try to get the s/s into both tables......thats harder to digest.
If your s/s works to one table ....then write the next part.

can you help me writing the store procedure please

SQL Server doesnt provides updating both tables directly, updating a temporary table and then add a SQL task in DTS to update the actual tables should be fine.

i think first you get the Excel into one table.

Create your table that has the same column names as your excel spreadsheet.
Put rowid ...indentity check so each record is unique.

Then we work on sp..

Hi Traceymary
this is how i did it please correct my query


hi i have import an excel sheet through DTS and name it as MARKDOWN (TABLE NAME)

i have an other table in DATABASE which i want to update. name is Markdown_details

the field i want to update in MARKDOWN_DETAILS
is NEW_SP
and the field name in imported table is SALE

how can i do it the query i want is

UPDATE MARKDOWN_DETAILS
SET markdown_details.NEW_SP = markdown.sale
where markdown_details.PRODUCT_NO_STRING = markdown.PROD_CODE
and markdown_id = 734

but this query doesnt work can you please correct me.
Regards,

try this...copy your data first ...just incase.

UPDATE MARKDOWN_DETAILS
SET MARKDOWN_DETAILS.NEW_SP = MARKDOWN.NEW_SP
FROM MARKDOWN_DETAILS
INNER JOIN MARKDOWN_DETAILS ON
    MARKDOWN.ID =    MARKDOWNDETAIL.ID       (Is the key of the table uniqueness)
WHERE MARKDOWN_DETAILS.ID = '734'

hold for a second

UPDATE MARKDOWN_DETAILS
SET MARKDOWN_DETAILS.NEW_SP = MARKDOWN.NEW_SP
FROM MARKDOWN_DETAILS
INNER JOIN MARKDOWN ON
    MARKDOWN.ID =    MARKDOWNDETAIL.ID       (Is the key of the table uniqueness)
WHERE MARKDOWN_DETAILS.ID = '734'

So did you get all the excel into the dts job and then run the command to do the update and it all worked..
Awesome.