Splitting Excel Files using Bat Script- easy way

Sometimes we will face situations, where we have to split our excel sheets into multiple. Suppose for importing to some softwares, if we have a excel sheet with millions of data we have to split it to small adata files for our convinence to upload. In those situations, copy pasting lakhs of adata into seperate excel sheet is a tiredous task. Here I will give a script which you can run as a bat file to split the xcel sheets.

1) Save your excel sheet in csv format.

2) Make a file and rename it as script.bat. Open it in edit mode and paste the following code:-

@echo off

setlocal ENABLEDELAYEDEXPANSION

REM Edit this value to change the name of the file that needs splitting. Include the extension.

SET BFN=importfile.csv

REM Edit this value to change the number of lines per file.

SET LPF=50000

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

3)In the above file set BFN variable the csv  file name  we want to split. and in SFN variable we have to put the split files prefix for our convinence .in LPF we have to mention the count of number of rows per excel sheet.Now save it and close it.

4) Put this script file in same folder of the csv file. Now open Command prompt and navigate to same file location and run the script.

Combining CSV Files

For combing CSV files.. Navigate to folder using cmd.Put the command
copy *.csv merge.csv