|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > File Clean up using Batch Script
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
File Clean up using Batch Script
|
|
| Sanjay 2002-08-04, 1:23 pm |
| I am receiving file from a datasource which i have tp bcp
into a table
Problem is that file is not clean it has a lot of blank
lines followed by data
I want to clean and get rid of blank lines it using batch
script
ANy help on how to do it
BAD File looks like this
, , , , , , , ,,,
, , , , , , , ,,,
, , , , , , , ,,,
, , , , , , , ,,,
, , , , , , , ,,,
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
CLEAN File should look like this
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
Thanks
Sanjay
| |
| Andrew J. Kelly 2002-08-04, 1:23 pm |
| Just import it into a staging table and then do something like this
depending on if they are inserted as Null's or not:
DELETE FROM Stagingtable WHERE Col1 = '' AND Col2 = '' ....
or
DELETE FROM Stagingtable WHERE Col1 IS NULL AND Col2 IS NULL...
--
Andrew J. Kelly, SQL Server MVP
TargitInteractive
"Sanjay" <sanjayg@hotmail.com> wrote in message
news:0b3f01c23be9$199f3c40$a5e
62ecf@tkmsftngxa07...
> I am receiving file from a datasource which i have tp bcp
> into a table
> Problem is that file is not clean it has a lot of blank
> lines followed by data
> I want to clean and get rid of blank lines it using batch
> script
> ANy help on how to do it
>
> BAD File looks like this
>
> , , , , , , , ,,,
> , , , , , , , ,,,
> , , , , , , , ,,,
> , , , , , , , ,,,
> , , , , , , , ,,,
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
>
> CLEAN File should look like this
>
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
>
> Thanks
> Sanjay
>
>
>
>
| |
| Sanjay 2002-08-04, 7:23 pm |
| I would still like to clean it using Batch script as
sometimes i get some junk charaters bcoz of which i am
unable to import the file
For EX
BAD File looks like this
, , , , , , , ,,,
, , , , , , , ,,,
, , NO records Round~~~~~, , , , , ,,,
, , , , , , , ,,,
, , , , , , , ,,,
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
i want to clean it to get only data
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
54141, 8/3/02,3,15,4913,1626,0,,,0
| |
| Andrew J. Kelly 2002-08-04, 8:23 pm |
| That has nothing to do with Sql Server then. I suggest you post in a VB or
C++ group and you might get more responses.
--
Andrew J. Kelly, SQL Server MVP
TargitInteractive
"Sanjay" <sanjayg@hotmail.com> wrote in message
news:0a8b01c23c17$fbe35810$2ae
2c90a@phx.gbl...
> I would still like to clean it using Batch script as
> sometimes i get some junk charaters bcoz of which i am
> unable to import the file
>
> For EX
> BAD File looks like this
>
> , , , , , , , ,,,
> , , , , , , , ,,,
> , , NO records Round~~~~~, , , , , ,,,
> , , , , , , , ,,,
> , , , , , , , ,,,
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
>
> i want to clean it to get only data
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
>
>
>
>
| |
| BP Margolin 2002-08-04, 9:23 pm |
| Sanjay,
I would still suggest that you still consider Andrew's original suggestion
of loading to a staging table. The staging table does NOT have to the same
structure as the ultimate destination. It could be simply a single column
defined as varchar (100) or whatever length is appropriate. You could then
do successive "data cleansing" operations ... check that the first column is
numeric for example. Once you have clean data, you could use either T-SQL or
DTS to move the data to the ultimate destination.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Sanjay" <sanjayg@hotmail.com> wrote in message
news:0a8b01c23c17$fbe35810$2ae
2c90a@phx.gbl...
> I would still like to clean it using Batch script as
> sometimes i get some junk charaters bcoz of which i am
> unable to import the file
>
> For EX
> BAD File looks like this
>
> , , , , , , , ,,,
> , , , , , , , ,,,
> , , NO records Round~~~~~, , , , , ,,,
> , , , , , , , ,,,
> , , , , , , , ,,,
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
>
> i want to clean it to get only data
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
> 54141, 8/3/02,3,15,4913,1626,0,,,0
>
>
>
>
| |
| BP Margolin 2002-08-04, 9:23 pm |
| Andrew,
Perhaps DTS, which is part and parcel of SQL Server, is another alternative
;-)
Sanjay,
If you wish to consider DTS as an option, then DTS questions are best posted
to the microsoft.public.sqlserver.dts newsgroup. The bulk of questions to
that particular newsgroup are answered very expertly and knowledgeably by
Darren Green and Allan Mitchell.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Andrew J. Kelly" <akelly@targitinteractive.com> wrote in message
news:#jO1ZVCPCHA.1768@tkmsftngp12...
> That has nothing to do with Sql Server then. I suggest you post in a VB
or
> C++ group and you might get more responses.
>
> --
> Andrew J. Kelly, SQL Server MVP
> TargitInteractive
>
>
> "Sanjay" <sanjayg@hotmail.com> wrote in message
> news:0a8b01c23c17$fbe35810$2ae
2c90a@phx.gbl...
> > I would still like to clean it using Batch script as
> > sometimes i get some junk charaters bcoz of which i am
> > unable to import the file
> >
> > For EX
> > BAD File looks like this
> >
> > , , , , , , , ,,,
> > , , , , , , , ,,,
> > , , NO records Round~~~~~, , , , , ,,,
> > , , , , , , , ,,,
> > , , , , , , , ,,,
> > 54141, 8/3/02,3,15,4913,1626,0,,,0
> > 54141, 8/3/02,3,15,4913,1626,0,,,0
> > 54141, 8/3/02,3,15,4913,1626,0,,,0
> > 54141, 8/3/02,3,15,4913,1626,0,,,0
> >
> > i want to clean it to get only data
> > 54141, 8/3/02,3,15,4913,1626,0,,,0
> > 54141, 8/3/02,3,15,4913,1626,0,,,0
> > 54141, 8/3/02,3,15,4913,1626,0,,,0
> > 54141, 8/3/02,3,15,4913,1626,0,,,0
> >
> >
> >
> >
>
>
|
|
|
|
|