Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlval
Starting Member
12 Posts |
 Posted - 2011-06-02 : 11:46:43
|
| Hello!I have a weird issue with Excel source file. There is a column with transaction dates in the file. When I open it in Excel, I see all dates with no problem. When I preview it in SSIS, some of the dates show NULL instead of values as if some kind of additional format was applied to the spreadsheet, and SSIS does not recognize it. Is there any way it can be fixed so all dates would be available? Please don't advise to convert a file to ASCII format, I do not create it myself, and Excel is what the client wants.Thank you for your help? |
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-06-02 : 13:24:30
|
| Try one of these:1. This is one of problem with importing Excel,put in a dummy first row with data in the form rewuired for each column, e.g. TEXT for text, 1234 for number, etc. , then delete the first record imported. tends to solve the problem for us.2.One could just save the Excel as a CSV file, load the file via BULK INSERT or OPENROWSET and be done. 3.Load the Excel file with First Row as Header off even it contains the header. This can ensure all the fields are read in text format. And then add a Conditional Split to skip the first row.4.Solution : Set ConnectionString property of Excel source file as following Provider=Microsoft.Jet.OLEDB.4.0;Data Source=filepath\filename.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1“; NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. Source:http://www.sqlservercentral.com/Forums/Topic491125-1259-1.aspxAlso check these:http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62697/ |
 |
|
|
|
|
|
|
|
Excel Source does not recognize dates