Solved

problem using GREP newline characters with DTS

Posted on 2008-06-10
1
792 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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

772 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

12 Experts available now in Live!

Get 1:1 Help Now