SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import Excel Spreadsheet Data into SQL 2005


Import Excel Spreadsheet Data into SQL 2005

Author
Message
dlangschied
dlangschied
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 33
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.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14887 Visits: 14396
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
dlangschied
dlangschied
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 33
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.
Attachments
SQL Column Mapping.JPG (9 views, 57.00 KB)
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14887 Visits: 14396
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:

http://stackoverflow.com/questions/7433348/how-does-one-change-the-default-varchar-255-of-a-column-when-importing-data-from

In Books Online, under Truncated Text

Books Online > Excel Source:

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
dlangschied
dlangschied
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 33
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)
dlangschied
dlangschied
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 33
actually, I meant to say it does not show 255 char, varchar is still the type, but when highlighted, it shows LongText
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14887 Visits: 14396
Welcome to working with Excel using SSIS :-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14887 Visits: 14396
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
VegasL
VegasL
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 159
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search