Solved

problem using GREP newline characters with DTS

Posted on 2008-06-10
1
799 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

832 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