Solved

problem using GREP newline characters with DTS

Posted on 2008-06-10
1
795 Views
Last Modified: 2013-11-30
I am writing a Windows 2003 batch file that outputs recursive filenames to text file.  The text file is then used to populate a single-column table in SQL Server 2000.  When I redirect the DIR /B output directly to a text file, the data is formatted as expected (one filename per row).  But when I pipe the DIR output to GREP and then redirect to a text file, the filenames are all on one line with a nonprintable box character between strings.  My DTS task cannot interpret the newlines properly.  I have tried GREP -Z  with no noticable effect; -z produces an empty file.  Any ideas?  Thanks!
@REM This is what I ultimately want to do:

(FOR /R j:\ %%G IN (*.pdf) DO dir /B %%G) | grep -Ex ^[0-9]{9}_20[0-1][0-9][1-4]_[1-9]\.pdf$ > filenames.txt
 

@REM For debugging purposes, I tried a single folder with no FOR loop...
 

@REM With GREP the files are output on one line:

dir /B j:\2008\20084 | grep -Ex ^[0-9]{9}_20[0-1][0-9][1-4]_[1-9]\.pdf$ > filenames.txt
 

@REM Without GREP it works fine:

dir /B j:\2008\20084 > filenames.txt
 

@REM This also works:

(FOR /R j:\ %%G IN (*.pdf) DO dir /B %%G) > filenames.txt

Open in new window

0
Comment
Question by:ksymer
1 Comment
 

Accepted Solution

by:
ksymer earned 0 total points
ID: 21756103
I just found a solution!

Because the DTS text file (source) connection object was not able to interpret the line breaks, I tried another method to import the data.  I started with BULK INSERT, but couldn't figure out the ROWTERMINATOR.  So I just tried to TYPE the output and it worked.  

I am still interested in learning about the character issue if anyone is already working on this.  Thanks!

USE [Admissions]

DELETE FROM dbo.[ApplicationsFilenameValid]

INSERT INTO dbo.[ApplicationsFilenameValid](files)

EXEC ('master..xp_cmdshell ''TYPE \\uwtsql2\c$\batch\Admissions_ApplicationsFilenameValid.txt''') 

DELETE FROM dbo.[ApplicationsFilenameValid]

WHERE files IS NULL

Open in new window

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now