Re: Excel field returning NULLS in SQL field...
From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 03/22/04
- Next message: Andrew Gale: "Re: package incomplete after error"
- Previous message: Peter Newman: "Re: Data Driven Query - Small Problem using TextFile source"
- In reply to: Josh Mikow: "Re: Excel field returning NULLS in SQL field..."
- Messages sorted by: [ date ] [ thread ]
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 Spreadsheet > > > 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. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
- Next message: Andrew Gale: "Re: package incomplete after error"
- Previous message: Peter Newman: "Re: Data Driven Query - Small Problem using TextFile source"
- In reply to: Josh Mikow: "Re: Excel field returning NULLS in SQL field..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|