Remove Double Quotes from Flat File

[erunamo114]

Hello everyone!

I am a new member to the Computer Hope forums here. I am a very new beginner to the MS-DOS/Windows command line, as well as the Unix/Linux command line. I am seeking to learn more about the commands and grow in my understanding.

Currently, I have a flat file I am using for an ETL job to integrate data between two CRM systems. I am performing a data replication task from the source system to a flat file. That flat file contains double quotes around all of the values in the flat file, and I need to remove those double quotes in order to properly work with the flat file. Could someone explain what Windows command line script I could use to remove all of the text qualifier double quotes in the flat file? If possible, could you also briefly explain the solution just so that I get a better understanding?

Thank you all for your time!

[Salmon Trout]

Could you explain what you mean by a "flat file"?

[Squashman]

How big is the file?

[Lemonilla]

This is what I got when I looked it up: http://en.wikipedia.org/wiki/Flat_file_database

If it is a text file, you can do this:

Code: (batch file) [Select]

@echo off
REM This is to make your program pretty when run

setlocal EnableDelayedExpansion
REM This is to allow for variable editing within the for loop

for /f "delims=" %%A in (flatFILE.txt) do (
REM This loops through each line in flatFILE.txt
REM and performs everything within () to the line
REM which is held in %%A

    set a=%%A
    echo !a:"=! >>newFile.txt
    REM this adds the line to newFile.txt replacing every instance of " with no character
)

You can also use dbenham's repl.bat like this:

Code: (cmd.exe) [Select]

type flatFile.txt | repl "\q" "" ix >newFile.txt
Simply put it in the same folder as flatFile.txt

dbenham can probably show you a way with his jrepl, but I haven't gotten around to looking at it yet.


Size will matter, some programs will not be able to process larger files.

[erunamo114]

Thank you all for the responses. By a "flat file" I just mean a file that isn't going directly to another system. The file is being exported as a .csv file, however and not a .txt file. Would the above solution work for a .csv file?

Also, the file itself is only 25KB large.

Thanks!

[erunamo114]

@Lemonilla,

I tried the solution you provided using the Repl.bat file to remove the quotes. It worked great. Do you think you could explain how that command is working? I ask because I have another task I need to perform where I need to add a date stamp to a file name using a post-processing script after a file is exported. I was wondering if I might could use this batch file for that as well or if I would need a different solution for that.

[Squashman]

A CSV file is a text file.  No ifs ands or buts about it.

[Squashman]

You really wouldn't use REPL to rename files with a date.  You can do that with plain ole batch.

[Geek-9pm]

For what it's worth...
Many utilities found in Unix are  not duplicated in Windows.
One reason for this is the Windows ports of such utilizes are free and work well, so there was little need for Microsoft  to create them anew in Windows.
So if you want awk or sed, get the windows versions from GNU for Windows
http://gnuwin32.sourceforge.net/
Peal for windows
https://www.perl.org/get.html
Python for Windows
https://www.python.org/downloads/

Or, just come here and somebody will do it for you in batch.

[foxidrive]

where I need to add a date stamp to a file name using a post-processing script

REPL can add text here and there easily but the solution is heavily dependent on the text that needs to be changed,
and if it is in the first column, or following some other text, and if there are other lines etc.

REPL uses regular expressions to modify text, though it can do literal text replacement too.

The date can be taken using another few lines of batch script.

You really wouldn't use REPL to rename files with a date.  You can do that with plain ole batch.

Oops, I read it as text manipulation rather than renaming a file.

Adding code to get a date in a variable here:

The first four lines of this code will give you reliable YY DD MM YYYY HH Min Sec variables in XP Pro and higher.

Code: [Select]

@echo off
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"

set "datestamp=%YYYY%%MM%%DD%" & set "timestamp=%HH%%Min%%Sec%" & set "fullstamp=%YYYY%-%MM%-%DD%_%HH%-%Min%-%Sec%"
echo datestamp: "%datestamp%"
echo timestamp: "%timestamp%"
echo fullstamp: "%fullstamp%"
pause


[erunamo114]

@Lemonilla,

I had a quick question regarding the other solution that you provided. In the batch file I have created, I have the following script:

echo !a:"=! >>newfile.txt

When I try to execute this to write the lines to the new file, no lines are being written to the file.

This is how I have my batch file so far:

@echo off

setlocal EnableDelayedExpansion

type NUL > SF_USER_NO_QUOTES.csv
REM this line is to create the new text file that will not have any quotes. I wasn't sure if I should create this file first and then echo to it or if the echo itself should create the file if it doesn't already exist.

for /f "delims=" %%A in (SF_USER.csv) do (

set a=%%A
echo !a:"=! >>SF_USER_NO_QUOTES.csv

)

Does this look correct?

[foxidrive]

BTW, the # icon in the editor will put code tags around the selected text, to make it clear where code is.

[foxidrive]

That's an interesting effect in your code above - I don't recall seeing that before.

Quotes are often very difficult to handle in batch code - and using delayed expansion can affect some normal text,
even though it helps with various other poison characters.

Code: [Select]

@echo off
setlocal EnableDelayedExpansion
(
for /f "delims=" %%A in (SF_USER.csv) do (

   set a=%%A
   set a=!a:"=!
   echo(!a!
)
)>SF_USER_NO_QUOTES.csv
pause

The code above fixes your issue, but test it with the following file and you'll see how it corrupts certain other text.

Code: [Select]

"SF_USER.csv"
"1
2"
a
b
c
def "" ghi
!z1!
"!z2!
"!z3!"
end!


[erunamo114]

Quotes are often very difficult to handle in batch code - and using delayed expansion can affect some normal text,
even though it helps with various other poison characters.

@FoxiDrive,

Since doing batch on double quotes may not necessarily be reliable to maintain the integrity of the rest of the text, do you any suggestions for more reliable alternatives for performing this process?

[foxidrive]

Lemonilla's suggestion using repl.bat is robust and will work with any normal text, and even binary files.

Dave Benham has developed a new version called Jrepl.bat and you can also use that, though for this task Jrepl.bat may not have any benefit.

You can also use dbenham's repl.bat like this:

Code: (cmd.exe) [Select]

type flatFile.txt | repl "\q" "" ix >newFile.txt


I should have been clearer by saying that the code snippet I showed above treats !z1! etc as an environment variable using delayed expansion, but because they weren't defined variables they disappeared, and the single ! also disappears when using delayed expansion.

There are some workarounds for this but repl.bat and jrepl.bat and Aacini's Findrepl.bat are all robust tools for text handling, and other functions.