Import Excell worksheet - SQL Server Forums

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Import Excell worksheet

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-05 : 14:53:16

hi everyone,
I am trying to import some phone numbers from an excell worksheet into a table called sms_phonebook in my db.

I am using DTS import wizard, however when i get to the source/destination dialog box, i choose transform and then, set the 3main fields i need, which are

1. phoneID (This is a primary Key (INT) - auto increment value )
2. username
3. phone_number
4. nickname

Since 1, is the primary key, i choose
- Ignore in the transform table option
- I leave the enable identity insert option

In the query builder,
It appears as such

'**********************************************************************
'  Visual Basic Transformation Script
'  Copy each source column to the
'  destination column
'************************************************************************

Function Main()
	DTSDestination("username") = DTSSource("jakesalabi")
	DTSDestination("Phone_number") = DTSSource("F2")
	DTSDestination("Nickname") = DTSSource("A")
	Main = DTSTransformStat_OK
End Function

however, when i click on run immediately, i always get this error

Error at Destination for Row number 1. Errors encountered so far in this task: 1. Insert error, column 1('phoneID',DBTYPE_14), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.
Unspecified error

Please what is wrong, am assuming that the phoneID, would auto generate itself ???

thanks
Afrika

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-05 : 15:20:43

I finally got it into a fresh table, and tried running this insert statement from QA, but it still gives this error

insert into sms_phonebook (username,phone_number, nickname)

select jakesalabi, f2, a  from [dbo].[sheet1$]

quote:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Phone_number', table 'afrika_sql.dbo.sms_phonebook'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Any Advice ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-05 : 15:37:37

Isn't the error message clear? You must have NULL values in dbo.Sheet1$ in the column f2.

what does:

select count(*) from [dbo].[sheet1$] where f2 is null

return?

- Jeff

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-05 : 15:49:19

Oh,
very silly of me.

You are right. I just imported it.

We normally have clients with bulk phone number entries and just assist them to import it to thier phone books. This is the first error of its kind.

However, i was thinking (The error) was the phoneID column as stated from the first thread.

I Should cut back on the late nights

Gracia
Afrika

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-05 : 16:10:43

Thanks Jeff,
I just got it sorted out

here is my code

insert into sms_phonebook (username,phone_number, nickname)

select jakesalabi, phone, a  from [dbo].[no$] 
where  left(phone,3) = '234' and len(phone) = 13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources