Delete the row having special characters

[techipa]

Hello All,

I import the data from CSV files to the SQL table. The last step (conversion of the data) in the package fails because the last row in the SQL table shows is as follows

I do not know why I get that last line with all these special characters?

12/24/2006 22:30:00 999.99 NULL
12/24/2006 22:45:00 999.99 NULL
12/24/2006 23:00:00 999.99 NULL
12/24/2006 23:15:00 999.99 NULL
12/24/2006 23:30:00 999.99 NULL
12/24/2006 23:45:00 999.99 NULL
???í?ß ##0_);\("$"# ##0\)! NULL

I wanted to delete that row, may I have any suggestion on how to delete that row? I do not want to hard code anything.

Thanks in advance,
-techiPA

 

[Gixonita]

Hi,

Is the last line always the same?

If so you could

DELETE <YourTable>
WHERE <YourColumn> LIKE '?%'


Hope this helps

 

[AlexCuse]

Does the first column always start with a date? It sounds like you are using DTS, if so I think what you want to do is set up a script task to loop through rows checking that first column (in your CSV), and deleting the entire row if it doesn't meet (whatever criteria you establish). Make sure you have a backup of the file before you try this, because if your check is wrong the script will end up deleting every row.

Another option is to replace the 'special characters' during your transform but that would probably be more difficult, and still leave a bunch of trash in your table.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

Or you could use the ascci() function to identify records with bad characters and delete (it may not be only the last line). Or, and this is what I recommend, you could go back to your vendor and get them to provide you the data without this last line.

Questions about posting. See faq183-874

 

[techipa]

Alex,

The first column may not have the date. It will have some other string as well somethign like this

CPPX.PRIMARY.P01 TEMP NULL NULL
12/31/2006 00:00:00 69.69 NULL
12/31/2006 00:15:00 70.70 NULL
12/31/2006 00:30:00 70.70 NULL
12/31/2006 00:45:00 69.69 NULL

Thanks,
-techiPA

 

[AlexCuse]

Then you will need to set up two checks, and only delete the row if it fails both.

Building on SQLSister's advice, you would need to also set up a staging table to allow all inputs from your file, and use t-SQL to delete the bad rows, and move the remaining data into your real table.

This is really all a matter of what you are comfortable with.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[techipa]

These character's line may not be there as last row everytime. For example I ran the DTS a minutes ago for different date and it ran successfully.

I think it is random. I need to see if the row with the special characters is there then delete.

The CSV files does not have this line so I can not involve Vendor who uploads the files.

Let me know what shoudl be the best way to delete the row if it exists.

Thanks,
-techiPA

 

[AlexCuse]

>>>The CSV files does not have this line so I can not involve Vendor who uploads the files.

Um, where is the line coming from then?

Ignorance of certain subjects is a great part of wisdom

 

[techipa]

I think I wanetd to use ASCII function. Since I know the data values in the first column, I have to delete the row if the values contains something other than A to Z, 0 to 9, /, : and .

Can anyone give me an example of using ASCII function to check the above chars?

Thanks,
-techiPA

 

[AlexCuse]

techipa - I don't think you want to use ascii function for this, I think that can only check one character at a time. Here is something I put together that should help explain a way to do this (using PATINDEX):

--declare table variables
declare @test table (d varchar(20), t varchar(8), s numeric null, n nvarchar)
declare @stagetest table (d varchar(20), t varchar(20), s varchar(20), n varchar)

--insert values to staging test
insert @stagetest
select 'CPPX.PRIMARY.P01', 'TEMP',         NULL,    NULL
union all select '12/24/2006',    '22:30:00',    '999.99',     NULL
union all select '12/24/2006',    '22:45:00',    '999.99',     NULL
union all select '???í?ß',    '##0_);\("$"#',    '##0\)!', NULL

--view what's in staging test
select * from @stagetest

--view what you don't want in final table
select * from @stagetest 
where patindex('[A-Z]', d) = 0
and patindex('%[0-9/]%', d) = 0

--insert only records you want into final table
insert @test
select * from @stagetest 
where patindex('[A-Z]', d) > 0
or patindex('%[0-9/]%', d) > 0

--view results
select * from @test

What patindex is doing is checking the first column (called 'd') for the first occurence of any character between 0-9, as well as the / (this is all that will be allowable in your first column. Also, the top row that you have looks like it should be a column header or something, NOT a value in your table.

YOu are really exposing yourself to issues like this by setting your column types to varchar where they should be dates, etc...

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom