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 Data From Excel as varchar Expand / Collapse
Author
Message
Posted Monday, September 29, 2008 6:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:34 PM
Points: 81, Visits: 218
Hi,

Am attempting to import a spreadsheet of data as a new table.

I specify the field as varchar(10).

I have made sure that the spreadsheet has the column stored as text.

However the Import Data wizard within SQL workbench or whatever the 2005 replacement is called for Enterprise manager, skips data that doesn't match a numeric value

Eg
My data looks like (coded values)
1A
2
3
0
1A

SSIS inserts nulls instead of the '1A' value.

Why Why Why?

Enterprise Manager and DTS was so easy!!!!!!!!!!!!!!!!!!!!!!!



Post #578043
Posted Tuesday, September 30, 2008 2:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
Welcome to one of the most irritating nuances of SSIS. Text is seen as NVarchar and not Varchar. In other words, if you have a text (10), make it Nvarchar (20).
You can use a dataconversion transformation to get past this.

~PD
Post #578129
Posted Tuesday, September 30, 2008 2:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
My bad... I meant in Excel, text is seen as nvarchar and not varchar
Post #578130
Posted Tuesday, September 30, 2008 4:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:34 PM
Points: 81, Visits: 218
Ill try nvarchar next time..

I imported it into MS Access first and then into SQl Server.
Post #578740
Posted Wednesday, October 1, 2008 12:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
SSIS and MS Access also has its own unique nuances, some of which are downright irritating.

Good luck!

~PD
Post #578878
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse