February 27, 2013 at 10:46 am
I have some data that I need to import from an Excel spreadsheet. The trouble is that SQL 2005 is giving me problems with the import field type. On text filed types it is giving field mismatches. And on character fields longer than 255 characters, it is erroring saying that the field is truncating.
I have to believe that there is a way to do this. It is not like I am trying to load an Excel file into Oracle, which would work if I created as a csv. But this is Microsoft to Microsoft.
I just want the MS SQL to recognize the field length of my Excel spreadsheet column. Any help would be welcome.
February 27, 2013 at 11:55 am
What method are you trying? Please post the code and a sample Excel file so we can recreate the problem on our side.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 27, 2013 at 12:19 pm
I am using the DTS Wizard. I am pulling in the worksheets from Excel and the Wizard is assigning fields by column and then is some how setting the field type and size.
I am a newbie. This wizard does look like it is simple enough to use. I have used it a few times to copy tables to Excel for loading in other SQL databases. I have had an issue with text fields, but it has not hurt me to simply ignore those fields until now. On the varchar field, that is the first time i have experienced it.
I am providing a snap shot of the varchar field. You can see here that the varchar is 255, yet to the right it determines the field to be 800. The problem may very well be that the Excel spreadsheet is telling SQL that is its size. I just do not know enoght to know.
February 27, 2013 at 12:51 pm
dlangschied (2/27/2013)
I am using the DTS Wizard. I am pulling in the worksheets from Excel and the Wizard is assigning fields by column and then is some how setting the field type and size.I am a newbie. This wizard does look like it is simple enough to use. I have used it a few times to copy tables to Excel for loading in other SQL databases. I have had an issue with text fields, but it has not hurt me to simply ignore those fields until now. On the varchar field, that is the first time i have experienced it.
I am providing a snap shot of the varchar field. You can see here that the varchar is 255, yet to the right it determines the field to be 800. The problem may very well be that the Excel spreadsheet is telling SQL that is its size. I just do not know enoght to know.
That's the default behavior of the Excel driver used by the DTS Wizard Import/Export Wizard. You can work around the behavior by making a registry change, or by moving one of your longer text values into one the first 8 rows. Here is a thread that discusses the issue:
In Books Online, under Truncated Text
Truncated text. When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 28, 2013 at 5:58 am
It work in the sense that the varchar was not employed as field/column type, but now this error appears. It just goes to show you Jane...!
This field contains text only, so I am a bit baffled as to why SQL is pitching a fuss.
Error 0xc02020f6: Data Flow Task: Column "Selection_Clause" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)
February 28, 2013 at 6:00 am
actually, I meant to say it does not show 255 char, varchar is still the type, but when highlighted, it shows LongText
February 28, 2013 at 9:19 am
Welcome to working with Excel using SSIS 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 28, 2013 at 9:24 am
Seriously though, just change the destination table to have NVARCHAR instead of VARCHAR and you should be fine.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 1, 2013 at 11:10 pm
dlangschied, great question.
I ran into same problem when learning sql when importing from excel.
Do this.
Look at column sql is reporting the error when importing.
Then go into excel, create a temporary column and use the len function, for example =len(a1), this will give you the lenght of the characters in the column.
Then sort by greatest to least, and you will identify which columns are greater than 255 character length in excel.
Chances are you're having some bad data garbled up in one cell in excel, thus causing this issue in when importing.
what you can then do is, either remove this data from excel then ones over 255, or what i do is
use =LEFT(A1,255)
basically saying, use first 255 characters in excel for this cell, if you do this you won't run into any errors when importing in sql
hope this helps
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply