Import some field of excel file to table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Import some field of excel file to table

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-11-02 : 12:28:58

Hello:

I think my previous post not clear enough. Here is my problem.

I have table: Name


id   name   time    term    test
12   Jean     1        1       0
12   Jean     2        2       0
12   Jean     3        3       0
12   Jean     4        4       0
12   Jean     5        5       0
12   Jean     6        6       0
12   Jean     7        7       0
12   Jean     8        8       0
12   Jean     9        8       0
12   Jean    10       11       0
12   Jean    11       11       0 

32   QADF     1        1       0
32   QADF     2        2       0
32   QADF     3        5       0
32   QADF     4        5       0
32   QADF     5        5       0
32   QADF     6        6       0
32   QADF     7        7       0
32   QADF     8        8       0

41   RTHF     1        1       0
41   RTHF     2        2       0
41   RTHF     3        5       0
41   RTHF     4        5       0
41   RTHF     5        5       0
41   RTHF     6        6       0
41   RTHF     7        7       0
41   RTHF     8        8       0
41   RTHF     9        8       0

need to update column: time and term where id = 12, 32, 41 ......212.I have several hundred ID
base on following excel data needed to be update time and test column.

time    term    test	id   name
1        1         0
2        2         0
3        3         0
4        10        0
5        11        0
7        7         0
8        8         0
9        8         0
10       11        0
11       11        0
12       11        0

There is any efficient way work out it?

Thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 13:39:07

something like


UPDATE t
SET t.time=x.time,
t.test=x.test
FROM table t
INNER JOIN (SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=excel file path here', Sheetname$);
)x
ON x.id = t.id
and x.term = t.term

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-11-02 : 15:14:47

Thanks for the reply.

So I have to make several hundred excel files for each id first.

For example; The template

time    term    test	id   name
1        1         0
2        2         0
3        3         0
4        10        0
5        11        0
7        7         0
8        8         0
9        8         0
10       11        0
11       11        0
12       11        0

for each id

id = 12

time    term    test	id   name
1        1         0	12   Jean
2        2         0	12   Jean
3        3         0	12   Jean
4        10        0	12   Jean
5        11        0	12   Jean
7        7         0	12   Jean
8        8         0	12   Jean
9        8         0	12   Jean
10       11        0	12   Jean
11       11        0	12   Jean
12       11        0	12   Jean

id = 32

time    term    test	id   name
1        1         0	32   QADF 
2        2         0	32   QADF 
3        3         0	32   QADF 
4        10        0	32   QADF 
5        11        0	32   QADF 
7        7         0	32   QADF 
8        8         0	32   QADF 
9        8         0	32   QADF 
10       11        0	32   QADF 
11       11        0	32   QADF 
12       11        0	32   QADF

ID = 41

time    term    test	id   name
1        1         0	41   RTHF 
2        2         0	41   RTHF 
3        3         0	41   RTHF  
4        10        0	41   RTHF  
5        11        0	41   RTHF  
7        7         0	41   RTHF  
8        8         0	41   RTHF 
9        8         0	41   RTHF  
10       11        0	41   RTHF  
11       11        0	41   RTHF  
12       11        0	41   RTHF

Is any way no need to make several hundred excel files but still could update table only based on one template file above?

Thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 04:25:22

why should you need separate excel files? isnt it matter of putting all of them in same excel with new column id to indicate ID value. Use it also in join condition to make sure you update row with correct id.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-11-03 : 12:23:37

Visakh:

Thank you very much for response.

So first, I need to repeatly put hundreds id on this template. It is time Consuming to make hundreds id repeat the same on excel. Is there any efficient way to dynamically
input id?

Template:

time term test name id
1 1 0 RTHF
2 2 0 RTHF
3 3 0 RTHF
4 10 0 RTHF
5 11 0 RTHF
7 7 0 RTHF
8 8 0 RTHF
9 8 0 RTHF
10 11 0 RTHF
11 11 0 RTHF
12 11 0 RTHF

id = 12, 32, 41 .......55,76,123,456

As always, your help is highly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 12:31:05

sorry whats the significance of this sequence?

12, 32, 41 .......55,76,123,456

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-11-03 : 14:44:04

Thanks for the response again.

It is just random id number, not sequence at all, but there is several hundred id

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-11-03 : 14:47:29

I have a separate excel sheet for those several hundred id.

viol-8-r
Starting Member

4 Posts

Posted - 2011-12-08 : 16:37:17

Hi Visakh16,
I see you have contributed a lot to this issue. I have a strange issue. I am following your script and it runs fine on my sandbox but produces an error on production server with error 7203 whereas all the necessary drivers are installed. Any idea why would it do that?

viol-8-r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 00:21:54

whats the error you're getting?
is OPENROWSET enabled on production box?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

viol-8-r
Starting Member

4 Posts

Posted - 2011-12-09 : 14:45:15

Yes , openrowset is enabled

here is my query
UPDATE I
SET I.itemdesc = T.itemdesc
FROM iv00101 I
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\desc.xls;',
'SELECT itemnmbr, itemdesc
FROM [sheet1$]') T
ON I.itemnmbr = T.itemnmbr where I.ITEMNMBR = '01-03-0136'

and the error message

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

viol-8-r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 08:00:13

is the excel sheet kept open by somebody?
also dont use any spaces,line feed characters etc in OPENROWSET statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources