dbTalk Databases Forums  

Lost data importing from Excel

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.dts Lost data importing from Excel

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts


Discuss Lost data importing from Excel in the microsoft.public.sqlserver.dts forum.



Reply
Page 2 of 2 < 1 2
 
Thread Tools Display Modes
  #11  
Old   
D.Stone@ed.ac.uk
 
Posts: n/a

Default Re: Lost data importing from Excel - 07-22-2008 , 08:56 AM





Fear not, Dennis, the column is consistent - the values are all 2
characters (which just happen to be digits). It seems that DTS cannot
handle values with a leading zero even though Excel knows the column
values are text and the SQL Server field is char(2). I've tried
setting the Excel column to a custom format of '00', but then an Excel
value of '06' transforms to '6' in SQL Server.

Dave

On Jul 8, 5:39 pm, "Irvine, Dennis" <dennis.irv... (AT) plumasnt (DOT) com>
wrote:
Quote:
Well your column in Excel should be consistent. they should all be text or
all be numbers.
If I were doing it, I would just format the Excel column to show leading
zeros and make all the entries in the Excel workbook numbers.

Dennis

D.St... (AT) ed (DOT) ac.uk> wrote in message

news:7c504f4e-4613-415b-b16b-308d83f4dd3a (AT) d1g2000hsg (DOT) googlegroups.com...

I'm trying to import a small spreadsheet from Excel into a pre-created
SQL Server table. One column is defined as char(2), and the data
values are actually 2 digits. When the leading digit is non-zero, the
data transfers OK, but if the code is, say, '06', I get a null in SQL
Server. I'm running SQL Server 2000, and using a simple DTS package
with straightforward column copies for the transformations.

Is there anything I can do in either DTS or Excel to ensure this data
is transferred? I notice Excel flags these values as errors because
they are numbers formatted as text (preceded by ').

Cheers,

Dave


Reply With Quote
  #12  
Old   
D.Stone@ed.ac.uk
 
Posts: n/a

Default Re: Lost data importing from Excel - 07-22-2008 , 08:56 AM





Fear not, Dennis, the column is consistent - the values are all 2
characters (which just happen to be digits). It seems that DTS cannot
handle values with a leading zero even though Excel knows the column
values are text and the SQL Server field is char(2). I've tried
setting the Excel column to a custom format of '00', but then an Excel
value of '06' transforms to '6' in SQL Server.

Dave

On Jul 8, 5:39 pm, "Irvine, Dennis" <dennis.irv... (AT) plumasnt (DOT) com>
wrote:
Quote:
Well your column in Excel should be consistent. they should all be text or
all be numbers.
If I were doing it, I would just format the Excel column to show leading
zeros and make all the entries in the Excel workbook numbers.

Dennis

D.St... (AT) ed (DOT) ac.uk> wrote in message

news:7c504f4e-4613-415b-b16b-308d83f4dd3a (AT) d1g2000hsg (DOT) googlegroups.com...

I'm trying to import a small spreadsheet from Excel into a pre-created
SQL Server table. One column is defined as char(2), and the data
values are actually 2 digits. When the leading digit is non-zero, the
data transfers OK, but if the code is, say, '06', I get a null in SQL
Server. I'm running SQL Server 2000, and using a simple DTS package
with straightforward column copies for the transformations.

Is there anything I can do in either DTS or Excel to ensure this data
is transferred? I notice Excel flags these values as errors because
they are numbers formatted as text (preceded by ').

Cheers,

Dave


Reply With Quote
  #13  
Old   
D.Stone@ed.ac.uk
 
Posts: n/a

Default Re: Lost data importing from Excel - 07-22-2008 , 08:56 AM


Fear not, Dennis, the column is consistent - the values are all 2
characters (which just happen to be digits). It seems that DTS cannot
handle values with a leading zero even though Excel knows the column
values are text and the SQL Server field is char(2). I've tried
setting the Excel column to a custom format of '00', but then an Excel
value of '06' transforms to '6' in SQL Server.

Dave

On Jul 8, 5:39 pm, "Irvine, Dennis" <dennis.irv... (AT) plumasnt (DOT) com>
wrote:
Quote:
Well your column in Excel should be consistent. they should all be text or
all be numbers.
If I were doing it, I would just format the Excel column to show leading
zeros and make all the entries in the Excel workbook numbers.

Dennis

D.St... (AT) ed (DOT) ac.uk> wrote in message

news:7c504f4e-4613-415b-b16b-308d83f4dd3a (AT) d1g2000hsg (DOT) googlegroups.com...

I'm trying to import a small spreadsheet from Excel into a pre-created
SQL Server table. One column is defined as char(2), and the data
values are actually 2 digits. When the leading digit is non-zero, the
data transfers OK, but if the code is, say, '06', I get a null in SQL
Server. I'm running SQL Server 2000, and using a simple DTS package
with straightforward column copies for the transformations.

Is there anything I can do in either DTS or Excel to ensure this data
is transferred? I notice Excel flags these values as errors because
they are numbers formatted as text (preceded by ').

Cheers,

Dave


Reply With Quote
  #14  
Old   
D.Stone@ed.ac.uk
 
Posts: n/a

Default Re: Lost data importing from Excel - 07-22-2008 , 08:56 AM


Fear not, Dennis, the column is consistent - the values are all 2
characters (which just happen to be digits). It seems that DTS cannot
handle values with a leading zero even though Excel knows the column
values are text and the SQL Server field is char(2). I've tried
setting the Excel column to a custom format of '00', but then an Excel
value of '06' transforms to '6' in SQL Server.

Dave

On Jul 8, 5:39 pm, "Irvine, Dennis" <dennis.irv... (AT) plumasnt (DOT) com>
wrote:
Quote:
Well your column in Excel should be consistent. they should all be text or
all be numbers.
If I were doing it, I would just format the Excel column to show leading
zeros and make all the entries in the Excel workbook numbers.

Dennis

D.St... (AT) ed (DOT) ac.uk> wrote in message

news:7c504f4e-4613-415b-b16b-308d83f4dd3a (AT) d1g2000hsg (DOT) googlegroups.com...

I'm trying to import a small spreadsheet from Excel into a pre-created
SQL Server table. One column is defined as char(2), and the data
values are actually 2 digits. When the leading digit is non-zero, the
data transfers OK, but if the code is, say, '06', I get a null in SQL
Server. I'm running SQL Server 2000, and using a simple DTS package
with straightforward column copies for the transformations.

Is there anything I can do in either DTS or Excel to ensure this data
is transferred? I notice Excel flags these values as errors because
they are numbers formatted as text (preceded by ').

Cheers,

Dave


Reply With Quote
  #15  
Old   
D.Stone@ed.ac.uk
 
Posts: n/a

Default Re: Lost data importing from Excel - 07-22-2008 , 08:56 AM


Fear not, Dennis, the column is consistent - the values are all 2
characters (which just happen to be digits). It seems that DTS cannot
handle values with a leading zero even though Excel knows the column
values are text and the SQL Server field is char(2). I've tried
setting the Excel column to a custom format of '00', but then an Excel
value of '06' transforms to '6' in SQL Server.

Dave

On Jul 8, 5:39 pm, "Irvine, Dennis" <dennis.irv... (AT) plumasnt (DOT) com>
wrote:
Quote:
Well your column in Excel should be consistent. they should all be text or
all be numbers.
If I were doing it, I would just format the Excel column to show leading
zeros and make all the entries in the Excel workbook numbers.

Dennis

D.St... (AT) ed (DOT) ac.uk> wrote in message

news:7c504f4e-4613-415b-b16b-308d83f4dd3a (AT) d1g2000hsg (DOT) googlegroups.com...

I'm trying to import a small spreadsheet from Excel into a pre-created
SQL Server table. One column is defined as char(2), and the data
values are actually 2 digits. When the leading digit is non-zero, the
data transfers OK, but if the code is, say, '06', I get a null in SQL
Server. I'm running SQL Server 2000, and using a simple DTS package
with straightforward column copies for the transformations.

Is there anything I can do in either DTS or Excel to ensure this data
is transferred? I notice Excel flags these values as errors because
they are numbers formatted as text (preceded by ').

Cheers,

Dave


Reply With Quote
  #16  
Old   
D.Stone@ed.ac.uk
 
Posts: n/a

Default Re: Lost data importing from Excel - 07-22-2008 , 08:56 AM


Fear not, Dennis, the column is consistent - the values are all 2
characters (which just happen to be digits). It seems that DTS cannot
handle values with a leading zero even though Excel knows the column
values are text and the SQL Server field is char(2). I've tried
setting the Excel column to a custom format of '00', but then an Excel
value of '06' transforms to '6' in SQL Server.

Dave

On Jul 8, 5:39 pm, "Irvine, Dennis" <dennis.irv... (AT) plumasnt (DOT) com>
wrote:
Quote:
Well your column in Excel should be consistent. they should all be text or
all be numbers.
If I were doing it, I would just format the Excel column to show leading
zeros and make all the entries in the Excel workbook numbers.

Dennis

D.St... (AT) ed (DOT) ac.uk> wrote in message

news:7c504f4e-4613-415b-b16b-308d83f4dd3a (AT) d1g2000hsg (DOT) googlegroups.com...

I'm trying to import a small spreadsheet from Excel into a pre-created
SQL Server table. One column is defined as char(2), and the data
values are actually 2 digits. When the leading digit is non-zero, the
data transfers OK, but if the code is, say, '06', I get a null in SQL
Server. I'm running SQL Server 2000, and using a simple DTS package
with straightforward column copies for the transformations.

Is there anything I can do in either DTS or Excel to ensure this data
is transferred? I notice Excel flags these values as errors because
they are numbers formatted as text (preceded by ').

Cheers,

Dave


Reply With Quote
  #17  
Old   
D.Stone@ed.ac.uk
 
Posts: n/a

Default Re: Lost data importing from Excel - 07-22-2008 , 08:56 AM


Fear not, Dennis, the column is consistent - the values are all 2
characters (which just happen to be digits). It seems that DTS cannot
handle values with a leading zero even though Excel knows the column
values are text and the SQL Server field is char(2). I've tried
setting the Excel column to a custom format of '00', but then an Excel
value of '06' transforms to '6' in SQL Server.

Dave

On Jul 8, 5:39 pm, "Irvine, Dennis" <dennis.irv... (AT) plumasnt (DOT) com>
wrote:
Quote:
Well your column in Excel should be consistent. they should all be text or
all be numbers.
If I were doing it, I would just format the Excel column to show leading
zeros and make all the entries in the Excel workbook numbers.

Dennis

D.St... (AT) ed (DOT) ac.uk> wrote in message

news:7c504f4e-4613-415b-b16b-308d83f4dd3a (AT) d1g2000hsg (DOT) googlegroups.com...

I'm trying to import a small spreadsheet from Excel into a pre-created
SQL Server table. One column is defined as char(2), and the data
values are actually 2 digits. When the leading digit is non-zero, the
data transfers OK, but if the code is, say, '06', I get a null in SQL
Server. I'm running SQL Server 2000, and using a simple DTS package
with straightforward column copies for the transformations.

Is there anything I can do in either DTS or Excel to ensure this data
is transferred? I notice Excel flags these values as errors because
they are numbers formatted as text (preceded by ').

Cheers,

Dave


Reply With Quote
Reply
Page 2 of 2 < 1 2

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top