January 10, 2013 at 11:11 pm
Hi
In excel 2007 file, one of the columns contains the below values
Job Number
10132386
10289093
10132628
10191002
10162417
10137136
10189017
10160436
10133333
10192752
10182457
WSDD-105346353
WSDD-105685515
WSDD-105977159
WSDD-107316895
WSDD-107479127
As i am using ssis 2005, installed driver to support the 2007 file format and set the connection string as
"Data Source="+ @[User::File_Name] + ";Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0 XML;HDR=YES;IMEX=1';".
Issue:
In sql server table, i declared column "Job number" as Varchar.
While loading the data from SSIS to table, it loads the numeric data correctly, but displayed "NULL" for Varchar data (ie)
10132386
10289093
10132628
10191002
10162417
10137136
10189017
10160436
10133333
10192752
10182457
NULL
NULL
NULL
NULL
While previewing the data in OLEDB source itself, it is showing "NULL".
Please advise how to load the varchar data for this scenario.
Regards
SqlStud
January 11, 2013 at 12:15 am
I'd suggest exporting the Excel data to CSV format and importing that instead. You'll save yourself a grey hair or two. Mixed-datatype columns in Excel have cost the world numerous man-years of pain.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 11, 2013 at 6:06 am
Phil Parkin (1/11/2013)
I'd suggest exporting the Excel data to CSV format and importing that instead. You'll save yourself a grey hair or two. Mixed-datatype columns in Excel have cost the world numerous man-years of pain.
Thanks Phil.
Asked teh client to sent the file in CSV format
Regards
SqlStud
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply