Solved

Updating tables from CSV file or excel

Posted on 2006-06-09
37
458 Views
Last Modified: 2012-06-21
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.

0
Comment
Question by:ammartahir1978
  • 15
  • 12
  • 8
  • +1
37 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16868257
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
 

Author Comment

by:ammartahir1978
ID: 16868307
but its two tables i have to update

and can you explain your query a bit please
0
 

Author Comment

by:ammartahir1978
ID: 16868313
and its returning error INVALID OBJECT NAME SHEET 1

Regards,
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16868380
I am  updating the master table by joining the result of the Excel file and the master table.

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16868397


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
 

Author Comment

by:ammartahir1978
ID: 16868819
Same error that INVALID OBJECT SHEET$1


Regards
0
 

Author Comment

by:ammartahir1978
ID: 16868820
Error: Invalid object name '.Sheet$1'. (State:S0002, Native Code: D0)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16868836
aneeshattingal,
> ..Sheet$1  

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

Author Comment

by:ammartahir1978
ID: 16868892
Sheet name is sheet1 and i replace it
0
 

Author Comment

by:ammartahir1978
ID: 16868901
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16869391
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16869404
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
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16872391
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
 

Author Comment

by:ammartahir1978
ID: 16883967
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
 

Author Comment

by:ammartahir1978
ID: 16885099
is anyone going to help here or should i close the connection
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16886011
go to SQL Enterprise...
   Go to your database
   Then go to tools dts import .....excel to table.
0
 

Author Comment

by:ammartahir1978
ID: 16886115
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16886457
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ammartahir1978
ID: 16886821
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16887521
Can you post the exact Query you ran...
0
 

Author Comment

by:ammartahir1978
ID: 16887897
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
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16888265
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
 

Author Comment

by:ammartahir1978
ID: 16888423
i did that but what are the next steps when i pick the file up

can you explain step by step please
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16888928
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
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16888951
0
 
LVL 2

Expert Comment

by:rajkumarrg
ID: 16891788
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
 

Author Comment

by:ammartahir1978
ID: 16892768
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
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16892819
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
 

Author Comment

by:ammartahir1978
ID: 16893107
can you help me writing the store procedure please
0
 
LVL 2

Expert Comment

by:rajkumarrg
ID: 16893145
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
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16893679
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
 

Author Comment

by:ammartahir1978
ID: 16935199
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
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16935304
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
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16935307
hold for a second
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16935314
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
 
LVL 7

Accepted Solution

by:
TRACEYMARY earned 500 total points
ID: 16935348
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
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16936339
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now