Deleting lines from a text file

I have a flat file that look like this

############################################################
# Market Issue Lookup
# Applies to: Muni,Pfd,Govt,Corp
###########################################################
LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|

############################################################
# Maturity Type Lookup

and i want it to look like this

LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|

Basically remove any line that start with a "#" or any blank lines..

I am assuming you can do this only using a script component and not directly through ssis..but i am not too familiar with scripting...so some code would be helpful

Thanks for any help in advance.

smathew

You don't need a script component for that. Use a derived colum to trim leading spaces and then a conditional split to discard all rows that start with '#'|||

but when it reads the file.. I am assuming you have to use a Flat File Source...

in that case all the lines are read into a single column...

true that you will be able to delete empty lines and lines that start with a #, but you end up having the rest in a single column instead of having 4 coulmns.

|||

You could add a second dataflow where you read the file after removing the '#' rows. This time, the flat file source has 4 columns instead a single one. This is just in case you don't want to use the script component. A drawback is that you need to read the file twice.

|||Given your example, I'd follow Rafael's recommendation. If the file is large, and you don't want to process it twice, take a look at http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx. This has some example script for taking in the flat file and parsing it. You could alter the script slightly to look for and discard the "#" rows.

Deleting leading 0's in numbers stored in a text field.

I am trying to use several tables that have one 10-character text field in
common. Most of the records have a numeric expression, but some tables have leading
0's, and some don't.
I can't cast the field to numbers because there are some records that have
letters also.
What function can I use to get rid of all the 0s at the left of each record?
(Sort of a LTRIM function that gets rid of 0s instead of spaces).

Thanks!

While I am not aware of any built-in function to perform this (maybe a good SQLCLR function candidate :) ), this TSQL will work as tested below...

declare @.Field varchar(10)

declare @.i int

set @.Field = '000123405'

set @.i = 1

--remove leading 0s?

if charindex('0', @.Field) = 1

begin

while @.i <= Len(@.Field)

begin

--character a 0?

if charindex('0',@.Field,@.i) = @.i

begin

set @.Field = substring(@.Field, (@.i + 1), (len(@.Field)-@.i))

end

else

begin

break

end

--increment counter

set @.i = @.i + 1

end

end

select @.Field

|||

Leading zeroes are fine for casting character values to numeric/integer/money data types. So it should be fine without doing any trimming. For the rows that have letters you can filter those using a case expression like:

case when col not like '%[^0-9]%' then cast(col as int) end

or below although this checks for conversions to integer/numeric/money data types

case when isnumeric(col) = 1 then cast(col as int) end

And if you want to strp the leading zeroes you can use the expression below:

substring(col, patindex('%[123456789]%', col), 8000 /* 4000 if col is Unicode */)

|||the code above has a bug.. here is the correct and much efficient code

DECLARE @.i INT
,@.output VARCHAR(MAX)
,@.Input varchar(max)

set @.Input = '0012321'

SET @.i = 1

IF CHARINDEX('0', @.Input) = 1
BEGIN
WHILE @.i <= LEN(@.Input)
BEGIN

IF CHARINDEX('0',@.Input,@.i) = 0
BEGIN
SET @.output = SUBSTRING(@.Input,@.i,LEN(@.Input))
BREAK
END

SET @.i = @.i + 1

END

END

RETURN @.output

Deleting Files using SSIS Scripting Object

I am utlizing a scripting object in my ssis to combine two text files into one final file, and then I want to delete the original files. To do this I am utilizing the FileSystemInfo namespace and associating the file names, then utilizing the DELETE functionality.

The creation of the final file works perfectly...unfortunately, my base files do not delete, and I do not get a failure message or indictator.

Here is my code:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports System.IO.File
Imports System.IO.FileSystemInfo
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim strCurrentMonth As String
Dim strCurrentYear As String
Dim strWriteFileName As String
Dim strReadHeaderFileName As String
Dim strReadBodyFileName As String

'Utilizing a case statement, determine the monthname & year and set the appropriate variables

Select Case Month(Now())
Case 1
strCurrentMonth = "January"
Case 2
strCurrentMonth = "February"
Case 3
strCurrentMonth = "March"
Case 4
strCurrentMonth = "April"
Case 5
strCurrentMonth = "May"
Case 6
strCurrentMonth = "June"
Case 7
strCurrentMonth = "July"
Case 8
strCurrentMonth = "August"
Case 9
strCurrentMonth = "September"
Case 10
strCurrentMonth = "October"
Case 11
strCurrentMonth = "November"
Case 12
strCurrentMonth = "December"
End Select

strCurrentYear = Year(Now()).ToString

'Set variables with file names (reader files and write file) for ease in readability and to
'set final (write file) with appropriate nameing convention utilized by Matria HealthCare.

strWriteFileName = "\\CUPSRV05\SHARED\IS\Public\Data Export\Matria\Files TO Matria\cup_ref_cup_" & strCurrentMonth & strCurrentYear & "_ftp_ReferralFormat.txt"

strReadHeaderFileName = "\\CUPSRV05\SHARED\IS\Public\Data Export\Matria\Files TO Matria\Matria_Referral_Control.txt"

strReadBodyFileName = "\\CUPSRV05\SHARED\IS\Public\Data Export\Matria\Files TO Matria\Matria_Referral.txt"

'create stream reader/writer objects

Dim sr As New StreamReader(strReadHeaderFileName)
Dim sr2 As New StreamReader(strReadBodyFileName)
Dim sw As New StreamWriter(strWriteFileName)

'feed the header record into the final file

Do Until sr.Peek = -1
'write the header record
sw.WriteLine(sr.ReadLine)
Loop

'close the read stream for the header record file
sr.Close()

'Feed the body records into the final file
Do Until sr2.Peek = -1
'write all base records
sw.WriteLine(sr2.ReadLine)
Loop

'close the read stream for the body records
sr2.Close()

'close the write stream for the final distribution file
sw.Close()

'dispose of all stream objects
sr.Dispose()
sr2.Dispose()
sw.Dispose()

Dim EligBaseFile As New FileInfo("strReadBodyFileName")
Dim EligHeaderFile As New FileInfo("strReadHeaderFileName")

EligBaseFile.Delete() <--These do not delete or through an error
EligHeaderFile.Delete()

'final statement for SSIS package to determine script result

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I would appreciate any light you can shed on this. Thanks!

I have also posted this in the Visual Basic Language forum. But, again, any help/guidance would be appreciated.

|||

hi,

My issue going beyond of yours because of I am not be able even to read my file...

Do Until sFitxer.Peek = -1

sFitxer3.WriteLine(sFitxer.ReadLine)

Loop

--

Dim line As String

Do

line = sFitxer.ReadLine

sFitxer3.WriteLine(line)

Loop Until line Is Nothing

Neither of them works.

any help will be welcomed.

|||

Dim EligBaseFile As New FileInfo("strReadBodyFileName")
Dim EligHeaderFile As New FileInfo("strReadHeaderFileName")

EligBaseFile.Delete() <--These do not delete or through an error
EligHeaderFile.Delete()

The problem is on the first two lines. You are passing strReadBodyFileName and strReadHeaderFileName as string values rather than variables. Remove the quotes around them.

|||

You could always simplify the script you have as well:

File.WriteAllText(varForCombinedFile, File.ReadAllText(strReadHeaderFileName))
File.AppendAllText(varForCombinedFile, File.ReadAllText(strReadBodyFileName))
File.Delete(strReadHeaderFileName)
File.Delete(strReadBodyFileName)

Hope this helps.

|||

Also you can use String.Format("{0:MMMM}", DateTime.Now) to derive the Long month name instead of the Select Case.

Deleting certain text patterns from a column

Hi,
I have a column in SQL DB and the column contains the information like:
<ProductDescription>This TV is good. </ProductDescription> This TV is sold
out.
<ProductDescription>This TV is bad. </ProductDescription> This TV is not
selling well.
(By the way, I am NOT talking about the XML-formatted SQL DB, which was
introduced in SQL 2000. The tag is just text mainly used for human
consumption.)
I want to delete all the text between <ProductDescription> and
</ProductDescription>, including the tags from the column. Is it possible?
It looks like the Replace function cannot take wildcard character and I am
thinking doing it programmatically, like with C#, is the only way. I
appreciate your help!Try something like this:
declare @.tag varchar(30)
declare @.test varchar(8000)
set @.test = 'Don''t get <tag> get rid of this </tag>rid of outside stuff'
set @.tag = 'tag'
select
stuff(@.test,charindex('<'+@.tag+'>',@.test),charindex('</'+@.tag+'>',@.test) +
len(@.tag) + 2,'')
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Kevin" <no_spam@.nospamfordiscussion.com> wrote in message
news:OfqSCkMYFHA.1736@.tk2msftngp13.phx.gbl...
> Hi,
> I have a column in SQL DB and the column contains the information like:
> <ProductDescription>This TV is good. </ProductDescription> This TV is sold
> out.
> <ProductDescription>This TV is bad. </ProductDescription> This TV is not
> selling well.
> (By the way, I am NOT talking about the XML-formatted SQL DB, which was
> introduced in SQL 2000. The tag is just text mainly used for human
> consumption.)
> I want to delete all the text between <ProductDescription> and
> </ProductDescription>, including the tags from the column. Is it possible?
> It looks like the Replace function cannot take wildcard character and I am
> thinking doing it programmatically, like with C#, is the only way. I
> appreciate your help!
>
>|||Thanks! Didn't think of using that function.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23o1nh9MYFHA.2588@.TK2MSFTNGP14.phx.gbl...
> Try something like this:
> declare @.tag varchar(30)
> declare @.test varchar(8000)
> set @.test = 'Don''t get <tag> get rid of this </tag>rid of outside stuff'
> set @.tag = 'tag'
> select
> stuff(@.test,charindex('<'+@.tag+'>',@.test),charindex('</'+@.tag+'>',@.test) +
> len(@.tag) + 2,'')
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Kevin" <no_spam@.nospamfordiscussion.com> wrote in message
> news:OfqSCkMYFHA.1736@.tk2msftngp13.phx.gbl...
>

Delete trigger capturing text data

I am trying to use a trigger to capture text data that is being deleted and
put it into an audittrail table. I use the same logic for the insert
trigger(using inserted instead of deleted) and it functions as it should,
however when I do a delete, this trigger seems to be overlooked as no insert
ever occurs.
create trigger evidence_audit_delete on evidence
for delete
not for replication
as
begin
insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name,
curr_val, username, session_id)
select getdate(),'DELETE','evidence',d.rowguid,'note',cast(r.note as
varchar(4000)),system_user,@.@.spid from deleted d, evidence r where r.rowguid
= d.rowguid
endHi tracey,
If I understood it right, rowguid should be the key, so that row would be
deleted when you called the trigger. So the join returns no rows.
rewrite it this way.
create trigger evidence_audit_delete on evidence
for delete
not for replication
as
begin
insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name,
curr_val, username, session_id)
select getdate(),'DELETE','evidence',d.rowguid,'note',cast(d.note as
varchar(4000)),system_user,@.@.spid from deleted d
end
Let me know if this helps|||I put the join in because you cant seem to use the text data(even if its
converted into a varchar) from the deleted table. Is there a way to get the
trigger to fire before the delete maybe?
Server: Msg 311, Level 16, State 1, Procedure evidence_audit_delete, Line 7
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
tables.
"Omnibuzz" wrote:

> Hi tracey,
> If I understood it right, rowguid should be the key, so that row would be
> deleted when you called the trigger. So the join returns no rows.
> rewrite it this way.
> create trigger evidence_audit_delete on evidence
> for delete
> not for replication
> as
> begin
> insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name
,
> curr_val, username, session_id)
> select getdate(),'DELETE','evidence',d.rowguid,'note',cast(d.note as
> varchar(4000)),system_user,@.@.spid from deleted d
> end
> Let me know if this helps|||try this then (untested)
create trigger evidence_audit_delete on evidence
instead of delete
not for replication
as
begin
insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name,
curr_val, username, session_id)
select getdate(),'DELETE','evidence',d.rowguid,'note',cast(r.note as
varchar(4000)),system_user,@.@.spid from deleted d, evidence r where r.rowguid
= d.rowguid
delete from a from evidence a, deleted d where a.rowguid = d.rowguid
end|||That works
"Omnibuzz" wrote:

> try this then (untested)
>
> create trigger evidence_audit_delete on evidence
> instead of delete
> not for replication
> as
> begin
> insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name
,
> curr_val, username, session_id)
> select getdate(),'DELETE','evidence',d.rowguid,'note',cast(r.note as
> varchar(4000)),system_user,@.@.spid from deleted d, evidence r where r.rowgu
id
> = d.rowguid
> delete from a from evidence a, deleted d where a.rowguid = d.rowguid
> end|||good. If you are looking no further can you close the thread.
Thanks
Omnibuzz
"Tracey" wrote:
> That works
> "Omnibuzz" wrote:
>

Delete Textfiles after dataload

I am transferring data from text file to sql server.I have created .dtsx packages. After the package executes i need to remove the data from the text file or even remove the text files. But i want my package to run it receives new textfile.What do i need to do?Please help?

The file system task on the control flow can delete the file for you.|||Thanks it worked...|||where does the deleted file get stored...|||If the files were good, they go to textfile and binary heaven. If the files were bad (corrupt), they might go to purgatory or worse |||

sureshv wrote:

where does the deleted file get stored...

Umm, they don't. They get deleted.

delete text character

hi,

for example ;

select price from table

query results:195dollar

I want to see query result only numeric:195

how can I do?

You could try this:

select convert(int, price) as price from table

That would round the value to the nearest integer value.

|||

Allen White wrote:

You could try this:

select convert(int, price) as price from table

That would round the value to the nearest integer value.

Don't working :(

Delete Takes Time when u have text datatype column

I have a table with a coulumn of Text Datatype. This column is stored with XML data. Now i need to delete records from this table using a SP. The deletion 60K records is taking 30 min almost. Generally this table will not have this much deletion. But when there is no Text data type column (i removed the column from the table for testing) the same deletion is taking only few sec. What is the techinical reason behind it.

If anyone could explain this it would be really helpful

Leena

See Text datatype in Books Online. It is one of the BLOB datatype, it depends with the option you set in the table for large objects.

Micrsoft recommand to switch over from Text/Ntext to Varchar(Max)/NVarchar(Max) - if you use sql server 2005.

|||

It really is all depending on the size of the data in your text datatype. Data is stored in ~8K pages, and if you have 80K worth of text in columns, it will require 10 pages to be deleted to delete the row. Your table will look something like (and this can get ugly for 60K rows):

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

And all of the pages would have to be deleted. I think this might be improved upon performance wise in 2005, but I am not 100% sure. There is a concept of Ghost rows that may apply here where pages are simply marked as deleted. You should also try using the varchar(max) datatype if at all possible, though it has the same issues.

|||

thanks mani and louis for quick help.

one more q..... is sp_spaceused reflect the text data storage space also. If not how can we findout that.

the table with Text data and with out text data is almost showing same space used. Why its so..

Thanks again

Leena

|||

The large datatype value is stored outside of your table row & your current tables row's keeping the pointer of the outside stored values. To change this settins use the following statement

Exec sp_tableoption N'MyTable', 'large value types out of row', OFF

Exec sp_tableoption N'MyTable', 'text in row', ON

|||

When the BLOB's are not stored in row you have a lot of random I/O going on too.

Random I/O are very expensive of course. It has to check for each record where the BLOB data is located, remove it, go to the next record, check where the BLOB data is located, remove it, ... well you get my point I hope :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Delete row when data is numeric?

I'm using a DTS package to import a large CSV file. There is a particular column that contains text or numbers. I want to delete the row if that column has a number, I've used IsNumeric in the selection portion of the statement, but can't figure out how to use it as part of my where clause.Never mind - i got it right after I posted... it has been a long week and I'm not thinking clearly any longer: Where IsNumeric(columnName)=1