Excel job + column import fail

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.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Excel job + column import fail

Author

Topic

nduggan23
Starting Member

42 Posts

Posted - 2008-05-23 : 07:44:03

Hi all, need help before i break this pc! trying to get an import job to read from an excel file. Normally this works fine, no issues but have a certain excel file that is just not importing correctly. one row is importing nulls for some values but without any visible reason. I have a file with over 15000 rows. 9 columns. last column stores a year in the format yyyy. this is the problem column. in the import job it shows up as a float. have checked the format of the cell and it says General in the excel file. when i execute the job over 5000 row come throught with null Years. cant see a reason for this. anyone able to shed some light please..

N.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 07:50:55

Can you try using OPENROWSET function inside your job and specifying a format file to consider the year column in correct format?

http://www.mssqltips.com/tip.asp?tip=1202

nduggan23
Starting Member

42 Posts

Posted - 2008-05-23 : 08:10:35

Im trying to go the other way around. Excel into SQL. Tried just selecting the data from the excel file and still get the same problem...


SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Test.xls', 'SELECT * FROM [Sheet1$]')

Sample output


39569.7021533565	XXX	XXX	996	11264	4	Consultancy	        Indirect Tax	File 1	2007
39569.7021533565	XXX	XXX	996	11264	4	Consultancy	        Indirect Tax	File 1	2008
39569.699284838	        XXX	XXX	999	16157	4	Personal Compliance	Personal Tax	File 1	NULL
39569.699284838	        XXX	XXX	999	16157	4	Personal Compliance	Personal Tax	File 1	2007
39569.697881713	        XXX	XXX	999	12075	4	Personal Compliance	Personal Tax	File 1	2006
39569.697881713	        XXX	XXX	999	12075	4	Personal Compliance	Personal Tax	File 1	2007
39569.6898528935	XXX	XXX	999	16079	3	Personal Compliance	Personal Tax	File 1	NULL
39569.6898528935	XXX	XXX	999	16079	3	Personal Compliance	Personal Tax	File 1	2007
39569.6878193287	XXX	XXX	999	17825	4	Consultancy	        Personal Tax	File 1	2007
39569.6878193287	XXX	XXX	999	17825	4	Consultancy	        Personal Tax	File 1	NULL

neversaydie
Starting Member

3 Posts

Posted - 2008-05-23 : 22:31:19

Tried just importing the singl problem column ,then check if there is any problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources