Updating tables from CSV file or excel

Hi

I have an excel sheet which contains product codes and NEW Selling price

i want to update the tables

its only two tables

how can i do it?

Field name "S_current_sp"  Table name " Product_master " as pm
Field name "Current_sp"     Table name " Product_detail"  as pd

Joining details are

pm.prod_id = pd.product_id

PROD_CODE is the field which i will have in excel and its Selling Price

but i want to update both table fields as i mentioned above, i am doing it by one by one can i automated it please as i give the path of the file from where it can be taken and update the tables.

ammartahir1978Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TRACEYMARYConnect With a Mentor Commented:
Here an example of an update i have
hard to tell if above is correct without the complete structure.

update web_training
      set email = webusername.email
from web_training
inner join webusername on
      webusername.username = web_training.username
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
ammartahir1978Author Commented:
but its two tables i have to update

and can you explain your query a bit please
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ammartahir1978Author Commented:
and its returning error INVALID OBJECT NAME SHEET 1

Regards,
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I am  updating the master table by joining the result of the Excel file and the master table.

0
 
Aneesh RetnakaranDatabase AdministratorCommented:


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
0
 
ammartahir1978Author Commented:
Same error that INVALID OBJECT SHEET$1


Regards
0
 
ammartahir1978Author Commented:
Error: Invalid object name '.Sheet$1'. (State:S0002, Native Code: D0)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
aneeshattingal,
> ..Sheet$1  

You need to replace the Sheet$1 with the actual sheet name...
0
 
ammartahir1978Author Commented:
Sheet name is sheet1 and i replace it
0
 
ammartahir1978Author Commented:
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)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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$]
0
 
TRACEYMARYCommented:
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..
0
 
ammartahir1978Author Commented:
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
0
 
ammartahir1978Author Commented:
is anyone going to help here or should i close the connection
0
 
TRACEYMARYCommented:
go to SQL Enterprise...
   Go to your database
   Then go to tools dts import .....excel to table.
0
 
ammartahir1978Author Commented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
ammartahir1978Author Commented:
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:   ].
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Can you post the exact Query you ran...
0
 
ammartahir1978Author Commented:
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$]
0
 
TRACEYMARYCommented:
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..
0
 
ammartahir1978Author Commented:
i did that but what are the next steps when i pick the file up

can you explain step by step please
0
 
TRACEYMARYCommented:
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....
0
 
rajkumarrgCommented:
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
0
 
ammartahir1978Author Commented:
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

0
 
TRACEYMARYCommented:
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.
0
 
ammartahir1978Author Commented:
can you help me writing the store procedure please
0
 
rajkumarrgCommented:
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.
0
 
TRACEYMARYCommented:
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..
0
 
ammartahir1978Author Commented:
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,
0
 
TRACEYMARYCommented:
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'
0
 
TRACEYMARYCommented:
hold for a second
0
 
TRACEYMARYCommented:
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'
0
 
TRACEYMARYCommented:
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.
0
All Courses

From novice to tech pro — start learning today.