I am trying to pull the number preceeding the decimal, but I want my output in a fixed lengh. Here is what I tried thinking it might work, however it did not.
sf_retail = right('000' + floor(cast(labsf.last_retail_price as varchar)),3),
the number I am running this against is '0000001.45' I would like my output to read '001'.....I am getting only '1'
Any suggestions?We just did something like that...
Check out...
http://www.dbforums.com/t987264.html|||Thanks, that was helpful. I ended up using:
sf_retail = right('000' + convert(varchar(3), floor(labsf.last_retail_price)),3),
2012年3月29日星期四
floating point truncation
How can I truncate a floating point number to required number of decimal points
Eg:
100.642364074 to 100.64 and 67.643929847 to 67.645
Thanks.STR|||...And the last number should be rounded to 67.644 not 67.645
Eg:
100.642364074 to 100.64 and 67.643929847 to 67.645
Thanks.STR|||...And the last number should be rounded to 67.644 not 67.645
Floating point precision
Hi, I have a SP which adds a bunch of the same number together 365
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype'
TIA
Mark
================================= 2006MJGOOGLENEWSDon't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegroups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================> 2006MJGOOGLENEWS
>|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype'
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
---
21.000000000000046000000000000000
---
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
---
21.000000000000012500000000000000
---
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
> >Hi, I have a SP which adds a bunch of the same number together 365
> >times
> >
> >The base number is 0.0575342465753425
> >
> >I am storing this as a float but when I view it in the first iteration
> >it appears as
> >
> >0.0575342
> >
> >The next one is
> >
> >0.115068
> >
> >and the 10th one is
> >
> >0.575342
> >
> >and finally the 365 one is
> >
> >21.0575
> >
> >However if I multiply the original number by 365 I get the following
> >21.0000000000000125
> >
> >Which is vastly different from the one I got using a float.
> >
> >How can I get more precision using MSSQL float? Am I using the wrong
> >Datatype'
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> ---
> 21.000000000000046000000000000000
>
> ---
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> ---
> 21.000000000000012500000000000000
>
> ---
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVPsql
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype'
TIA
Mark
================================= 2006MJGOOGLENEWSDon't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegroups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================> 2006MJGOOGLENEWS
>|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype'
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
---
21.000000000000046000000000000000
---
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
---
21.000000000000012500000000000000
---
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
> >Hi, I have a SP which adds a bunch of the same number together 365
> >times
> >
> >The base number is 0.0575342465753425
> >
> >I am storing this as a float but when I view it in the first iteration
> >it appears as
> >
> >0.0575342
> >
> >The next one is
> >
> >0.115068
> >
> >and the 10th one is
> >
> >0.575342
> >
> >and finally the 365 one is
> >
> >21.0575
> >
> >However if I multiply the original number by 365 I get the following
> >21.0000000000000125
> >
> >Which is vastly different from the one I got using a float.
> >
> >How can I get more precision using MSSQL float? Am I using the wrong
> >Datatype'
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> ---
> 21.000000000000046000000000000000
>
> ---
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> ---
> 21.000000000000012500000000000000
>
> ---
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVPsql
Floating point precision
Hi, I have a SP which adds a bunch of the same number together 365
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype?
TIA
Mark
=================================
2006MJGOOGLENEWS
Don't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegr oups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype?
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype?
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype?
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
21.000000000000046000000000000000
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
21.000000000000012500000000000000
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP
|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> 21.000000000000046000000000000000
>
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> 21.000000000000012500000000000000
>
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVP
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype?
TIA
Mark
=================================
2006MJGOOGLENEWS
Don't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegr oups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype?
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype?
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype?
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
21.000000000000046000000000000000
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
21.000000000000012500000000000000
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP
|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> 21.000000000000046000000000000000
>
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> 21.000000000000012500000000000000
>
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVP
Floating point precision
Hi, I have a SP which adds a bunch of the same number together 365
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype'
TIA
Mark
=================================
2006MJGOOGLENEWSDon't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegroups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype'
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
---
21.000000000000046000000000000000
---
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
---
21.000000000000012500000000000000
---
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> ---
> 21.000000000000046000000000000000
>
> ---
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> ---
> 21.000000000000012500000000000000
>
> ---
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVP
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype'
TIA
Mark
=================================
2006MJGOOGLENEWSDon't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegroups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype'
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
---
21.000000000000046000000000000000
---
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
---
21.000000000000012500000000000000
---
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> ---
> 21.000000000000046000000000000000
>
> ---
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> ---
> 21.000000000000012500000000000000
>
> ---
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVP
2012年3月26日星期一
Flat File With Fixed Length Header and No Delimeter
Hi,
I'm trying to extract data from a Flat File which is as fixed length as they come. The file has a header, which simply contains the number of records in the file, followed by the records, with no header delimeter (No CR/LF, nothing).
For example a file would look like the following:
00000003Name1Address1Name2Address2Name3Address3
So this has 3 records (indicated by the first 8 characters), each consisting of a Name and Address.
I can't see a way to extract the data using a flat file connection, unless we add a delimeter for the header (not possible at this stage). Am I wrong?
Any suggestions on possible solution would be much appreciated - I'm thinking Ill have to write a script to parse the file manually.
Thanks in advance,
Scott
Do you need the data in the first row?
You can just ignore it by setting the "Header rows to skip" setting to 1.
K
|||Yes. Essentially the file is just one row..Which would include the header details (number fo records) then all of the fixed length records follow (on the same line)
Scott
|||Scott,
Given the unstructured nature of this file I think you will have to parse it out yourself in a script task. This isn't as daunting as it sounds. First clue I can give you is that it will have to be an asynchronous script task.
You can still import it into the pipeline using a Flat File Connection Manager though. It'll be a 1-column, 1-row file that's all.
-Jamie
|||
Using a script component of type source should be easiest. A little example follows.
Here is my sample file, representing an 8 byte header, followed by three rows of two columns, 10 and 20 bytes respectively.
00000003A234567890B234567890C234567890D234567890E234567890F234567890G234567890H234567890I234567890
The results table will look a bit like this-
Name Address
A234567890 B234567890C234567890
D234567890 E234567890F234567890
G234567890 H234567890I234567890
You will need to create the two columns in the script component , Name and Address as DT_WSTR 10 and 20 in length.
Now the code-
Public Class ScriptMain
Inherits UserComponent
Private stream As StreamReader
Public Overrides Sub CreateNewOutputRows()
Dim headerRecordCount As Integer
Dim recordCount As Integer = 0
'// Get filename from connection, using full acquire method
Dim filename As String = CType(Me.Connections.Connection.AcquireConnection(Nothing), String)
'// Open source file
stream = New StreamReader(filename)
'// Reader header block, 8 characters
Dim headerBuffer(7) As Char
If stream.ReadBlock(headerBuffer, 0, 8) = 8 Then
'// Store record count for later use in validation
headerRecordCount = CType(New String(headerBuffer), Integer)
Else
Throw New Exception("Invalid file format, header not valid.")
End If
With Output0Buffer
While stream.Peek > 0
'// Add data rows
.AddRow()
.Name = ReadColumn(10)
.Address = ReadColumn(20)
recordCount = recordCount + 1
End While
'// Close down buffer
.SetEndOfRowset()
'// Check record count
If recordCount = headerRecordCount Then
Me.Log(String.Format("Header row count ({0}) matched toital rows found.", headerRecordCount), 1, Nothing)
Else
Throw New Exception(String.Format("Invalid file format, header row count ({0}) not equal to rows found ({1}).", headerRecordCount, recordCount))
End If
End With
End Sub
Private Function ReadColumn(ByVal length As Integer) As String
Dim buffer(length - 1) As Char
If stream.Read(buffer, 0, length) = length Then
Return New String(buffer)
Else
Throw New Exception("Invalid file format, full column length not found.")
End If
End Function
End Class
|||Thanks for the answers guys..Have gone with using a script component of type source, as per code above. With one change...Just ensured that the stream is closed after processing to ensure the resources are released...
I also added an extra Output for the script which holds the header details - my real data file has extra (useful) details in the header.
Thanks again..
Scott
2012年3月22日星期四
Flat File Data Source with variable number of delimited columns
I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file).
Is it possible to use a single flat file data source that will work with all of the different width files?
No.
The only thing you can do is read in each line as one big record and then maybe use substrings or something to pick apart the files.
Flat file CSV problem
Hi all,
I hope someone can help with a problem i'm having.
I want to process a large number of CSV files into various tables in an SQL database.
The CSV file contains entries on a row by row basis relating to specific events (indicated by an eventID in column 0).
Eventually i think i want to be using a conditional split to process each row seperately depending on the eventID but before i get this far i am having a problem with the source data.
Each event can have varying amounts of columns filled in in the CSV file. And each CSV file can have multiple event types in it.
The flat file manager seems to merge a number of different rows into one within the preview pane. It seems to ignore the end of row delimiter of CrLf.
Can anyone please help me to sort this so that each row is on its own and will allow me to pass the structured data set to a conditional split task?
Many thanks in advance,
GrantOk,
I seem to have managed to set all the data for each row into one component and have a script to extract the EventID to one output column and the remaining parameters to a second column. I need to perform a check on the EventID by passing it and another variable into a stored procedure. How do i go about doing this in the data flow section? is it possible or do i have to look at using the control flow section for this?
Thanks,
Grant|||
Grant,
What kind of check does that procedure performed? Keep in mind that any operation you define in the dataflow will be performed in row by row basis; so a call to a procedure in a data flow will be executed as many times as rows you have in the file. Since you already succeed on separating the eventID from the rest I would try to use a conditional split transformation based on the EventID value and then to performed specific transformations/checks to every data pipeline.
Rafael Salas
|||Hi Rafael,Thanks for the response. The stored procedure i was talking about returns a value based on the event ID. Having thought about what you said i can set this value manually after the conditional split has been carried out and i know what the event ID is. Does that sound more plausible?
I am trying to rewrite a windows application that processes these files, initially this used a stored procedure to process each row of data. The main stored procedure calls other sub procedures within it. The problem was that the stored procedure itself was getting very unwieldly with a large number of if and nested if statement which meant that the addition of new events was complex and time consuming. Using the SSIS package a believe i can make this a much easier process to manage.
Is it still possible to call a stored procedure in the dataflow task once the conditional split has been performed? i realise that there may be other stored procedures required once i have my data row?
I have just attempted to run some SQL code on a per row basis and have discovered that i cannot seem to user variables or parameters in the OLE DB Command task. How would i go about either returning a value dependant on if the current eventID exists in another table in the database or indeed; how to insert data into a specific table if it doesn't exist.
After i do that then i can get on to processing the event data to the table where this is stored. I hope that makes sense.
Many thanks,
Grant|||
Wow a lot of questions!
First to all let me clarify that there is nothing wrong with having OLE DB Command tasks in your Dataflow; it is just that as personal pratice I try to use bulk operations against the DB when possible.
If what you are trying to use thr stored procedures for is to check if a row exists in a table; you can use the lookup transformation in your dataflow; then use the error output as your insert pipeline and the output as the 'already exists' kind of pipeline (or just not use it if you want to ignore them). The lookup can be also returned other columns from your lookup table if that is what you need
Rafael Salas
|||I tend to ask a lot of them yes :)I looked into the lookup transformation which i can see how i would use the error output etc. I was then using an OLEDB destinbation to insert rows to the table. The problem with the OLEDB destination is that i cannot loop back to the lookup. With an oledb command would i script the insert command and then be able to loop back to the lookup transformation.
E.g.
Should this be ok to enter as a SQL Command:
if (select count(*) from SerialPartRev Where SerialPartRev.SerialNo = ? and SerialPartRev.PartNo = ?) = 0
begin
Insert into SerialPartRev (SerialNo, PartNo, RevisionNo)
Values(?,?,?)
end
The reason i want to do this is so that the SerialPartRev table is update automatically. I require to do the same thing with a Username table. The big problem is how to check again for the entry before processing further. As i have found looping back to the lookup isn't possible due to it only accepting one input path.
I'm quite happy to accept that i am doing this wrong and that maybe someone could suggest another process for implementing this.
Cheers,
Grant|||Rafael,
I have been reading you're previous post again and whilst i understand what you are saying about using the output and error output in the pipeline depending on if the row exists but one question still remains. Firstly i have to say that there will be a couple of instances where i have to check if data from the columns in the flat file exists in SQL tables. Regardless of if i have to insert the row manually or if it already exists after both of these operations the output still has to go to the one conditional split task. Effectively it splits the path in two and then rejoins after carrying out the necessary functionality. Is this possible without the use of scripting? I believe i can achieve this via a script although it will mean exposing a password in plain taxt so that the database connection string will work properly.
Thank you,
Grant|||Actually, forget it. I have managed to call the stored procedures form the OLE DB commands. I have no idea what i was doing wrong previously but it seems to be working now.
Thanks for you're help on this matter it was most appreciated. I'm sure i'll have more questions in time.
Cheers,
Grant
2012年3月21日星期三
Flat File and uneven number of columns
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493
Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.
For example: (assume CR LF row delimiter)
The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test
"Normally" I'd expect this
Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.
So we get
I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"
I'm sure it's there.....help!
(By the way SSIS team, great job on the package love using it)
Try using ragged right instead of delimited.
Thanks,
Matt
|||Ah see if only it was that simple Matt. "Ragged right files are files in which every column has a fixed width, except for the last column, which is delimited by the row delimiter."
So the only thing which can be Ragged in fact is the last column. In my example I've purposely put varying column widths so the old "Ragged Right" doesn’t apply (this is more the norm for ragged files).
So any ideas how this is going to work in SSIS...I must be missing something simple here this is a very common problem that the old DTS, Excel, Access, and other programs deal with quite well.
help!
|||Unfortunately we only support ragged right for fixed width so for this scenario you would have to read it as a single column and then split it using a script component or you could write a custom component that split it.
Matt
|||but this is a bug or changed behavior from the old sql server 2000 dts which when you had a comma or tab deliminited file some of the rows had extra columns after the last valid one.. it ignored those extra columns.
the new functionality is to instead append the commas/delimiters into the data pulled in from the last column..
This is causing us major grief becuase now none of our dts's work in the new sql server 2005 so it is not backwards compatible..
|||
This is similar to an issure raised the other day. I produced a sample package on how to handle this.
http://sqlblogcasts.com/files/4/integration_services/entry412.aspx
|||I'm with you Igkahn. It's a bug. No way is this a feature! This is killing us too. If I have to script this as set out below then something fundimentally is wrong with SSIS. I cut one of our large SQL 2000 servers over to SQL 2005 and we are stopping there until fixes are supplied for errors like this. I'm now having to use the SQL 2000 DTS instead of the nice looking however functionality poor SSIS.
Please get this fixed.
Garry Swan
Information Systems Manager
CSIRO
Australia
If you feel strongly about it then you should raise it through the Feedback centre and through your MS representative if you have one.
|||
Calling it a bug implies that is supposed to do something else and I'm not sure that is the case. I'm not denying that it MIGHT be a bug - hopefully Matt will reply again and tell us (By the way, just because some behaviour was present in DTS, you shouldn't assume the same would be true of SSIS. This is a replacement, not an upgrade).
Regardless, I can understand why this is causing headache. Have you logged it at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx)? If not, then you shouldn't expect that the feature will make it into the next version.
I don't understand why you call SSIS functionally poor. As Simon explained this can still be achieved quite easily. Surely the fact that there is "more than one way to skin a cat" so to speak means the product is functionally rich as opposed to poor? Is the fact that something cannot be achieved using your "preferred" method really prohibiting you from moving to a superior product?
-Jamie
|||From my research there has been a lot of posts about this issue in the last couple of months. But im still looking for a proper solution, is there a fix available? Is there a code sample available for a SSIS source component or whatnot, that threats uneven columns properly?
I've looked through the sample for the source component and could not get it to run (something about no compatible component in the dll)
Since people do need to import data from delimited files very often, SSIS should be expending the functionality of what there was in the DTS world... but in this case it was decided to limit it. Whoever came up with the delimiter for each column must have been so proud of himself that he generlized and removed the need for a row delimiter....
Well you haven't, and we want it back. :) It's causing many people, a lot of greef.
Or at least release the source for a flat file handler which people can customize to their liking.
There is so many flat file formats out there.... Why be so restrictive when dealing with them?
|||Whats wrong with the sample I have provided.
SSIS focus was on building a framework that was performant, scalable and extensible. For this reason the components out of the box don't satisfy everyones requirements. But I have shown how it is very easy to extend SSIS with a script component to achieve your goal.
If you want a more polished solution you can develop your own custom component but that is another level of complexity that really isn't needed due to the power of the script component.
|||Someone mentioned that the code provided by Sabin didn't work for him/her. I have not tried that code.
Here is a version (another way to skin the cat) that can be tried. Please note that your first row should contain all the column names for this to work. This script assumes that the input is configured in a way that all data on one row constitute a column. So basically, there is just one column in each row. The output of this script is also one column in each row. However, the output created by this script can be "understood" by SSIS file connection manager. Finally, assumption is that "tab" is column delimiter. You can change that below, if that is not the case.
Private dataRow As Boolean = False
Public Function AppendMissingTabs(ByVal Row As InputBuffer) As String
Dim columns As String() = Nothing
Dim outputRow As String = Nothing
Dim outputString As String = Nothing
Dim buffer As StringBuilder = New StringBuilder()
buffer.Append(Row.BigSingleColumn)
columns = Row.BigSingleColumn.Split(New [Char]() {Chr(9)})
If Not dataRow Then
columnCount = columns.Length
End If
Dim N As Integer
If columns.Length < columnCount Then
For N = 1 To columnCount - columns.Length Step 1
buffer.Append(Chr(9))
Next N
End If
dataRow = True
Return buffer.ToString()
End Function
|||
It comes down to if I tell a system that my row finishes with a crlf I don't want it to override my decision and pull the crlf into the data. Why would I want the row delimiter in the data?!? Is this a feature people needed and the SSIS team decided the former was so last week that this new functionality was, in fact, a better solution path...I just doubt it. The required functionality is the way Excel, DTS, Access and many other apps handle this data import. Why is SSIS pulling crlf into my data when I've said this is the end of the row?!?. Just stop and move onto the next row. Put that functionality back in and you've got a great delimited file importer.
Garry Swan
MCDBA
|||What you have actually said is that your column finishes with your delimiter. The flat file source then looks for that delimiter to end the column. The CRLF is not defined as the record delimiter but the delimiter of the last column.
I know that doesn't solve the problem but should explain the reason for the situation.
I still stand by the fact that if you want this functionality using the script component is a valid solution.
|||Last night I uploaded a custom source adapter to SourceForge that would let you parse this file with regular expressions.
http://sourceforge.net/projects/textregexsource
Just connect a file connection manager to it and set a regular expression, and it could produce what you want.
I think a regex like (?'Col1'\w+),*(?'Col2'\w*),*(?'Col3'\w*),*(?'Col4'\w*),*(?'Col5'\w*)\n
might do the trick, though it might need tweaking.
Geof
Flat File and uneven number of columns
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493
Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.
For example: (assume CR LF row delimiter)
The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test
"Normally" I'd expect this
Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.
So we get
I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"
I'm sure it's there.....help!
(By the way SSIS team, great job on the package love using it)
Try using ragged right instead of delimited.
Thanks,
Matt
|||Ah see if only it was that simple Matt. "Ragged right files are files in which every column has a fixed width, except for the last column, which is delimited by the row delimiter."
So the only thing which can be Ragged in fact is the last column. In my example I've purposely put varying column widths so the old "Ragged Right" doesn’t apply (this is more the norm for ragged files).
So any ideas how this is going to work in SSIS...I must be missing something simple here this is a very common problem that the old DTS, Excel, Access, and other programs deal with quite well.
help!
|||Unfortunately we only support ragged right for fixed width so for this scenario you would have to read it as a single column and then split it using a script component or you could write a custom component that split it.
Matt
|||but this is a bug or changed behavior from the old sql server 2000 dts which when you had a comma or tab deliminited file some of the rows had extra columns after the last valid one.. it ignored those extra columns.
the new functionality is to instead append the commas/delimiters into the data pulled in from the last column..
This is causing us major grief becuase now none of our dts's work in the new sql server 2005 so it is not backwards compatible..
|||This is similar to an issure raised the other day. I produced a sample package on how to handle this.
http://sqlblogcasts.com/files/4/integration_services/entry412.aspx
|||I'm with you Igkahn. It's a bug. No way is this a feature! This is killing us too. If I have to script this as set out below then something fundimentally is wrong with SSIS. I cut one of our large SQL 2000 servers over to SQL 2005 and we are stopping there until fixes are supplied for errors like this. I'm now having to use the SQL 2000 DTS instead of the nice looking however functionality poor SSIS.
Please get this fixed.
Garry Swan
Information Systems Manager
CSIRO
Australia
If you feel strongly about it then you should raise it through the Feedback centre and through your MS representative if you have one.
|||Calling it a bug implies that is supposed to do something else and I'm not sure that is the case. I'm not denying that it MIGHT be a bug - hopefully Matt will reply again and tell us (By the way, just because some behaviour was present in DTS, you shouldn't assume the same would be true of SSIS. This is a replacement, not an upgrade).
Regardless, I can understand why this is causing headache. Have you logged it at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx)? If not, then you shouldn't expect that the feature will make it into the next version.
I don't understand why you call SSIS functionally poor. As Simon explained this can still be achieved quite easily. Surely the fact that there is "more than one way to skin a cat" so to speak means the product is functionally rich as opposed to poor? Is the fact that something cannot be achieved using your "preferred" method really prohibiting you from moving to a superior product?
-Jamie
|||From my research there has been a lot of posts about this issue in the last couple of months. But im still looking for a proper solution, is there a fix available? Is there a code sample available for a SSIS source component or whatnot, that threats uneven columns properly?
I've looked through the sample for the source component and could not get it to run (something about no compatible component in the dll)
Since people do need to import data from delimited files very often, SSIS should be expending the functionality of what there was in the DTS world... but in this case it was decided to limit it. Whoever came up with the delimiter for each column must have been so proud of himself that he generlized and removed the need for a row delimiter....
Well you haven't, and we want it back. :) It's causing many people, a lot of greef.
Or at least release the source for a flat file handler which people can customize to their liking.
There is so many flat file formats out there.... Why be so restrictive when dealing with them?
|||Whats wrong with the sample I have provided.
SSIS focus was on building a framework that was performant, scalable and extensible. For this reason the components out of the box don't satisfy everyones requirements. But I have shown how it is very easy to extend SSIS with a script component to achieve your goal.
If you want a more polished solution you can develop your own custom component but that is another level of complexity that really isn't needed due to the power of the script component.
|||Someone mentioned that the code provided by Sabin didn't work for him/her. I have not tried that code.
Here is a version (another way to skin the cat) that can be tried. Please note that your first row should contain all the column names for this to work. This script assumes that the input is configured in a way that all data on one row constitute a column. So basically, there is just one column in each row. The output of this script is also one column in each row. However, the output created by this script can be "understood" by SSIS file connection manager. Finally, assumption is that "tab" is column delimiter. You can change that below, if that is not the case.
Private dataRow As Boolean = False
Public Function AppendMissingTabs(ByVal Row As InputBuffer) As String
Dim columns As String() = Nothing
Dim outputRow As String = Nothing
Dim outputString As String = Nothing
Dim buffer As StringBuilder = New StringBuilder()
buffer.Append(Row.BigSingleColumn)
columns = Row.BigSingleColumn.Split(New [Char]() {Chr(9)})
If Not dataRow Then
columnCount = columns.Length
End If
Dim N As Integer
If columns.Length < columnCount Then
For N = 1 To columnCount - columns.Length Step 1
buffer.Append(Chr(9))
Next N
End If
dataRow = True
Return buffer.ToString()
End Function
|||It comes down to if I tell a system that my row finishes with a crlf I don't want it to override my decision and pull the crlf into the data. Why would I want the row delimiter in the data?!? Is this a feature people needed and the SSIS team decided the former was so last week that this new functionality was, in fact, a better solution path...I just doubt it. The required functionality is the way Excel, DTS, Access and many other apps handle this data import. Why is SSIS pulling crlf into my data when I've said this is the end of the row?!?. Just stop and move onto the next row. Put that functionality back in and you've got a great delimited file importer.
Garry Swan
MCDBA
|||What you have actually said is that your column finishes with your delimiter. The flat file source then looks for that delimiter to end the column. The CRLF is not defined as the record delimiter but the delimiter of the last column.
I know that doesn't solve the problem but should explain the reason for the situation.
I still stand by the fact that if you want this functionality using the script component is a valid solution.
|||Last night I uploaded a custom source adapter to SourceForge that would let you parse this file with regular expressions.
http://sourceforge.net/projects/textregexsource
Just connect a file connection manager to it and set a regular expression, and it could produce what you want.
I think a regex like (?'Col1'\w+),*(?'Col2'\w*),*(?'Col3'\w*),*(?'Col4'\w*),*(?'Col5'\w*)\n
might do the trick, though it might need tweaking.
Geof
Flat File and uneven number of columns
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493
Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.
For example: (assume CR LF row delimiter)
The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test
"Normally" I'd expect this
Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.
So we get
I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"
I'm sure it's there.....help!
(By the way SSIS team, great job on the package love using it)
Try using ragged right instead of delimited.
Thanks,
Matt
|||Ah see if only it was that simple Matt. "Ragged right files are files in which every column has a fixed width, except for the last column, which is delimited by the row delimiter."
So the only thing which can be Ragged in fact is the last column. In my example I've purposely put varying column widths so the old "Ragged Right" doesn’t apply (this is more the norm for ragged files).
So any ideas how this is going to work in SSIS...I must be missing something simple here this is a very common problem that the old DTS, Excel, Access, and other programs deal with quite well.
help!
|||Unfortunately we only support ragged right for fixed width so for this scenario you would have to read it as a single column and then split it using a script component or you could write a custom component that split it.
Matt
|||but this is a bug or changed behavior from the old sql server 2000 dts which when you had a comma or tab deliminited file some of the rows had extra columns after the last valid one.. it ignored those extra columns.
the new functionality is to instead append the commas/delimiters into the data pulled in from the last column..
This is causing us major grief becuase now none of our dts's work in the new sql server 2005 so it is not backwards compatible..
|||
This is similar to an issure raised the other day. I produced a sample package on how to handle this.
http://sqlblogcasts.com/files/4/integration_services/entry412.aspx
|||I'm with you Igkahn. It's a bug. No way is this a feature! This is killing us too. If I have to script this as set out below then something fundimentally is wrong with SSIS. I cut one of our large SQL 2000 servers over to SQL 2005 and we are stopping there until fixes are supplied for errors like this. I'm now having to use the SQL 2000 DTS instead of the nice looking however functionality poor SSIS.
Please get this fixed.
Garry Swan
Information Systems Manager
CSIRO
Australia
If you feel strongly about it then you should raise it through the Feedback centre and through your MS representative if you have one.
|||
Calling it a bug implies that is supposed to do something else and I'm not sure that is the case. I'm not denying that it MIGHT be a bug - hopefully Matt will reply again and tell us (By the way, just because some behaviour was present in DTS, you shouldn't assume the same would be true of SSIS. This is a replacement, not an upgrade).
Regardless, I can understand why this is causing headache. Have you logged it at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx)? If not, then you shouldn't expect that the feature will make it into the next version.
I don't understand why you call SSIS functionally poor. As Simon explained this can still be achieved quite easily. Surely the fact that there is "more than one way to skin a cat" so to speak means the product is functionally rich as opposed to poor? Is the fact that something cannot be achieved using your "preferred" method really prohibiting you from moving to a superior product?
-Jamie
|||From my research there has been a lot of posts about this issue in the last couple of months. But im still looking for a proper solution, is there a fix available? Is there a code sample available for a SSIS source component or whatnot, that threats uneven columns properly?
I've looked through the sample for the source component and could not get it to run (something about no compatible component in the dll)
Since people do need to import data from delimited files very often, SSIS should be expending the functionality of what there was in the DTS world... but in this case it was decided to limit it. Whoever came up with the delimiter for each column must have been so proud of himself that he generlized and removed the need for a row delimiter....
Well you haven't, and we want it back. :) It's causing many people, a lot of greef.
Or at least release the source for a flat file handler which people can customize to their liking.
There is so many flat file formats out there.... Why be so restrictive when dealing with them?
|||Whats wrong with the sample I have provided.
SSIS focus was on building a framework that was performant, scalable and extensible. For this reason the components out of the box don't satisfy everyones requirements. But I have shown how it is very easy to extend SSIS with a script component to achieve your goal.
If you want a more polished solution you can develop your own custom component but that is another level of complexity that really isn't needed due to the power of the script component.
|||Someone mentioned that the code provided by Sabin didn't work for him/her. I have not tried that code.
Here is a version (another way to skin the cat) that can be tried. Please note that your first row should contain all the column names for this to work. This script assumes that the input is configured in a way that all data on one row constitute a column. So basically, there is just one column in each row. The output of this script is also one column in each row. However, the output created by this script can be "understood" by SSIS file connection manager. Finally, assumption is that "tab" is column delimiter. You can change that below, if that is not the case.
Private dataRow As Boolean = False
Public Function AppendMissingTabs(ByVal Row As InputBuffer) As String
Dim columns As String() = Nothing
Dim outputRow As String = Nothing
Dim outputString As String = Nothing
Dim buffer As StringBuilder = New StringBuilder()
buffer.Append(Row.BigSingleColumn)
columns = Row.BigSingleColumn.Split(New [Char]() {Chr(9)})
If Not dataRow Then
columnCount = columns.Length
End If
Dim N As Integer
If columns.Length < columnCount Then
For N = 1 To columnCount - columns.Length Step 1
buffer.Append(Chr(9))
Next N
End If
dataRow = True
Return buffer.ToString()
End Function
|||
It comes down to if I tell a system that my row finishes with a crlf I don't want it to override my decision and pull the crlf into the data. Why would I want the row delimiter in the data?!? Is this a feature people needed and the SSIS team decided the former was so last week that this new functionality was, in fact, a better solution path...I just doubt it. The required functionality is the way Excel, DTS, Access and many other apps handle this data import. Why is SSIS pulling crlf into my data when I've said this is the end of the row?!?. Just stop and move onto the next row. Put that functionality back in and you've got a great delimited file importer.
Garry Swan
MCDBA
|||What you have actually said is that your column finishes with your delimiter. The flat file source then looks for that delimiter to end the column. The CRLF is not defined as the record delimiter but the delimiter of the last column.
I know that doesn't solve the problem but should explain the reason for the situation.
I still stand by the fact that if you want this functionality using the script component is a valid solution.
|||Last night I uploaded a custom source adapter to SourceForge that would let you parse this file with regular expressions.
http://sourceforge.net/projects/textregexsource
Just connect a file connection manager to it and set a regular expression, and it could produce what you want.
I think a regex like (?'Col1'\w+),*(?'Col2'\w*),*(?'Col3'\w*),*(?'Col4'\w*),*(?'Col5'\w*)\n
might do the trick, though it might need tweaking.
Geof
sqlFlat File and uneven number of columns
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493
Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.
For example: (assume CR LF row delimiter)
The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test
"Normally" I'd expect this
Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.
So we get
I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"
I'm sure it's there.....help!
(By the way SSIS team, great job on the package love using it)
Try using ragged right instead of delimited.
Thanks,
Matt
|||Ah see if only it was that simple Matt. "Ragged right files are files in which every column has a fixed width, except for the last column, which is delimited by the row delimiter."
So the only thing which can be Ragged in fact is the last column. In my example I've purposely put varying column widths so the old "Ragged Right" doesn’t apply (this is more the norm for ragged files).
So any ideas how this is going to work in SSIS...I must be missing something simple here this is a very common problem that the old DTS, Excel, Access, and other programs deal with quite well.
help!
|||Unfortunately we only support ragged right for fixed width so for this scenario you would have to read it as a single column and then split it using a script component or you could write a custom component that split it.
Matt
|||but this is a bug or changed behavior from the old sql server 2000 dts which when you had a comma or tab deliminited file some of the rows had extra columns after the last valid one.. it ignored those extra columns.
the new functionality is to instead append the commas/delimiters into the data pulled in from the last column..
This is causing us major grief becuase now none of our dts's work in the new sql server 2005 so it is not backwards compatible..
|||
This is similar to an issure raised the other day. I produced a sample package on how to handle this.
http://sqlblogcasts.com/files/4/integration_services/entry412.aspx
|||I'm with you Igkahn. It's a bug. No way is this a feature! This is killing us too. If I have to script this as set out below then something fundimentally is wrong with SSIS. I cut one of our large SQL 2000 servers over to SQL 2005 and we are stopping there until fixes are supplied for errors like this. I'm now having to use the SQL 2000 DTS instead of the nice looking however functionality poor SSIS.
Please get this fixed.
Garry Swan
Information Systems Manager
CSIRO
Australia
If you feel strongly about it then you should raise it through the Feedback centre and through your MS representative if you have one.
|||
Calling it a bug implies that is supposed to do something else and I'm not sure that is the case. I'm not denying that it MIGHT be a bug - hopefully Matt will reply again and tell us (By the way, just because some behaviour was present in DTS, you shouldn't assume the same would be true of SSIS. This is a replacement, not an upgrade).
Regardless, I can understand why this is causing headache. Have you logged it at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx)? If not, then you shouldn't expect that the feature will make it into the next version.
I don't understand why you call SSIS functionally poor. As Simon explained this can still be achieved quite easily. Surely the fact that there is "more than one way to skin a cat" so to speak means the product is functionally rich as opposed to poor? Is the fact that something cannot be achieved using your "preferred" method really prohibiting you from moving to a superior product?
-Jamie
|||From my research there has been a lot of posts about this issue in the last couple of months. But im still looking for a proper solution, is there a fix available? Is there a code sample available for a SSIS source component or whatnot, that threats uneven columns properly?
I've looked through the sample for the source component and could not get it to run (something about no compatible component in the dll)
Since people do need to import data from delimited files very often, SSIS should be expending the functionality of what there was in the DTS world... but in this case it was decided to limit it. Whoever came up with the delimiter for each column must have been so proud of himself that he generlized and removed the need for a row delimiter....
Well you haven't, and we want it back. :) It's causing many people, a lot of greef.
Or at least release the source for a flat file handler which people can customize to their liking.
There is so many flat file formats out there.... Why be so restrictive when dealing with them?
|||Whats wrong with the sample I have provided.
SSIS focus was on building a framework that was performant, scalable and extensible. For this reason the components out of the box don't satisfy everyones requirements. But I have shown how it is very easy to extend SSIS with a script component to achieve your goal.
If you want a more polished solution you can develop your own custom component but that is another level of complexity that really isn't needed due to the power of the script component.
|||Someone mentioned that the code provided by Sabin didn't work for him/her. I have not tried that code.
Here is a version (another way to skin the cat) that can be tried. Please note that your first row should contain all the column names for this to work. This script assumes that the input is configured in a way that all data on one row constitute a column. So basically, there is just one column in each row. The output of this script is also one column in each row. However, the output created by this script can be "understood" by SSIS file connection manager. Finally, assumption is that "tab" is column delimiter. You can change that below, if that is not the case.
Private dataRow As Boolean = False
Public Function AppendMissingTabs(ByVal Row As InputBuffer) As String
Dim columns As String() = Nothing
Dim outputRow As String = Nothing
Dim outputString As String = Nothing
Dim buffer As StringBuilder = New StringBuilder()
buffer.Append(Row.BigSingleColumn)
columns = Row.BigSingleColumn.Split(New [Char]() {Chr(9)})
If Not dataRow Then
columnCount = columns.Length
End If
Dim N As Integer
If columns.Length < columnCount Then
For N = 1 To columnCount - columns.Length Step 1
buffer.Append(Chr(9))
Next N
End If
dataRow = True
Return buffer.ToString()
End Function
|||
It comes down to if I tell a system that my row finishes with a crlf I don't want it to override my decision and pull the crlf into the data. Why would I want the row delimiter in the data?!? Is this a feature people needed and the SSIS team decided the former was so last week that this new functionality was, in fact, a better solution path...I just doubt it. The required functionality is the way Excel, DTS, Access and many other apps handle this data import. Why is SSIS pulling crlf into my data when I've said this is the end of the row?!?. Just stop and move onto the next row. Put that functionality back in and you've got a great delimited file importer.
Garry Swan
MCDBA
|||What you have actually said is that your column finishes with your delimiter. The flat file source then looks for that delimiter to end the column. The CRLF is not defined as the record delimiter but the delimiter of the last column.
I know that doesn't solve the problem but should explain the reason for the situation.
I still stand by the fact that if you want this functionality using the script component is a valid solution.
|||Last night I uploaded a custom source adapter to SourceForge that would let you parse this file with regular expressions.
http://sourceforge.net/projects/textregexsource
Just connect a file connection manager to it and set a regular expression, and it could produce what you want.
I think a regex like (?'Col1'\w+),*(?'Col2'\w*),*(?'Col3'\w*),*(?'Col4'\w*),*(?'Col5'\w*)\n
might do the trick, though it might need tweaking.
Geof
Flat File and uneven number of columns
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493
Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.
For example: (assume CR LF row delimiter)
The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test
"Normally" I'd expect this
Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.
So we get
I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"
I'm sure it's there.....help!
(By the way SSIS team, great job on the package love using it)
Try using ragged right instead of delimited.
Thanks,
Matt
|||Ah see if only it was that simple Matt. "Ragged right files are files in which every column has a fixed width, except for the last column, which is delimited by the row delimiter."
So the only thing which can be Ragged in fact is the last column. In my example I've purposely put varying column widths so the old "Ragged Right" doesn’t apply (this is more the norm for ragged files).
So any ideas how this is going to work in SSIS...I must be missing something simple here this is a very common problem that the old DTS, Excel, Access, and other programs deal with quite well.
help!
|||Unfortunately we only support ragged right for fixed width so for this scenario you would have to read it as a single column and then split it using a script component or you could write a custom component that split it.
Matt
|||but this is a bug or changed behavior from the old sql server 2000 dts which when you had a comma or tab deliminited file some of the rows had extra columns after the last valid one.. it ignored those extra columns.
the new functionality is to instead append the commas/delimiters into the data pulled in from the last column..
This is causing us major grief becuase now none of our dts's work in the new sql server 2005 so it is not backwards compatible..
|||
This is similar to an issure raised the other day. I produced a sample package on how to handle this.
http://sqlblogcasts.com/files/4/integration_services/entry412.aspx
|||I'm with you Igkahn. It's a bug. No way is this a feature! This is killing us too. If I have to script this as set out below then something fundimentally is wrong with SSIS. I cut one of our large SQL 2000 servers over to SQL 2005 and we are stopping there until fixes are supplied for errors like this. I'm now having to use the SQL 2000 DTS instead of the nice looking however functionality poor SSIS.
Please get this fixed.
Garry Swan
Information Systems Manager
CSIRO
Australia
If you feel strongly about it then you should raise it through the Feedback centre and through your MS representative if you have one.
|||
Calling it a bug implies that is supposed to do something else and I'm not sure that is the case. I'm not denying that it MIGHT be a bug - hopefully Matt will reply again and tell us (By the way, just because some behaviour was present in DTS, you shouldn't assume the same would be true of SSIS. This is a replacement, not an upgrade).
Regardless, I can understand why this is causing headache. Have you logged it at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx)? If not, then you shouldn't expect that the feature will make it into the next version.
I don't understand why you call SSIS functionally poor. As Simon explained this can still be achieved quite easily. Surely the fact that there is "more than one way to skin a cat" so to speak means the product is functionally rich as opposed to poor? Is the fact that something cannot be achieved using your "preferred" method really prohibiting you from moving to a superior product?
-Jamie
|||From my research there has been a lot of posts about this issue in the last couple of months. But im still looking for a proper solution, is there a fix available? Is there a code sample available for a SSIS source component or whatnot, that threats uneven columns properly?
I've looked through the sample for the source component and could not get it to run (something about no compatible component in the dll)
Since people do need to import data from delimited files very often, SSIS should be expending the functionality of what there was in the DTS world... but in this case it was decided to limit it. Whoever came up with the delimiter for each column must have been so proud of himself that he generlized and removed the need for a row delimiter....
Well you haven't, and we want it back. :) It's causing many people, a lot of greef.
Or at least release the source for a flat file handler which people can customize to their liking.
There is so many flat file formats out there.... Why be so restrictive when dealing with them?
|||Whats wrong with the sample I have provided.
SSIS focus was on building a framework that was performant, scalable and extensible. For this reason the components out of the box don't satisfy everyones requirements. But I have shown how it is very easy to extend SSIS with a script component to achieve your goal.
If you want a more polished solution you can develop your own custom component but that is another level of complexity that really isn't needed due to the power of the script component.
|||Someone mentioned that the code provided by Sabin didn't work for him/her. I have not tried that code.
Here is a version (another way to skin the cat) that can be tried. Please note that your first row should contain all the column names for this to work. This script assumes that the input is configured in a way that all data on one row constitute a column. So basically, there is just one column in each row. The output of this script is also one column in each row. However, the output created by this script can be "understood" by SSIS file connection manager. Finally, assumption is that "tab" is column delimiter. You can change that below, if that is not the case.
Private dataRow As Boolean = False
Public Function AppendMissingTabs(ByVal Row As InputBuffer) As String
Dim columns As String() = Nothing
Dim outputRow As String = Nothing
Dim outputString As String = Nothing
Dim buffer As StringBuilder = New StringBuilder()
buffer.Append(Row.BigSingleColumn)
columns = Row.BigSingleColumn.Split(New [Char]() {Chr(9)})
If Not dataRow Then
columnCount = columns.Length
End If
Dim N As Integer
If columns.Length < columnCount Then
For N = 1 To columnCount - columns.Length Step 1
buffer.Append(Chr(9))
Next N
End If
dataRow = True
Return buffer.ToString()
End Function
|||
It comes down to if I tell a system that my row finishes with a crlf I don't want it to override my decision and pull the crlf into the data. Why would I want the row delimiter in the data?!? Is this a feature people needed and the SSIS team decided the former was so last week that this new functionality was, in fact, a better solution path...I just doubt it. The required functionality is the way Excel, DTS, Access and many other apps handle this data import. Why is SSIS pulling crlf into my data when I've said this is the end of the row?!?. Just stop and move onto the next row. Put that functionality back in and you've got a great delimited file importer.
Garry Swan
MCDBA
|||What you have actually said is that your column finishes with your delimiter. The flat file source then looks for that delimiter to end the column. The CRLF is not defined as the record delimiter but the delimiter of the last column.
I know that doesn't solve the problem but should explain the reason for the situation.
I still stand by the fact that if you want this functionality using the script component is a valid solution.
|||Last night I uploaded a custom source adapter to SourceForge that would let you parse this file with regular expressions.
http://sourceforge.net/projects/textregexsource
Just connect a file connection manager to it and set a regular expression, and it could produce what you want.
I think a regex like (?'Col1'\w+),*(?'Col2'\w*),*(?'Col3'\w*),*(?'Col4'\w*),*(?'Col5'\w*)\n
might do the trick, though it might need tweaking.
Geof
Flat File and uneven number of columns
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493
Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.
For example: (assume CR LF row delimiter)
The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test
"Normally" I'd expect this
Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.
So we get
I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"
I'm sure it's there.....help!
(By the way SSIS team, great job on the package love using it)
Try using ragged right instead of delimited.
Thanks,
Matt
|||Ah see if only it was that simple Matt. "Ragged right files are files in which every column has a fixed width, except for the last column, which is delimited by the row delimiter."
So the only thing which can be Ragged in fact is the last column. In my example I've purposely put varying column widths so the old "Ragged Right" doesn’t apply (this is more the norm for ragged files).
So any ideas how this is going to work in SSIS...I must be missing something simple here this is a very common problem that the old DTS, Excel, Access, and other programs deal with quite well.
help!
|||Unfortunately we only support ragged right for fixed width so for this scenario you would have to read it as a single column and then split it using a script component or you could write a custom component that split it.
Matt
|||but this is a bug or changed behavior from the old sql server 2000 dts which when you had a comma or tab deliminited file some of the rows had extra columns after the last valid one.. it ignored those extra columns.
the new functionality is to instead append the commas/delimiters into the data pulled in from the last column..
This is causing us major grief becuase now none of our dts's work in the new sql server 2005 so it is not backwards compatible..
|||This is similar to an issure raised the other day. I produced a sample package on how to handle this.
http://sqlblogcasts.com/files/4/integration_services/entry412.aspx
|||I'm with you Igkahn. It's a bug. No way is this a feature! This is killing us too. If I have to script this as set out below then something fundimentally is wrong with SSIS. I cut one of our large SQL 2000 servers over to SQL 2005 and we are stopping there until fixes are supplied for errors like this. I'm now having to use the SQL 2000 DTS instead of the nice looking however functionality poor SSIS.
Please get this fixed.
Garry Swan
Information Systems Manager
CSIRO
Australia
If you feel strongly about it then you should raise it through the Feedback centre and through your MS representative if you have one.
|||Calling it a bug implies that is supposed to do something else and I'm not sure that is the case. I'm not denying that it MIGHT be a bug - hopefully Matt will reply again and tell us (By the way, just because some behaviour was present in DTS, you shouldn't assume the same would be true of SSIS. This is a replacement, not an upgrade).
Regardless, I can understand why this is causing headache. Have you logged it at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx)? If not, then you shouldn't expect that the feature will make it into the next version.
I don't understand why you call SSIS functionally poor. As Simon explained this can still be achieved quite easily. Surely the fact that there is "more than one way to skin a cat" so to speak means the product is functionally rich as opposed to poor? Is the fact that something cannot be achieved using your "preferred" method really prohibiting you from moving to a superior product?
-Jamie
|||From my research there has been a lot of posts about this issue in the last couple of months. But im still looking for a proper solution, is there a fix available? Is there a code sample available for a SSIS source component or whatnot, that threats uneven columns properly?
I've looked through the sample for the source component and could not get it to run (something about no compatible component in the dll)
Since people do need to import data from delimited files very often, SSIS should be expending the functionality of what there was in the DTS world... but in this case it was decided to limit it. Whoever came up with the delimiter for each column must have been so proud of himself that he generlized and removed the need for a row delimiter....
Well you haven't, and we want it back. :) It's causing many people, a lot of greef.
Or at least release the source for a flat file handler which people can customize to their liking.
There is so many flat file formats out there.... Why be so restrictive when dealing with them?
|||Whats wrong with the sample I have provided.
SSIS focus was on building a framework that was performant, scalable and extensible. For this reason the components out of the box don't satisfy everyones requirements. But I have shown how it is very easy to extend SSIS with a script component to achieve your goal.
If you want a more polished solution you can develop your own custom component but that is another level of complexity that really isn't needed due to the power of the script component.
|||Someone mentioned that the code provided by Sabin didn't work for him/her. I have not tried that code.
Here is a version (another way to skin the cat) that can be tried. Please note that your first row should contain all the column names for this to work. This script assumes that the input is configured in a way that all data on one row constitute a column. So basically, there is just one column in each row. The output of this script is also one column in each row. However, the output created by this script can be "understood" by SSIS file connection manager. Finally, assumption is that "tab" is column delimiter. You can change that below, if that is not the case.
Private dataRow As Boolean = False
Public Function AppendMissingTabs(ByVal Row As InputBuffer) As String
Dim columns As String() = Nothing
Dim outputRow As String = Nothing
Dim outputString As String = Nothing
Dim buffer As StringBuilder = New StringBuilder()
buffer.Append(Row.BigSingleColumn)
columns = Row.BigSingleColumn.Split(New [Char]() {Chr(9)})
If Not dataRow Then
columnCount = columns.Length
End If
Dim N As Integer
If columns.Length < columnCount Then
For N = 1 To columnCount - columns.Length Step 1
buffer.Append(Chr(9))
Next N
End If
dataRow = True
Return buffer.ToString()
End Function
|||It comes down to if I tell a system that my row finishes with a crlf I don't want it to override my decision and pull the crlf into the data. Why would I want the row delimiter in the data?!? Is this a feature people needed and the SSIS team decided the former was so last week that this new functionality was, in fact, a better solution path...I just doubt it. The required functionality is the way Excel, DTS, Access and many other apps handle this data import. Why is SSIS pulling crlf into my data when I've said this is the end of the row?!?. Just stop and move onto the next row. Put that functionality back in and you've got a great delimited file importer.
Garry Swan
MCDBA
|||What you have actually said is that your column finishes with your delimiter. The flat file source then looks for that delimiter to end the column. The CRLF is not defined as the record delimiter but the delimiter of the last column.
I know that doesn't solve the problem but should explain the reason for the situation.
I still stand by the fact that if you want this functionality using the script component is a valid solution.
|||Last night I uploaded a custom source adapter to SourceForge that would let you parse this file with regular expressions.
http://sourceforge.net/projects/textregexsource
Just connect a file connection manager to it and set a regular expression, and it could produce what you want.
I think a regex like (?'Col1'\w+),*(?'Col2'\w*),*(?'Col3'\w*),*(?'Col4'\w*),*(?'Col5'\w*)\n
might do the trick, though it might need tweaking.
Geof
Flat File and uneven number of columns
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493
Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.
For example: (assume CR LF row delimiter)
The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test
"Normally" I'd expect this
Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.
So we get
I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"
I'm sure it's there.....help!
(By the way SSIS team, great job on the package love using it)
Try using ragged right instead of delimited.
Thanks,
Matt
|||Ah see if only it was that simple Matt. "Ragged right files are files in which every column has a fixed width, except for the last column, which is delimited by the row delimiter."
So the only thing which can be Ragged in fact is the last column. In my example I've purposely put varying column widths so the old "Ragged Right" doesn’t apply (this is more the norm for ragged files).
So any ideas how this is going to work in SSIS...I must be missing something simple here this is a very common problem that the old DTS, Excel, Access, and other programs deal with quite well.
help!
|||Unfortunately we only support ragged right for fixed width so for this scenario you would have to read it as a single column and then split it using a script component or you could write a custom component that split it.
Matt
|||but this is a bug or changed behavior from the old sql server 2000 dts which when you had a comma or tab deliminited file some of the rows had extra columns after the last valid one.. it ignored those extra columns.
the new functionality is to instead append the commas/delimiters into the data pulled in from the last column..
This is causing us major grief becuase now none of our dts's work in the new sql server 2005 so it is not backwards compatible..
|||
This is similar to an issure raised the other day. I produced a sample package on how to handle this.
http://sqlblogcasts.com/files/4/integration_services/entry412.aspx
|||I'm with you Igkahn. It's a bug. No way is this a feature! This is killing us too. If I have to script this as set out below then something fundimentally is wrong with SSIS. I cut one of our large SQL 2000 servers over to SQL 2005 and we are stopping there until fixes are supplied for errors like this. I'm now having to use the SQL 2000 DTS instead of the nice looking however functionality poor SSIS.
Please get this fixed.
Garry Swan
Information Systems Manager
CSIRO
Australia
If you feel strongly about it then you should raise it through the Feedback centre and through your MS representative if you have one.
|||
Calling it a bug implies that is supposed to do something else and I'm not sure that is the case. I'm not denying that it MIGHT be a bug - hopefully Matt will reply again and tell us (By the way, just because some behaviour was present in DTS, you shouldn't assume the same would be true of SSIS. This is a replacement, not an upgrade).
Regardless, I can understand why this is causing headache. Have you logged it at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx)? If not, then you shouldn't expect that the feature will make it into the next version.
I don't understand why you call SSIS functionally poor. As Simon explained this can still be achieved quite easily. Surely the fact that there is "more than one way to skin a cat" so to speak means the product is functionally rich as opposed to poor? Is the fact that something cannot be achieved using your "preferred" method really prohibiting you from moving to a superior product?
-Jamie
|||From my research there has been a lot of posts about this issue in the last couple of months. But im still looking for a proper solution, is there a fix available? Is there a code sample available for a SSIS source component or whatnot, that threats uneven columns properly?
I've looked through the sample for the source component and could not get it to run (something about no compatible component in the dll)
Since people do need to import data from delimited files very often, SSIS should be expending the functionality of what there was in the DTS world... but in this case it was decided to limit it. Whoever came up with the delimiter for each column must have been so proud of himself that he generlized and removed the need for a row delimiter....
Well you haven't, and we want it back. :) It's causing many people, a lot of greef.
Or at least release the source for a flat file handler which people can customize to their liking.
There is so many flat file formats out there.... Why be so restrictive when dealing with them?
|||Whats wrong with the sample I have provided.
SSIS focus was on building a framework that was performant, scalable and extensible. For this reason the components out of the box don't satisfy everyones requirements. But I have shown how it is very easy to extend SSIS with a script component to achieve your goal.
If you want a more polished solution you can develop your own custom component but that is another level of complexity that really isn't needed due to the power of the script component.
|||Someone mentioned that the code provided by Sabin didn't work for him/her. I have not tried that code.
Here is a version (another way to skin the cat) that can be tried. Please note that your first row should contain all the column names for this to work. This script assumes that the input is configured in a way that all data on one row constitute a column. So basically, there is just one column in each row. The output of this script is also one column in each row. However, the output created by this script can be "understood" by SSIS file connection manager. Finally, assumption is that "tab" is column delimiter. You can change that below, if that is not the case.
Private dataRow As Boolean = False
Public Function AppendMissingTabs(ByVal Row As InputBuffer) As String
Dim columns As String() = Nothing
Dim outputRow As String = Nothing
Dim outputString As String = Nothing
Dim buffer As StringBuilder = New StringBuilder()
buffer.Append(Row.BigSingleColumn)
columns = Row.BigSingleColumn.Split(New [Char]() {Chr(9)})
If Not dataRow Then
columnCount = columns.Length
End If
Dim N As Integer
If columns.Length < columnCount Then
For N = 1 To columnCount - columns.Length Step 1
buffer.Append(Chr(9))
Next N
End If
dataRow = True
Return buffer.ToString()
End Function
|||
It comes down to if I tell a system that my row finishes with a crlf I don't want it to override my decision and pull the crlf into the data. Why would I want the row delimiter in the data?!? Is this a feature people needed and the SSIS team decided the former was so last week that this new functionality was, in fact, a better solution path...I just doubt it. The required functionality is the way Excel, DTS, Access and many other apps handle this data import. Why is SSIS pulling crlf into my data when I've said this is the end of the row?!?. Just stop and move onto the next row. Put that functionality back in and you've got a great delimited file importer.
Garry Swan
MCDBA
|||What you have actually said is that your column finishes with your delimiter. The flat file source then looks for that delimiter to end the column. The CRLF is not defined as the record delimiter but the delimiter of the last column.
I know that doesn't solve the problem but should explain the reason for the situation.
I still stand by the fact that if you want this functionality using the script component is a valid solution.
|||Last night I uploaded a custom source adapter to SourceForge that would let you parse this file with regular expressions.
http://sourceforge.net/projects/textregexsource
Just connect a file connection manager to it and set a regular expression, and it could produce what you want.
I think a regex like (?'Col1'\w+),*(?'Col2'\w*),*(?'Col3'\w*),*(?'Col4'\w*),*(?'Col5'\w*)\n
might do the trick, though it might need tweaking.
Geof
订阅：
博文 (Atom)