Need to remove quotes from text file

Question

I have written below in a batch file to copy a csv file in a folder to txt file and move into our import folder

copy "\\jarvisfs1\groups\InforIFC\Payroll\NEW\*.csv" "\\jarvisfs1\groups\InforIFC\Payroll\NEW\EPRO.txt"
------------------------------------------------------------------------

The text file that is generated no longer seems to have the columns seperated when trying to import into sql or bi Tool.

But when I use the below command using a vba script the columns are fine within Sql or BI Tool

Dim objFSO, objFile, objFileTSV
Dim strLine, strNewLine
Dim FileNameLength, LineLength, NewFileName, Linepos, Quote, QuoteCount, 
TotalFilesConverted
set WshShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("scripting.filesystemobject")
strCurPath = objFSO.GetAbsolutePathName(".")
TotalFilesConverted = 0
For Each objFile In objFSO.getfolder(strCurPath).Files

            If UCase(Right(objFile.Name, 4)) = ".CSV" Then
                            Result = WshShell.Popup("Converting " & 
  objFile.Name & "...", 3,"" , 64)
                            FileNameLength = Len(objFile.Name)-4
                            NewFileName = "EPRO.txt"
                            Set objFile = objFSO.OpenTextFile(objFile, 1)
                            Set objFileTSV = 
  objFSO.CreateTextFile(NewFileName)

                            Do Until objFile.AtEndOfStream
                                            strLine = objFile.ReadLine
                                            LineLength = Len(strLine)
                                            Linepos =1
                                            strNewLine =""
                                            Quote = False
                                            QuoteCount = 0


   Do While Linepos <= LineLength
                                                            If Mid(strLine, 
   Linepos, 1) = "," And Not Quote Then

    strNewLine = strNewLine + vbTab

   Quote = False
                                                            ElseIf 
  Mid(strLine, Linepos, 1) = Chr(34) Then

 QuoteCount = QuoteCount + 1

 If QuoteCount = 2 And Linepos <> LineLength Then

 If Mid(strLine, Linepos, 2) = Chr(34) & Chr(34) Then

 strNewLine = strNewLine + Chr(34)

  Linepos = Linepos + 1

 Quote = True

  QuoteCount = 1

  Else

  Quote = False

  QuoteCount = 0

  End If

  Else

  Quote = True

  End If
                                                           ElseIf QuoteCount 
  = 0 And Mid(strLine, Linepos, 1) = "-" Then

  strNewLine = strNewLine + Mid(strLine, Linepos, 1) + "0"
                                                            Else

  strNewLine = strNewLine + Mid(strLine, Linepos, 1)
                                                            End If
                                                            Linepos = 
   Linepos + 1
                                            Loop


                                            objFileTSV.WriteLine strNewLine
                                            strNewLine = ""
                            Loop
                            objFile.Close
                            TotalFilesConverted = TotalFilesConverted +1
                            objFileTSV.Close

            End If
    Next

    MsgBox CStr(TotalFilesConverted) + " Files Converted from CSV to TXT."

But using the above command the columns are fine within the text file. The issue I have with the 2nd script is I cannot get the script to run automatically using Task Schu, where as the batch file I can get to run automatically

How it needs to appear

How it appears after the new script is ran

Answer 1

In Batch it is easy enough to just remove the quotes and commas and replace the commas with a whitespace

We can therefore just copy the CSV files to a tempfile, then we re-write the content to your actual text file after we replace all the quotes and commas.

@echo off
setlocal enabledelayedexpansion

if exist "\jarvisfs1\groups\InforIFC\Payroll\NEW\tempfile.txt" (del /Q "\jarvisfs1\groups\InforIFC\Payroll\NEW\tempfile.txt")
copy "\jarvisfs1\groups\InforIFC\Payroll\NEW*.csv" "\jarvisfs1\groups\InforIFC\Payroll\NEW\tempfile.txt"
for /f "delims=" %%f in(\jarvisfs1\groups\InforIFC\Payroll\NEW\tempfile.txt) do (
set var=%%f
set var=!var:"=!
set var=!var:,= !
echo !var! >> "\jarvisfs1\groups\InforIFC\Payroll\NEW\EPRO.txt"
)
del /Q "\jarvisfs1\groups\InforIFC\Payroll\NEW\tempfile.txt"

Finally notice how the replace function works. the character you want to replace comes before the = and the character you want to replace it with comes after the = So if you want to replace + with - in a string then you will do !var:+=-! or %var:+=-% if you do not setlocal enabledelayedexpansion to understand exactly how delayedexpansion works, you can run setlocal /? from cmd.exe

Answer 2

I don't think that command adds in the quotes and commas. They were there to begin with. Open your csv in Notepad and see them yourself.

So you just need some way of removing them. Although that is technically posible in cmd, I wouldn't want to attempt it. So here is a solution in powershell:

(Get-Content "\jarvisfs1\groups\InforIFC\Payroll\NEW*.csv") | ForEach-Object {
    $_.Replace(",", "").Replace("`"", "")
} | Set-Content "\jarvisfs1\groups\InforIFC\Payroll\NEW\EPRO.txt"