Replacing NULL characters from within a flat file with script?

Here's one for the group:

I have a fixed-width flat file. Within some of the rows, I have embedded NULL characters. The inherit problem is that NULL characters are string terminators, so using a flat file source doesn't allow the capturing of these NULL characters or any characters after the first NULL character -- only the string up to the NULL character.

So, within SSIS, what would be the best way to replace NULL characters with a SPACE character? My file is fixed-width, and replacing with a space will allow me to keep the length the same. I am not opposed to running a script task against the file first (before using my flat file source), but would need some guidance as I'm not a .Net guru, by any means.

Unfortunately, going to the bank to have them correct this file has proved fruitless. We're going to have to deal with these characters on our side.

Thanks,
Phil

Just curious, what does a null character look like in a flat file? I've encountered parsing problems in some basic BAI bank files that were comma delimited. Basically, I couldn't bulk insert or bcp the data in through SQL scripting, but had to use ActiveX to import the data or the SQL 2000 (probably 2005 also) DTS Text Connection Object (because somehow DTS ignored the problem characters and found the end-of-line return like it is supposed to). The basic run-down on using ADO/ActiveX can be found here: http://msdn2.microsoft.com/en-us/library/ms974559.aspx.

Anyway, if you come across another solution please let me know.

Good luck!

|||A NULL character is a hex 00.|||Bump|||

Hi Phil,

I would write a tiny C program to do the conversion and then call it from the SSIS package.

BTW, what is the semantic of these embedded nulls in string fields, are they some kind of dividers?

Thanks,

Bob

sql

Replacing columnn name programmatically

Hi,

The following script does not return any resultset against a test db
while I know for a fact tables with letter "aaa" has columns that
contains "ccc".
What's wrong? the the inner cursor?

Thanks.

-- get all tbls with letter aaa
declare @.tbl varchar(8000)
declare tblCursor cursor for
SELECT name
FROM sysobjects
WHERE xtype = 'U'
AND name LIKE '%aaa%'

open tblCursor
fetch next from tblCursor
into @.tbl

while (@.@.fetch_status = 0)
begin

-- get all columns with letter ccc and replace it with nothing /
remove it
declare @.tbuffer varchar(4000)
declare @.cbuffer varchar(8000)

declare abnormal_cols cursor for
SELECT o.name, c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
and o.id = object_id('+@.tbl')
-- ORDER BY c.name

open abnormal_cols
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer

while (@.@.fetch_status = 0)
begin
-- EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
'COLUMN';
-- test
print @.tbuffer + ', ' + @.cbuffer;
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer
end

close abnormal_cols
deallocate abnormal_cols;

fetch next from tblCursor
into @.tbl

end
close tblCursor
deallocate tblCursor;Hi

I can't see why there are two cursors here, try:

SELECT o.name, Replace(c.name,'ccc','') as NewName, c.name as OldName
FROM sysobjects o JOIN syscolumns c ON o.id = c.id
JOIN syscolumns a ON o.id = a.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
AND a.name LIKE '%aaa%'

John

"Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
news:fc254714.0310211451.2f59f9c4@.posting.google.c om...
> Hi,
> The following script does not return any resultset against a test db
> while I know for a fact tables with letter "aaa" has columns that
> contains "ccc".
> What's wrong? the the inner cursor?
> Thanks.
>
> -- get all tbls with letter aaa
> declare @.tbl varchar(8000)
> declare tblCursor cursor for
> SELECT name
> FROM sysobjects
> WHERE xtype = 'U'
> AND name LIKE '%aaa%'
> open tblCursor
> fetch next from tblCursor
> into @.tbl
> while (@.@.fetch_status = 0)
> begin
> -- get all columns with letter ccc and replace it with nothing /
> remove it
> declare @.tbuffer varchar(4000)
> declare @.cbuffer varchar(8000)
> declare abnormal_cols cursor for
> SELECT o.name, c.name
> FROM sysobjects o
> JOIN syscolumns c ON o.id = c.id
> WHERE o.xtype = 'U'
> AND c.name LIKE '%ccc%'
> and o.id = object_id('+@.tbl')
> -- ORDER BY c.name
> open abnormal_cols
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> while (@.@.fetch_status = 0)
> begin
> -- EXEC sp_rename
'+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
> 'COLUMN';
> -- test
> print @.tbuffer + ', ' + @.cbuffer;
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> end
> close abnormal_cols
> deallocate abnormal_cols;
> fetch next from tblCursor
> into @.tbl
> end
> close tblCursor
> deallocate tblCursor;

Replacing characters in SQL

I am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash \ I know to replace it with a double backslash \\. But what if it is a " double quote what do I need to do to get it to appear as is?
ThanksYou'll love this answer: It depends.

Based on your previous message, you might have chosen to change the settings of QUOTED_IDENTIFIER. If you have changed it, then from Perl there is no way to pass a true double quote anymore. If you have not changed it, you don't need to do anything at all to a double quote.

My suggestion would be to leave the quoted identifier off, then use a simple s/'/''/gto fix up any apostrophes in the strings comming from Perl to SQL Server.

-PatP|||Thanks for you help.

-Laurasql

replacing all the ' in a table

Hi all

I need to remove all the ' in a table and i am having some problems with the script, any help would be great

Thanks

Richard

use the following replace statement,

Update tableName

set

ColumnName = Replace(Columnname,'''','')

Where

Columnname Like '%''%'

sql

Replacing Active X/VBscript used in SQL2000 data transformations to SQL2005 SSIS

Hi

I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.

Function Main()

Dim x

For x=1 to DTSSource.count
If Isnull(DTSSource(x)) = False Then

DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")

Else

DTSDestination(x) = DTSSource(x)

End If

Next

Main = DTSTransformStat_OK

End Function

Andre

SSIS does not support ActiveX script transforms. You have the Script Component that gives you full VB.NET, so you coudl simply convert the code for that if you wanted. In this case I woudl strongly recommend you use teh Derived Column transform. This uses the SSIS specific expression syntax, which includes a REPLACE function and a conditional operation, and ISNULL, so you can do what you require.

To specify the non-printable characters you can use the unicode character code, \x005C for a \ character.

Escaping In Expressions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EscapingInExpressions.html)

|||

Hi

Thanks for the answer, problem though I do not want to use derived columns as I have multiple extracts per client, currently if i add up each individual extract I am sitting at 300+ with the figure increasing monthly. I cannot do this by field by extract. I need something generic which will look at each field in an extract and then take out all char(10), char(13), commas and char(0) before writing the results to a flat file.

Your help with code would be much appreciated

Andre

sql

Replacing Active X/VBscript used in SQL2000 data transformations to SQL2005 SSIS

Hi

I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.

Function Main()

Dim x

For x=1 to DTSSource.count
If Isnull(DTSSource(x)) = False Then

DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")

Else

DTSDestination(x) = DTSSource(x)

End If

Next

Main = DTSTransformStat_OK

End Function

Andre

SSIS does not support ActiveX script transforms. You have the Script Component that gives you full VB.NET, so you coudl simply convert the code for that if you wanted. In this case I woudl strongly recommend you use teh Derived Column transform. This uses the SSIS specific expression syntax, which includes a REPLACE function and a conditional operation, and ISNULL, so you can do what you require.

To specify the non-printable characters you can use the unicode character code, \x005C for a \ character.

Escaping In Expressions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EscapingInExpressions.html)

|||

Hi

Thanks for the answer, problem though I do not want to use derived columns as I have multiple extracts per client, currently if i add up each individual extract I am sitting at 300+ with the figure increasing monthly. I cannot do this by field by extract. I need something generic which will look at each field in an extract and then take out all char(10), char(13), commas and char(0) before writing the results to a flat file.

Your help with code would be much appreciated

Andre

Repetitive SQL Tasks

Thanks in advance.

I am new to sql script area.

Below is my ugly program.

Help, please)

There should be a better code to handle these repetitive tasks.

/* DECLARE Part Omitted */

SET @.Job_Code_A = '(Job_Type = ''A'')';

SET @.Job_Code_B = '(Job_Type = ''B'')';

.... And so on

SET @.SQL1 = 'Select Count(*) FROM tblWorkorders WHERE' + @.Job_Code_A;

SET @.SQL2 = 'Select Count(*) FROM tblWorkorders WHERE' + @.Job_Code_B;

..... And so on

EXEC (@.SQL1);

EXEC (@.SQL2);

..... And so on

Why do you need dynamic SQL? You can do:

select o.Job_Type, count(*)

from tblWorkOrders as o

where o.Job_Type in ('A', 'B')

group by o.Job_Type;

|||

Thank you for reply.

I need to use three-level depth loops from outer conditions.

Repeatable error, with script, with code

Hello

I have spent the last weeks trying to solve this problem but with no
luck at all, I have a piece of code that looks like this

declare@.bestnr int,
@.artnr varchar(25),
@.journalnrrow int,
@.bestlevant decimal,
@.vb_inpris money

set @.bestnr = 33434
set @.artnr = '1441'
set @.journalnrrow = 11
set @.bestlevant = 50
set @.vb_inpris = 10

insert into bpl (bestnr, artnr, jibpjournal, bestlevant, vb_inpris,
bestlevantextqty)
values (@.bestnr, @.artnr, @.journalnrrow, @.bestlevant, @.vb_inpris,
@.bestlevant)

This code works just fine in query analyzer, but in SQL server agent
it does not work it creates an error message about string or bnary
data would be truncated, Simon suggested this has to do with
ansi_warnings being OFF in query analyser and ON when SQL srver agent
makes its connection.

I have scripted my entire DB at
http://donald.fruitsalad.org/microsoft/entiredb.sql

That script creates all the deps and tables and triggers for our DB,
any help would be appreciated, I just don't know where to turn, we
have hired a consultant but that did not help either as he could not
solve the problem.

If anyone know a good sql consultant in sweden that might have a clue
about this, please let us know we are getting pretty desperate.

Regards

MattOn 18 Apr 2004 10:42:00 -0700, Matt wrote:

>Hello
>I have spent the last weeks trying to solve this problem but with no
>luck at all, I have a piece of code that looks like this
>
>declare@.bestnr int,
>@.artnr varchar(25),
>@.journalnrrow int,
>@.bestlevant decimal,
>@.vb_inpris money
>set @.bestnr = 33434
>set @.artnr = '1441'
>set @.journalnrrow = 11
>set @.bestlevant = 50
>set @.vb_inpris = 10
>
>insert into bpl (bestnr, artnr, jibpjournal, bestlevant, vb_inpris,
>bestlevantextqty)
>values (@.bestnr, @.artnr, @.journalnrrow, @.bestlevant, @.vb_inpris,
>@.bestlevant)
>This code works just fine in query analyzer, but in SQL server agent
>it does not work it creates an error message about string or bnary
>data would be truncated, Simon suggested this has to do with
>ansi_warnings being OFF in query analyser and ON when SQL srver agent
>makes its connection.

Hi Matt,

To begin with: Simon (Hayes?) is correct. The reason that you see this
different behaviour is due to the settings Simon mentioned. These
settings are provided for historic reasons.

Previous versions of SQL Server had the feature of trimming strings
that didn't fit the declared length of a variable or column without
notice. For SQL Server 7.0, Microsoft wanted to certify as compliant
with the SQL-92 standard. One of the things this standard proscribed
was that trying to store a string in a columns with a shorter length
is an error condition. In order to provide backwards compatibility,
Microsoft supplied option that allow users to select the amount of
ANSI compliancy. The behaviour on string truncation is governed by the
ANSI_WARNINGS setting.

Try the following in Query Analyzer to see what I mean:

drop table test
go
create table test (try# int, shorttext varchar(6))
go
set ansi_warnings off
insert into test select 1, convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
go
set ansi_warnings on
insert into test select 2, convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
go
select * from test
go

>I have scripted my entire DB at
>http://donald.fruitsalad.org/microsoft/entiredb.sql

Boy, am I glad I'm no longer on a dialup connection - almost 100MB!!
When I saw how long the download would take, I assumed you had lots of
inserts embedded in the script, but I now see that it's just the DDL.
You sure make some pretty complex databases... :-|

>That script creates all the deps and tables and triggers for our DB,
>any help would be appreciated, I just don't know where to turn, we
>have hired a consultant but that did not help either as he could not
>solve the problem.

I killed the osql process that executed your script after more than 30
minutes, as I've already found the cause (though it's absolutely
possible that there are more culprits).

This line is taken from the create table statement for bpl:
[perssign] [varchar] (6) COLLATE Finnish_Swedish_CI_AS NOT NULL ,

This column has a default, specified in the (old and proprietary)
Transact-SQL syntax:

create default DefDbLoginId as convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
GO
(...)
EXEC sp_bindefault N'[dbo].[DefDbLoginId]', N'[bpl].[perssign]'
GO

The default results in a varchar(30) value. You attempt to store this
in a varchar(6) column. That's why the error gets raised if the ANSI
warnings option is set to on.

Some ways to get around this:
a) Change the default DefDbLoginId - replace "varchar(30)" with
"varchar(6)" in the convert function call. (ANSI standards does permit
truncation of strings in explicit conversions).
b) Make sure you always run your code with ANSI warnings set to off.
c) Change the maximum length of bpl.perssign to varchar(30).

(*) Note on ANSI (and other) settings: a default for these settings
can be set per database. This default can be overriden for each
connection. Many tools (like Query Analyzer) will do so - the settings
QA usses for each connection can be found (and changed) via menu
option Query / Currenc Connection Properties and the defaults for new
connection via Tools / Options / Connection Properties. Within the
connection, the setting can be flipped as often as wanted with the SET
command (like I did in the example above).

>If anyone know a good sql consultant in sweden that might have a clue
>about this, please let us know we are getting pretty desperate.

In Sweden? No, sorry. I only know SQL consultants in the Netherlands.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)