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.
| Author |
Topic |
|
hollyquinn
Starting Member
31 Posts |
 Posted - 2009-01-30 : 07:37:11
|
| Hi. I am trying to import about 3000 rows of data from an Excel spreadsheet into an existing SQL Server table. I'm using the following query to do this:Insert into tblRfes Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Documents and Settings\hadams\My Documents\Excel\RFESTEST1.xls;HDR=YES', 'SELECT * FROM [Sheet3$]')I get the following error message:Msg 8152, Level 16, State 13, Line 1String or binary data would be truncated.The statement has been terminated.I know that this means that some of the data is too long to fit into one of the columns. The problem is that I'm unsure where the problem is in the spreadsheet. Is there anyway to locate the problem? I think I know which column is causing the problem. The data type of the column in SQL is ntext. How many characters can ntext hold? I didn't write this database and I'm not familiar with this data type. Does anyone have any advice for me? Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-30 : 09:46:31
|
| You can open Excel and find out which column is causing issue. Increase the length. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 10:12:44
|
| look into metadata of package to compare size of varchar fields in pipeline and in destination table and make sure they match. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 10:13:51
|
| b/w what are you using ntext in sql 2005?its deprecated. you should be using nvarchar(max) instead. |
|
|
|
hollyquinn
Starting Member
31 Posts |
Posted - 2009-01-30 : 10:26:02
|
quote:
Originally posted by sodeep
You can open Excel and find out which column is causing issue. Increase the length.
I'm not sure I completely understand? How can you find out which column is causing the issue by opening Excel? I'm guessing you mean to increase the length of the column in SQL Server? |
|
|
|
hollyquinn
Starting Member
31 Posts |
Posted - 2009-01-30 : 10:26:55
|
quote:
Originally posted by visakh16
b/w what are you using ntext in sql 2005?its deprecated. you should be using nvarchar(max) instead.
I didn't build the database. I wasn't familiar with ntext. I did change it to nvarchar(MAX) and still receive the error. |
|
|
|
droberts
Starting Member
3 Posts |
Posted - 2009-01-30 : 10:39:03
|
| Yes. I have run into this issue recently.Check this out for a funny, cynical document outlining the foibles of querying Excel: [url]http://blog.lab49.com/archives/196[/url]My mind boggled but we got our Excel queries working. |
|
|
|
|
|
|
Problem with importing data from Excel to SQL