Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Import Excel Spreadsheet Data into SQL 2005 Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 10:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:08 AM
Points: 15, 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.

Post #1424650
Posted Wednesday, February 27, 2013 11:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1424676
Posted Wednesday, February 27, 2013 12:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:08 AM
Points: 15, 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.



  Post Attachments 
SQL Column Mapping.JPG (5 views, 57.64 KB)
Post #1424681
Posted Wednesday, February 27, 2013 12:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1424709
Posted Thursday, February 28, 2013 5:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:08 AM
Points: 15, 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)
Post #1425011
Posted Thursday, February 28, 2013 6:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:08 AM
Points: 15, Visits: 33
actually, I meant to say it does not show 255 char, varchar is still the type, but when highlighted, it shows LongText
Post #1425012
Posted Thursday, February 28, 2013 9:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
Welcome to working with Excel using SSIS

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1425106
Posted Thursday, February 28, 2013 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1425110
Posted Friday, March 1, 2013 11:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 13, 2013 8:55 PM
Points: 18, Visits: 68
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
Post #1425821
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse