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
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