Re: Bad data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Date: Mon, 1 Mar 2004 17:32:50 -0000

#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

  • Bad data
    ... I am creating a DTS process that will extract data from a ... text file and place it into sql server. ... double quotes must be off, because when I try to tell sql ...
    (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
    ... Formatting Character Data into Datetime fields ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. ... > Water industry AWWA ... >>#2 The Quotes should not be entered into the table as> well. ...
    (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)