Thank you everybody for your help! I'm getting super close, but still running into a few snags. I'm trying to perform the 3 operations below on a handful of large (200+ MB) .csv files. I'm able to complete 1 and 2, but not 3. I can't seem to get the script to continue running after #2; I assume this is because
goto:eof is being used. I've tried to setup #2 to run within a loop so it can continue after completion, but am having no luck. I'm attaching my full code below:
1. Remove the first line of each .csv file in the directory
2. Remove the last 2 lines of each .csv file in the directory
3. Combine all .csv files into a single .csv file and append column-headers.txt to the top of that file
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