Category: bat

Batch File to split CSV into smaller parts

So I am using CHATGPT regularly now and this is a good example of where it can prove very useful. The bottom bat iteration was obtained from StackOverflow and successfully split a large file into chunks of 2,500. I then realised that each sub file needed to have headers in it to assist in importing the information into a database. I asked CHATGPT to alter the starting file to ensure that headers were included subsequently.

It did it no problem..

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
@echo off
 
setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.
 
SET BFN=C:\csv\target.csv
 
REM Edit this value to change the number of lines per file.
SET LPF=2500
 
REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=SplitFile
 
REM Do not change beyond this line.
 
SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
set "header="
for /f "tokens=* usebackq" %%a in ("%BFN%") do (
 
if not defined header (
set "header=%%a"
echo !header! > %SFN%!FileNum!.%SFX%
) else (
SET /A LineNum+=1
echo %%a >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1
echo !header! > %SFN%!FileNum!.%SFX%
)
)
 
)
 
endlocal
 
Pause

This is the original obtained from Stack Overflow..

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
@echo off
 
setlocal ENABLEDELAYEDEXPANSION
 
REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=C:\csv\target.csv
 
REM Edit this value to change the number of lines per file.
SET LPF=2500
 
REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=SplitFile
 
REM Do not change beyond this line.
 
SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F "delims==" %%l in (%BFN%) Do (
SET /A LineNum+=1
echo %%l >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1
 
)
 
)
 
endlocal
 
Pause