1

I have an xml format file for bulk loading in SQL Server 2008. I need to replace the instance of TERMINATOR="\r\n" with TERMINATOR="\n". I'm trying to use the command line utility FART.exe. No matter how I try, I cannot seem to get the utility to recognize the characters.

I have tried passing as combos of the find string and replace string:

\r\n \n
"\r\n" "\n"
""\r\n"" ""\n""
^\r^\n ^\n
TERMINATOR=""\r\n"" TERMINATOR=""\n""
TERMINATOR=""^\r^\n"" TERMINATOR=""^\n""

and many, many more combinations. Every article I look up about escaping DOS commands tells me something different and I've tried them all with no luck. The truly strange thing is that not only does using \r\n \n not do what I want it to do (replace the literals) but it does not replace the carriage return + line feed characters at the end of each line in the file.

The format file I need to edit. I believe the line I've underlined in red is causing a problem:

The command window output from calling Jrepl. It's obvious that the utility has a problem with the red-underlined line from the other screenshot:

Paul Roub
  • 36,322
  • 27
  • 84
  • 93
Ognennyy
  • 11
  • 1
  • 1
  • 3

2 Answers2

1

To replace all \r\n literals with \n, simply use:

fart yourFile.xml \r\n \n

To only replace within larger string TERMINATOR="\r\n", then use:

fart -C yourfile.xml TERMINATOR=\x22\\r\\n\x22 TERMINATOR=\x22\\n\x22

I prefer to use JREPL.BAT - It is much more powerful than FART in that it can use regular expressions, plus it has a number of additional advanced features. The only limitation relative to FART is it can only process one file at a time.

To replace all \r\n literals with \n:

jrepl \r\n \n /l /f yourFile.xml /o -

To be more specific using a literal search string with quote escaped as \q and backslash as \\:

jrepl "TERMINATOR=\q\\r\\n\q" "TERMINATOR=\q\\n\q" /x /l /f yourFile.xml /o -

or using a regular expression:

jrepl "(TERMINATOR=\q)\\r(?=\\n\q)" "$1" /x /f yourFile.xml /o -
dbenham
  • 127,446
  • 28
  • 251
  • 390
  • Hi Dbenham. Unfortunately the first thing I tried was "fart yourFile.xml \r\n \n" and it did not work. I get the result "Replaced 0 occurence(s) in 0 file(s). I only need to find and replace in one file at a time, so I think I will try your jrepl command line utility instead for now. Thanks for the help! – Ognennyy Apr 07 '16 at 16:29
  • @Ognennyy - Hmmm. That is odd, because I tested with FART and it seemed to work for me. But good luck with JREPL – dbenham Apr 07 '16 at 18:59
  • I've posted two screenshots above that I hope will help. If I create just a text file with one line in it (Terminator="\r\n") then call jrepl \r\n \n /l /f "test.txt" /o "test_new.txt", it works correctly and replaces it with (Terminator="\n"). However, I seems like Jrepl doesn't like my xml file. Check out my screenshot. The line I hilighted in the xml file contains two links. The other screenshot of the cmd window when I send output to stdout clearly demonstrates that it's having an issue there. What do you think @dbenham ? Will Jrepl not work with a full blown xml file? – Ognennyy Apr 08 '16 at 19:32
  • Forgot to mention... I thought the issue was related to the links inside the . Second is a square shape – Ognennyy Apr 08 '16 at 19:39
  • @Ognennyy - JREPL is designed to work with ANSI (extended ASCII) encoded files. My guess is your XML file is encoded as some form of Unicode (something other than UTF-8). That might also explain why FART did not work for you. – dbenham Apr 08 '16 at 21:01
0

Simplest and quickest is fart binary method, for example:

fart.exe -C D:\sample.txt \x0d\x0a \x0a
Kingsley
  • 14,398
  • 5
  • 31
  • 53
Mariusz Mleczko
  • 21
  • 1