Re: Excel field returning NULLS in SQL field...

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 17:59:21 -0000

Glad you bottomed this problem out. I often find that things like this
catch you out. What you think you are looking at is not what you are
actually looking at. 

-- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Josh Mikow" <jmikow@applebyco.com> wrote in message
news:uwMBVWDEEHA.2404@TK2MSFTNGP11.phx.gbl...
> Allan,
>
> I think we narrowed down the issue to being the excel file.  We have other
> DTS packages which do what you tested and work fast and fine.  With the
> excel file which gives us trouble it appears that it is generated from a
> crystal report and exported.  When using this file it runs much slower and
> has problems compared to the other excel files we have.
>
> When we converted it to a standard excel file and it appears to work much
> better.
>
> Thanks for your research.
>
> Josh
>
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:%230MBjmMDEHA.1600@tk2msftngp13.phx.gbl...
> > OK done a very quick test
> >
> >
> > Excel table looks like
> >
> > Col1        Col2        Col3
> > I                am            Here
> > Yes            you            Are
> >
> >
> > SQL Server table looks like
> >
> > CREATE TABLE Exceltester(
> >  col1 varchar(20),
> >  col2 varchar(20),
> >  col2Again varchar(20),
> >  col3 varchar(20)
> >    )
> > GO
> >
> >
> >
> > I simply pump as you would expect by the names of the columns and I get.
> >
> > col1                 col2                 col2Again            col3
>
> -------------------- -------------------- -------------------- -----------
> --
> > ------- 
> > I                    Am                   Am                   Here
> > Yes                  You                  You                  are
> >
> >
> > Is what I am doing different to you?
> >
> >
> > -- 
> >
> > ----------------------------
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:...
> > > OK So I can test
> > >
> > > 1 Excel Spread***
> > > 1 SQL Server table
> > >
> > > 2 columns in SQL Table access same Excel attribute
> > >
> > > OK  I will run some texts and get back to you.
> > >
> > > -- 
> > >
> > > ----------------------------
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > > "Josh Mikow" <jmikow@applebyco.com> wrote in message
> > > news:uIzVJwGDEHA.1588@tk2msftngp13.phx.gbl...
> > > > Allan,
> > > >
> > > > Actually that is not what is happening.  When the DTS runs a test it
> > > appears
> > > > fine but when the package is saved it does not import correctly.  It
> > > either
> > > > has NULL values or blank values.
> > > >
> > > > We have tried changing the registry value and it does not appear to
> > change
> > > > the results.
> > > >
> > > > We have been trying to solve this for a few weeks now and nothing
has
> > > > worked.
> > > >
> > > > Any more suggestions would be great!
> > > >
> > > > Thanks,
> > > >
> > > > Josh
> > > >
> > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > > > news:%23mUIPuFDEHA.1452@TK2MSFTNGP09.phx.gbl...
> > > > > Are you seeing this problem?
> > > > >
> > > > > Excel Inserts Null Values
> > > > > (http://www.sqldts.com/default.aspx?254)
> > > > >
> > > > > -- 
> > > > >
> > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > > I support PASS - the definitive, global community
> > > > > for SQL Server professionals - http://www.sqlpass.org
> > > > >
> > > > >
> > > > > "Josh Mikow" <jmikow@applebyco.com> wrote in message
> > > > > news:eAOch$EDEHA.2712@TK2MSFTNGP10.phx.gbl...
> > > > > > I'm importing an Excel file into SQL using a DTS package.
There's
> a
> > > > > column
> > > > > > in the Excel file called 'Description.'  There are two fields in
> my
> > > SQL
> > > > > > table called 'DetailDesc' and 'ShortDesc.'  Both of these fields
> > point
> > > > to
> > > > > > the same 'Description' column in my Excel file.  The only
> exception
> > is
> > > > > that
> > > > > > the 'ShortDesc' field is only grabbing the first 40 characters
> > (using
> > > > Left
> > > > > > function).
> > > > > >
> > > > > > After running the DTS package, I see that the 'DetailDesc'
> imported
> > > > > > correctly, but the 'ShortDesc' returned all blanks.  I thought
> maybe
> > > it
> > > > > had
> > > > > > something to do with the Left function I was using, so I took it
> off
> > > and
> > > > > did
> > > > > > the same import as the 'DetailDesc.'  But now it just returns
> NULLs.
> > > So
> > > > > I'm
> > > > > > doing the exact same thing with two different SQL fields
pointing
> to
> > > an
> > > > > > Excel column, with one importing it correctly and the other
> > returning
> > > > > NULLs.
> > > > > >
> > > > > > I think I've narrowed it down.  It's when the two SQL fields are
> > > > accessing
> > > > > > the same column that I'm getting the problem.  If I take away
> either
> > > > one,
> > > > > > the other works fine, but with both, the second link created
> > receives
> > > > > NULLs.
> > > > > >
> > > > > > When I test the import links from within the ActiveX properties,
I
> > see
> > > > the
> > > > > > data fine.  But when it's actually executed, it's NULLs.  So I'm
> not
> > > > sure
> > > > > > why it will work in test mode, but not during execution.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>