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
Combining CSV Files
For combing CSV files.. Navigate to folder using cmd.Put the command
copy *.csv merge.csv
