0

I need a batch application

  1. main objective : split .csv files into seperate files. with a row limit.
  2. the source folder contains many .CSV files.
  3. each main file has 2 header rows. and the split files should have the same 2 header rows.
  4. there must must a parameter in the code to set the rows limit in the split file.
  5. there must be a parameter in the code for the source folder (containing 1 or more .csv files)
  6. there must be a paramater in the code for target folder (to contain the split files)
  7. each split file with have the same name like the source file witn addition of the "number" out of all splitted files (example: source file ABC.CSV with 3 rows. row limit = 2. traget files: ABC_1_of_2.csv, ABC 2_of_2.csv)

This code worked for a single file

@echo off
setlocal EnableExtensions DisableDelayedExpansion

rem // Define constants here:
set _FILE="C:\Users\USER\Desktop\Split\MAINFILE.CSV"   & rem // (first command line argument is input file)
set /A "_LIMIT=100" & rem // (number of records or rows per output file)

rem // Split file name:
set NAME=c:\users\USER\Desktop\Split\Splitted\MAINFILE & rem // (path and file name)
set EXT=.csv    & rem // (file name extension)

rem // Determine number of lines excluding header:
for /F %%I in ('^< "%_FILE%" find /V /C ""') do set /A "COUNT=%%I-1"

rem // Split file into multiple ones:
setlocal EnableDelayedExpansion
rem // Read file once:
< "!_FILE!" (
    rem // Read header (first line):
    set /P HEADER1=""
    set /P HEADER2=""
    rem // Calculate number of output files:
    set /A "DIV=(COUNT-1)/_LIMIT+3"
    rem // Iterate over output files:
    for /L %%J in (1,1,!DIV!) do (
        rem // Write an output file:
        > "!NAME!_%%J_of_!DIV!!EXT!" (
            rem // Write header:
            echo/!HEADER1!
        echo/!HEADER2!
            rem // Write as many lines as specified:
            for /L %%I in (1,1,%_LIMIT%) do (
                set "LINE=" & set /P LINE=""
                if defined LINE echo/!LINE!
            )
        )
    )
)
endlocal

endlocal
exit /B

but when I tried to move for taking all the files in the folder I'm stuck code is not working

setlocal EnableExtensions DisableDelayedExpansion
Set /A "Location=C:\USER\"
For /R "C:\USER\Source\" %%f in (*.csv) do (
rem // Define constants here:
set _FILE=%%f   & rem // (first command line argument is input file)
set /A "_LIMIT=20" & rem // (number of records or rows per output file)
rem // Split file name:
set "NAME=C:\USER\Splitted\%%~nf" & rem // (path and file name)
set EXT=.csv    & rem // (file name extension)
timeout /t 3 /nobreak
rem // Determine number of lines excluding header:
for /F %%I in ('^< "%_FILE%" find /V /C ""') do set /A "COUNT=%%I-2"
rem // Split file into multiple ones:
setlocal EnableDelayedExpansion
rem // Read file once:
< "!_FILE!" (
rem // Read header (first line):
set /P HEADER1=""
set /P HEADER2=""
rem // Calculate number of output files:
set /A "_DIV=(COUNT-1)/_LIMIT+1"
Echo !_DIV!
rem // Iterate over output files:
for /L %%J in (1,1,!DIV!) do (
        rem // Write an output file:
        > "!NAME!_%%J_of_!_DIV!!EXT!" (
            rem // Write header:
            echo/!HEADER1!
            echo/!HEADER2!
            rem // Write as many lines as specified:
            for /L %%I in (1,1,%_LIMIT%) do (
                set "LINE=" & set /P LINE=""
                if defined LINE echo/!LINE!
            )
        )
    )
)
endlocal
)
endlocal
exit /B

Please help.

current status - only one empty file created - header rows only. File named - Book2_1_of_1.csv See runtime in the output attachment.

Output

Thanks

Amir
  • 1
  • 2
  • 2
    In batch files we need to double on `%` for instance.. `for /f %f` should be `for /f %%f` and `set _FILE=%f ` should be `set "_FILE=%%f"` – Gerhard Nov 26 '19 at 09:25
  • Thanks! I changed that. Still doesn't work though – Amir Nov 26 '19 at 09:38
  • 2
    Doesn't work is a bit vague. What doesn't work, what happens or does not happen? Did you debug anything yet? start by removing `@echo off` to see what the output to the screen is. – Gerhard Nov 26 '19 at 09:47
  • I tried Debug, but can't pause inside for loop. it's like when I add the external for loop it stopped working. so I used timeout /t 10 /nobreak but it works in the internal for. the external for is corrupt – Amir Nov 27 '19 at 13:32
  • Well one thing is for sure you should stay consitant with your delayedexpansion variables. `%_FILE%` should be `!_FILE!` then to debug. Remove @echo off and then open cmd and run the script from there instead of by double click. – Gerhard Nov 27 '19 at 13:35
  • Hi Gerhard, I really appreciate your Help. It took me a very long time to understand the issue was spaces in the file path. now I get the error missing operator. i have updated the code in the message. – Amir Nov 28 '19 at 07:12
  • At what point do you get the missing operator message? It is difficult for me to debug your code as I need to build your environment, files, paths etc. – Gerhard Nov 28 '19 at 07:20
  • I have a sample file for testing - [link](https://drive.google.com/file/d/1fewPsk22-5ONC7ELniWejHpkmgwq2epM/view?usp=drivesdk) – Amir Nov 28 '19 at 09:17
  • What's the login information? Please [edit your question](https://stackoverflow.com/posts/59047339/edit), paste the sample file content into it, and format it using the [**{}**] button. – Compo Nov 28 '19 at 13:30
  • ok, attached a sample file. should be saved as CSV – Amir Nov 28 '19 at 15:15
  • Your posted sample file does not contain any doublequoted fields or any characters which can be considered 'poison', is that guaranteed to be correct for every file you're intending to parse? Also your 'header lines' seem to all be exactly the same and have empty values, is that really the case? It would be helpful if you were also to give us an indication of the maximum number of records per file, the maximum number of characters held in those records, and clarify that all records are made up of fields each delimited by a comma only. – Compo Nov 28 '19 at 15:59
  • the problem is not the file.. it should work on any CSV file with 2 header rows. the maximum number of rows is set by the VAR _LIMIT and currently is set to 100.. it can be changed to 1 for this – Amir Nov 28 '19 at 17:07
  • Amir, the lengths and number of records, as well as the delimiter characters and those used in your unquoted content are very important. If you're looking for something to work with any CSV file, then you should consider using a tool or script utilising a scripting language other than standard batch file commands. – Compo Nov 28 '19 at 17:15
  • `set /A _FILE=%%f` ?? `%%f` is holding a *file name*. `set /a` tries to assign an *Integer* to `%_FILE%`. Think about it... – Stephan Nov 29 '19 at 08:43
  • Hi All, I have updated the code - see edited. and also attached a screenshot of the Run. current status - only one empty file created - header rows only. – Amir Dec 01 '19 at 22:55
  • @GerhardBarnard Could you have a a look? – Amir Dec 04 '19 at 06:37
  • you forgot to use [delayed expansion](https://stackoverflow.com/questions/30282784/variables-are-not-behaving-as-expected/30284028#30284028) in one spot with `%_FILE%` – Stephan Dec 04 '19 at 08:33
  • I can't find where? when I add the delayed expension before the file. I get the same error. – Amir Dec 04 '19 at 09:56
  • here: `for /F %%I in ('^< "%_FILE%" find /V /C ""') do set /A "COUNT=%%I-2"` – Stephan Dec 04 '19 at 21:06

0 Answers0