Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

problem using GREP newline characters with DTS

Posted on 2008-06-10
1
Medium Priority
?
816 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

715 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