Re: Bad data

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 03/02/04 

Date: Tue, 2 Mar 2004 07:22:58 -0000

You data imports good into my table using a datapump task.

As for dates. If you are not interested in time then personally like to
enter textual dates using this

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)

Is it out of range?

Check like this

You column datatype is a smalldatetime = Date and time data from January 1,
1900, through June 6, 2079

so let's test some failures

(value = "01-01-0203")

if Right( DTSSource("MyPossibleBadData"), 4 ) < "1900" then
 msgbox "OOR - defaulted to 19000101"
end if 

-- 
----------------------------
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike" <anonymous@discussions.microsoft.com> wrote in message
news:4a7501c3ffbf$1563e310$a501280a@phx.gbl...
> Table Structure:
>
> CREATE TABLE [dbo].[INET_Sessions] (
> [Course] [varchar] (8) ,
> [Type] [varchar] (2)  ,
> [SessionDate] [smalldatetime] NOT NULL ,
> [Session] [varchar] (2)  ,
> [Provider] [varchar] (7)   ,
> [SessionDescription] [varchar] (60)   ,
> [DateRange] [varchar] (40)   ,
> [OEPACertType] [varchar] (4)   ,
> [OEPACrs] [varchar] (15)   ,
> [OEPAExpDate] [smalldatetime] NULL ,
> [CourseDescription] [varchar] (60)   ,
> [Contact] [varchar] (35)   ,
> [ProviderName] [varchar] (60)
> ) ON [PRIMARY]
> GO
>
> Sample data:
> "A-1"|"SC"|8/30/2001|"A0"|"AWWA00"|"Strategic Plan for the
> Water industry AWWA
> 2001Conference"||"D"|"104"|8/23/2003|"Strategic Plan for
> the Water industry AWWA 2001Conference"|"Ray
> Shawn"|"American Water Works Association"
> "A-2"|"SC"|8/30/2001|"A0"|"AWWA00"|"Solving the Cooper
> Corrosion Prob. AWWA 2001
> Conference"||"D"|"109"|8/23/2003|"Solving the Cooper
> Corrosion Prob. AWWA 2001 Conference"|"Ray Shawn"|"US
> Association"
> "A-3"|"SC"|8/30/2001|"A0"|"AWWA00"|"Standardization of
> Hach PO4 Kit Data AWWA 2001
> Conference"||"D"|"115"|8/23/2003|"Standardization of Hach
> PO4 Kit Data AWWA 2001 Conference"|"Ray Shawn"|"SS
> Association"
>
> Hope that helps, by the way, where do you begin to learn
> how to set up an Active Script transform to DEFAULT the
> data in this column if it is Out Of Range?
>
>
> >-----Original Message-----
> >#1 is a classic problem when moving date data.  Everybody
> has there own
> >limits and none of them ever seem to meet :-(
> >
> >Have you tried an Active Script transform and DEFAULT the
> data in this
> >column if it is Out Of Range.
> >
> >
> >#2 The Quotes should not be entered into the table as
> well.  Have  you a
> >sample row from the text file and your table structure?
> >
> >-- 
> >
> >Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> >www.allisonmitchell.com - Expert SQL Server Consultancy.
> >www.SQLDTS.com - The site for all your DTS needs.
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >"Mike" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:488201c3ffa4$c3ae5f10$a501280a@phx.gbl...
> >> I am creating a DTS process that will extract data from
> a
> >> text file and place it into sql server.  The data comes
> >> from a Paradox database and outputted into a text file.
> >> The file is delimited with a pipe (|) and text has
> double
> >> quotes "".
> >>
> >> Issue number 1.
> >> One problem I'm noticing, there are many dates in the
> file
> >> that have invalid dates such as 02-04-0203.  So when I
> try
> >> to load them into a datetime field in SQL it gives an
> >> error.  How can that be avoided, temporarily I changed
> the
> >> field type to be a varchar, but that doesn't seem
> >> efficient or like the right thing to do.
> >>
> >> Issue number 2.
> >> The second problem is that somewhere in the file, the
> >> double quotes must be off, because when I try to tell
> sql
> >> server that the text is delimited by a | and "" it gives
> >> an error.  So my work around that issue is to allow
> quotes
> >> to be inserted into the fields then I have to run a
> >> routine to remove the quotes.  That again seems like an
> >> inefficient way of handling the data, but it does work.
> >> Are there any suggestions on if I should alter this
> >> process? What is a better way to handle this data which
> >> seems to have bad data in it?  Is there any way to pre-
> >> clean the data from the textfile so it will go into the
> >> table without quotes?
> >
> >
> >.
> >

Relevant Pages

  • Re: Bad data
    ... Water industry AWWA ... >www.allisonmitchell.com - Expert SQL Server Consultancy. ... >> double quotes must be off, because when I try to tell ...
    (microsoft.public.sqlserver.dts)
  • Re: The best elegant solution to override 65k rows limit in a sheet
    ... >I'm sorry that your jerry-rigged spreadsheet program isn't flexible ... Single quotes are used to delimit different syntactic tokens than ... >I'm so sorry that you work for a stupid company; SQL Server is WINNING ... it doesn't take as many IBM mainframes running DB/2 to do the same work ...
    (microsoft.public.excel)
  • Re: Bad data
    ... #2 The Quotes should not be entered into the table as well. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... www.SQLDTS.com - The site for all your DTS needs. ... > I am creating a DTS process that will extract data from a ...
    (microsoft.public.sqlserver.dts)
  • Re: On to Bulk Insert issues
    ... tell MS SQL Server that the fields are optionally enclosed by quotes. ... format file where you specify each field. ... Second column is data type of the field ... SQL Server developers something, ...
    (comp.databases.ms-sqlserver)
  • Re: Full-Text Search Not Finding Suffix
    ... How do I make it NOT have the double quotes. ... strSearch = "SELECT icgMenuSectionName FROM icgMenu WHERE contains( ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)