transferring data - 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

General SQL Server Forums

New to SQL Server Programming

transferring data

Author

Topic

sgraves
Starting Member

29 Posts

Posted - 2005-08-14 : 18:31:51

Hi! I am importing data from an Excel file into SQL. I am getting the following error message: Error during Transformation 'DirectCopyXform' from Row number 32. Errors encountered so far in this task: 1
Destination does not allow NULL on column pair 1(source column 'F1'(DBTYPE_R8), destination column 'agentno' (DBTYPE_14))

Anyone know why this is and how to correct it?

Thanks for any help!

Scott

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-14 : 19:10:56

Sounds like you have an empty cell and are trying to import into a column that doesn't accept nulls.
Change the tabe to allow nulls or fix the source data. Could be that you have some empty rows at the end of the spreadsheet.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sgraves
Starting Member

29 Posts

Posted - 2005-08-14 : 19:23:02

Hi! Yes, I have a few columns in the Excel file that contain no data. I only have 32 rows of data to import into SQL. I trying to import the data into an existing table so I left those columns empty to allow for the existing column in the SQL table. Can I not do this?

Thanks for your help.

Scott

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-14 : 20:49:48

If the table columns don't allow null then you have to put a value in them.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sgraves
Starting Member

29 Posts

Posted - 2005-08-15 : 09:29:39

Hi! So....if I don't want a value in those fields in my tables, I still have to enter a value. I need the fields to be empty for later data entry. Is there any way around entering a value for those cells?

Thanks!

Scott

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-15 : 10:40:57

Try to use

select * from excelTable$ where someColumn is null

instead of excel table as a source.

sgraves
Starting Member

29 Posts

Posted - 2005-08-15 : 11:15:20

So, I am to use * in the columns where there is a NULL value in the Excel file and then import it to my SQL database, correct?

Thanks for your help

Scott

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 01:10:46

Try to exclude the columns in the Excel file that dont any any data

Refer this on how to do this from query
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-16 : 03:49:58

Open Source tab of Transform Data Task. Check "SQL Query" and type the code mentioned. Replace excelTable$ with your excel table name and someColumn with name of any column in your excel table. I assume you use DTS to transfer data.

sgraves
Starting Member

29 Posts

Posted - 2005-08-16 : 10:54:28

It works! Thanks a million!

Scott

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 11:03:37

>>It works! Thanks a million!

Which method are you referring?

Madhivanan

Failing to plan is Planning to fail

niraligdesai
Starting Member

4 Posts

Posted - 2006-05-01 : 11:37:30

Hello! Madhivanan,

I need your help.

I want to transfer data from Excel to SQL Server. I have used your posted query as follow:
SQLStmt = "Insert into Imagetable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database= C:\Book1.xls;HDR=YES','SELECT * FROM [Sheet2$]')"

but I am getting following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

I am new with this and not getting it what's the problem. Can you please help me out.

I am using Excel 2002.

Thanks,
Nirali

quote:
Originally posted by madhivanan

Try to exclude the columns in the Excel file that dont any any data

Refer this on how to do this from query
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Nirali Desai

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-02 : 00:59:57

>>I am using Excel 2002.

The code I used is for EXCEL 2000. I dont know if it works for EXCEL 2002
Do a Google search on the error you get

Madhivanan

Failing to plan is Planning to fail

niraligdesai
Starting Member

4 Posts

Posted - 2006-05-02 : 09:04:47

I did try, but not getting it. I am not getting what's the real error is. I did try google search, but it has many different reasons for it. Don't know what to do. I am very new with this programming don't understand what to do. Can you help please!

quote:
Originally posted by madhivanan

>>I am using Excel 2002.

The code I used is for EXCEL 2000. I dont know if it works for EXCEL 2002
Do a Google search on the error you get

Madhivanan

Failing to plan is Planning to fail

Nirali Desai

akanodia98
Starting Member

6 Posts

Posted - 2006-05-11 : 16:10:54

Hello,

I am new to SQL server world.
I have a task to transfer data from excel sheet(s) to sql server.
I read I could do that using DTS.

But the problem is I have more than 1 excel files and in my table I need to get records from both the files with out duplication the data.
ex: one excel sheet conatins field 1,2,3
second sheet contains field 1,4,5,6 (now I should put data in the sql table as 1,2,3,4,5,6)

I will have to continue to do add data to this table as I get new excel sheets (the fields will remain same).

Can somebody help me with this?
If you can also give me some references for this it will be very helpful.

Thanks a lot in advance
Nicky

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-05-11 : 16:18:36

import data into auxiliary table, on success run execute sql task to insert ... where not exists(...)

akanodia98
Starting Member

6 Posts

Posted - 2006-05-11 : 23:30:11

mmarovic, thanks for the help,
but can you give a little more detail, how I can do those steps?

Thanks
Nicky

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-05-12 : 03:06:21

1. Start with execute sql tasks that drops auxiliary table if exists and then creates it again.
2. On success of previous step use transform data task to copy data from first excel file into auxiliary table.
3. On success of step 2 run execute sql task with code mentioned.
4. on success of step 3 run execute sql task to truncate auxiliary table
5. on success of step 4 copy data from another excel file.
6. on success of step 5 run execute sql task with the same code as in step 3.

Timbo2000
Starting Member

1 Post

Posted - 2009-07-13 : 13:59:24

quote:
Originally posted by mmarovic

Open Source tab of Transform Data Task. Check "SQL Query" and type the code mentioned. Replace excelTable$ with your excel table name and someColumn with name of any column in your excel table. I assume you use DTS to transfer data.

I was having this exact problem and thanks to your post I've got it working. Thanks a bunch!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources