DosTips.com

Code: Select all

For /F "Tokens=1 Delims=" %%a In ('Dir /A-D /B *.csv') Do (
   Echo %%a
)

Code: Select all

find /v /c "#$@$#@" < w1.csv > %temp%\#
set /p total=<%temp%\#
set /a num2keep=%total%-2
set lineNUM=0
for /f "tokens=*" %%L in (w1.csv) do (
call :sub1 %%L)
:sub1
set /a lineNUM+=1
if %lineNUM% gtr %num2keep% goto :eof
echo %* >> newfile.csv

Code: Select all

For /F "Tokens=1 Delims=" %%a In ('Dir /A-D /B *.csv') Do (
   find /v /c "#$@$#@" < %%a > %temp%\#
   set /p total=<%temp%\#
   set /a num2keep=%total%-2
   set lineNUM=0
   for /f "tokens=*" %%L in (%%a) do (
   call :sub1 %%L)
   :sub1
   set /a lineNUM+=1
   if %lineNUM% gtr %num2keep% goto :eof
   echo %* >> %%a
)

Code: Select all

@echo off &SETLOCAL
For %%a In (*.csv) Do CALL:ProcessFile "%%~a"
goto:eof

:ProcessFile
SET "fname=%~1"
ECHO(processing %fname%
FOR /f "delims=:" %%i IN ('findstr /n "^" "%fname%"') DO SET "lines=%%i"
SET /a lines-=2
SET /a counter=0
(FOR /f "delims=" %%i IN ('findstr /n "^" "%fname%"') DO (
   SET "line=%%i"
   SET /a counter+=1
   SETLOCAL ENABLEDELAYEDEXPANSION
   SET "line=!line:*:=!"
   IF !counter! leq %lines% ECHO(!line!
   ENDLOCAL
))>"%fname%.new"
goto:eof

Code: Select all

@echo off &SETLOCAL
For %%a In (*.csv) Do CALL:ProcessFile "%%~a"
goto:eof

:ProcessFile
SET "fname=%~1"
ECHO(processing %fname%
FOR /f "tokens=2delims=:" %%i IN ('find /c /v "" "%fname%"') DO SET "lines=%%i"
SET /a lines-=2
<"%fname%" (
FOR /l %%i IN (1,1,%lines%) DO (
   SET "line="
   SET/p "line="
   SETLOCAL ENABLEDELAYEDEXPANSION
   ECHO(!line!
   ENDLOCAL
))>"%fname%.new"
goto:eof

Code: Select all

@echo off &SETLOCAL
For %%a In (*.csv) Do CALL:ProcessFile "%%~a"
goto:eof

:ProcessFile
SET "fname=%~1"
ECHO(processing %fname%
FOR /f "tokens=2delims=:" %%i IN ('find /c /v "" "%fname%"') DO SET "lines=%%i"
SET /a lines-=2
SETLOCAL ENABLEDELAYEDEXPANSION
<"%fname%" (
FOR /l %%i IN (1,1,%lines%) DO (
   SET "line="
   SET/p "line="
   ECHO(!line!
))>"%fname%.new"
ENDLOCAL
goto:eof

Code: Select all

@echo off

:: use this script to combine multiple .csv files into one file
:: .csv files must be exported from DFP in Excel CSV format without header and with ID's
:: the first line of each .csv file will be removed. column headers are inserted from a separate file

:: ========== USER PREREQUISITES ========== ::
echo Did you complete the following?
echo   1. Save data files from DFP in Excel CSV format without headers and with ID's
echo   2. Save each .csv file using the following naming convention: "w##.csv"
echo   3. Save each .csv file to the same folder as this script
echo   4. Save column-headers.txt to same folder as this script
echo   5. Make sure this script has not been run previously for these files 
echo Enter y for yes or n for no
set /p warning=
if %warning% == y goto process
if not %warning% == y goto stop


:: ========== WARN USER & EXIT PROGRAM ========== ::
:stop
echo.
echo.
echo Please complete the steps above before running this file.
pause
exit


:: ========== RUN PROGRAM IF PREREQUISITES MET ========== ::
:process
cls

:: get current working directory
Set _Path=%cd%
PushD %_Path%


:: ~~~~~~~~~~ CHECK FOR REQUIRED FILES ~~~~~~~~~~ ::
if not exist column-headers.txt (
   echo Please upload the following file and start again: column-headers.txt
   echo.
   pause
   exit
)

if not exist w*.csv (
   echo Please upload the following file and start again: w##.csv
   echo.
   pause
   exit
)


:: ~~~~~~~~~~ INFORM USER TO WAIT FOR SCRIPT TO RUN ~~~~~~~~~~ ::
cls
echo Processing - Please wait 45-60 minutes for confirmation message to appear...
echo.
echo User Running Script: %USERNAME%
echo Start Time: %time%
echo.


:: ~~~~~~~~~~ DELETE PREVIOUS COMBINED FILE ~~~~~~~~~~ ::
:: delete previous combined .csv file, if one was created
IF EXIST combined.csv del combined.csv


:: ~~~~~~~~~~ REMOVE FIRST LINE OF EACH CSV FILE ~~~~~~~~~~ ::
:: delete first 1 lines of each file -- this contains the column headers
For /F "Tokens=1 Delims=" %%a In ('Dir /A-D /B *.csv') Do (
   Echo %%a

   :: define how many lines to remove. 0 lines includes column headers; 1 lines excludes column headers
   For /F "Usebackq Tokens=1 skip=1 Delims=" %%I In ("%%a") Do >>"%temp%\%%a" Echo.%%I
   Move /Y "%temp%\%%a" .\
)
PopD


:: ~~~~~~~~~~ CREATE TEMP FOLDER ~~~~~~~~~~ ::
:: create a 'temp' directory. delete directory and contents if already exists
IF EXIST temp rmdir temp /s/q
mkdir temp


:: ~~~~~~~~~~ REMOVE LAST TWO LINES OF EACH CSV FILE ~~~~~~~~~~ ::
:: delete last 2 lines of each file -- these contain totals calculations
For %%a In (*.csv) Do CALL:ProcessFile "%%~a"
goto:eof

:ProcessFile
SET "fname=%~1"
ECHO(processing %fname%
FOR /f "tokens=2delims=:" %%i IN ('find /c /v "" "%fname%"') DO SET "lines=%%i"
SET /a lines-=2
<"%fname%" (
FOR /l %%i IN (1,1,%lines%) DO (
   SET "line="
   SET/p "line="
   SETLOCAL ENABLEDELAYEDEXPANSION
   ECHO(!line!
   ENDLOCAL
))>"temp\%fname%"
goto:eof


:: ~~~~~~~~~~ COMBINE CSV FILES ~~~~~~~~~~ ::
:: combine all .csv files into one file and use column-headers.txt as headers for the combined file
copy column-headers.txt+/b temp\*.csv combined.csv


:: ~~~~~~~~~~ DELETE TEMP FOLDER ~~~~~~~~~~ ::
rmdir temp\ /s/q


:: ~~~~~~~~~~ CONFIRMATION MESSAGE ~~~~~~~~~~ ::
echo.
echo.
echo Success! All CSV files were combined into the following file: combined.csv
echo End Time: %time%
echo.
pause

Code: Select all

For %%a In (*.csv) Do CALL:ProcessFile "%%~a"
:: routines go here.

:: batch file finishes here
goto:eof