[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

problem using GREP newline characters with DTS

Posted on 2008-06-10
1
Medium Priority
?
822 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

608 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