?
Solved

SQL Error for long memo / text field

Posted on 2008-10-01
6
Medium Priority
?
457 Views
Last Modified: 2012-06-22
I'm trying to import data from Excel to a SQL table (SSIS)

All fields go in ok, except a memo field, which has variable, long text (notes).

I've attached the SQL error, it says it cannot got in because it's trunkated. I'm not sure if the field type is correct (nvchar), I've tried to change that, it did not help.

Please help... thank you!
Report1.txt
0
Comment
Question by:Moujan19
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 22619994
try 'text' in case of nvarchar
0
 

Author Comment

by:Moujan19
ID: 22620088
Yes, very strange, on other fields it works, on this field, when I change it, it still reads (at the bottom) nvchar (255) although the drop down has been changed to text
0
 
LVL 1

Expert Comment

by:MaxLyd
ID: 22623102
I 've come across a similar situation in the past with the truncation error.

Is it necessary to use SSIS in this case?

I ask this because you can create a linked server in SQL 2005 that will read directly from the spreadsheet and allow you to in effect query from the spreadsheet as if it were a table. This way, prior to import, you can choose to cast the memo field as nvarchar(max) which will normally facilitate the length of text you are mentioning.  Acts like a staging area for the data so you can more actively control the import process and deal with the errors.  Not as automated as using SSIS, but could help in the short run.

Also, another question would be if you needed to keep all the data in the memo field. If that wasn't the case, you could always take the left 255 characters and discard the remainder. This would stop the pipeline from blowing up on you on import.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Moujan19
ID: 22627707
Not sure why SSIS, I was instructed to do that by someone here. I'll try it without.. I think I see where you are talking about, during import we can run the query on it and import only what we need. I think this is where you are talking about? and how would I cast it as navchar(max) on the way in?

Thankyou!
0
 
LVL 1

Accepted Solution

by:
MaxLyd earned 2000 total points
ID: 22629328
What I was originally describing was setting the Excel file as Linked server through your SQL Server Management Studio.
To do this you expand the 'Server Objects' folder, then right click on the 'Linked Server' Folder, then select 'New Linked Server'
You will be prompted for:
'Linked Server' - which is the name you choose for the linked server
'Provider' - which you will have to select from a drop down list
  • For Excel 2003 use - 'Microsoft Jet 4.0 OLE DB Provider'
  • For Excel 2007 use - 'Microsoft Office 12.0 Access Database Engine OLE DB Provider'
'Product Name'
  • For Excel 2003 use - 'Microsoft OLE DB Provider for Jet'
  • For Excel 2007 use - 'Microsoft .ace.oldedb.12.0'
'Data source' - this is the file path including file name for the excel file you are linking
'Provider String'
  • For Excel 2003 use - 'Excel 5.0'
  • For Excel 2007 use - 'Excel 12.0'
Once you do that you can query this data directly without using the import wizard.
 I included an example of some code that pulls from linked excel file with with the linked server name 'CUSTOMER_LINKED' and the sheet name 'Sheet1'.   The data is queried and only the fields that are needed are inserted into a table 'Customer' in the 'test' database.  I also include an example of the CAST you mentioned.
I hope this proves helpful.

Insert test.dbo.Customer
Select 
CustomerID, Customer_Name, Customer_Country,
Customer_State, CAST(Customer_notes as nvarchar(max)) as Customer_notes
from
   (
   SELECT * 
   FROM OPENQUERY(CUSTOMER_LINKED, 'SELECT * FROM [Sheet1$]')
   ) as A

Open in new window

0
 

Author Closing Comment

by:Moujan19
ID: 31502199
Superb! Thank you for the GREAT Help! - This was very helpful.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

621 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