Solved

SQL Error for long memo / text field

Posted on 2008-10-01
6
423 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
Comment Utility
try 'text' in case of nvarchar
0
 

Author Comment

by:Moujan19
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Moujan19
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Superb! Thank you for the GREAT Help! - This was very helpful.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now