Batch file to split csv file

Question

I have a very large  csv file   500mb  and I wish to break this up into into smaller  csv files in command prompt   Basically trying to find a linux  split  function in Windows    This has to be a batch script as my machine only has windows installed and requesting softwares is a pain  I came across a number of sample codes  http   forums techguy org software-development 1023949-split-100000-line-csv-into html   however  it does not work when I execute the batch  All I get is one output file that is only 125kb when I requested it to parse every 20 000 lines   Has anyone ever come across a similar problem and how did you resolve the issue

User · Answer

Use the cgwin command SPLIT  Samples  To split a file every 500 lines counts   split -l 500  filename ext    by default  it adds xa xb xc    to filename after extension  To generate files with numbers and ending in correct extension  use following  split -l 1000 sourcefilename ext destinationfilename -d --additional-suffix  ext   the position of -d or -l does not matter     -d  is same as --numeric-suffixes  -l  is same as --lines   For more  split --help

User · Answer

Try this out    echo off setLocal EnableDelayedExpansion  set limit 20000 set file export csv set lineCounter 1 set filenameCounter 1  set name  set extension  for   a in   file   do       set  name    na      set  extension    xa     for  f  tokens      a in   file   do       set splitFile  name -part filenameCounter  extension      if  lineCounter  gtr  limit            set  a filenameCounter  filenameCounter    1         set lineCounter 1         echo Created  splitFile             echo   a gt  gt   splitFile       set  a lineCounter  lineCounter    1     As shown in the code above  it will split the original csv file into multiple csv file with a limit of 20 000 lines  All you have to do is to change the  file  and  limit  variable accordingly  Hope it helps

User · Answer

I found this question while looking for a similar solution  I modified the answer that   Dale gave to suit my purposes  I wanted something that was a little more flexible and had some error trapping  Just thought I might put it here for anyone looking for the same thing    echo off setLocal EnableDelayedExpansion GOTO checkvars   checkvars     IF   1      GOTO syntaxerror     IF NOT   1    -f   GOTO syntaxerror     IF  2     GOTO syntaxerror     IF NOT EXIST  2 GOTO nofile     IF   3      GOTO syntaxerror     IF NOT   3    -n  GOTO syntaxerror     IF   4       GOTO syntaxerror     set param  4     echo  param   findstr  xr   1-9  0-9   0   gt nul  amp  amp            goto proceed                    echo  param  is NOT a valid number         goto syntaxerror         proceed     set limit  4     set file  2     set lineCounter 1  limit      set filenameCounter 0      set name      set extension       for   a in   file   do           set  name    na          set  extension    xa             for  f  usebackq tokens      a in   file   do           if  lineCounter  gtr  limit                set splitFile  name  part filenameCounter  extension              set  a filenameCounter  filenameCounter    1             set lineCounter 1             echo Created  splitFile                     cls         echo Adding Line  splitFile  -  lineCounter          echo   a gt  gt   splitFile          set  a lineCounter  lineCounter    1           echo Done      goto end  syntaxerror     Echo Syntax   0 -f Filename -n  Number Of Rows Per File      goto end  nofile     echo  2 does not exist     goto end  end

User · Answer

A free windows app that does that  http   www addictivetips com windows-tips csv-splitter-for-windows

User · Answer

If splitting very large files  the solution I found is an adaptation from this  with PowerShell  embedded  in a batch file  This works fast  as opposed to many other things I tried  I wouldn t know about other options posted here    The way to use mysplit bat below is     mysplit bat  lt mysize gt   myfile    Note  The script was intended to use the first argument as the split size  It is currently hardcoded at 100Mb  It should not be difficult to fix this   Note 2  The filname should be enclosed in single quotes  Other alternatives for quoting apparently do not work   Note 3  It splits the file at given number of bytes  not at given number of lines  For me this was good enough  Some lines of code could be probably added to complete each chunk read  up to the next CR LF  This will split in full lines  not with a constant number of them   with no sacrifice in processing time   Script mysplit bat    REM Using https   stackoverflow com questions 19335004 how-to-run-a-powershell-script-from-a-batch-file  REM and https   stackoverflow com questions 1001776 how-can-i-split-a-text-file-using-powershell  PowerShell         upperBound   100MB          rootName    2          from    rootName          fromFile    io file   OpenRead  from           buff   new-object byte    upperBound          count    idx   0         try              do                   Reading      upperBound                  count    fromFile Read  buff  0   buff Length                  if   count -gt 0                        to     0   1   -f   rootName   idx                       toFile    io file   OpenWrite  to                      try                           Writing      count     to      to                          tofile Write  buff  0   count                        finally                           tofile Close                                                          idx                  while   count -gt 0                   finally               fromFile Close                 End PowerShell

User · Answer

This will give you lines 1 to 20000 in newfile1 csv and lines 20001 to the end in file newfile2 csv  It overcomes the 8K character limit per line too   This uses a helper batch file called findrepl bat from - https   www dropbox com s rfdldmcb6vwi9xc findrepl bat  Place findrepl bat in the same folder as the batch file or on the path   It s more robust than a plain batch file  and quicker too   findrepl  o 1 20000  lt file csv  gt newfile1 csv findrepl  o 20001    lt file csv  gt newfile2 csv

[windows] Batch file to split .csv file

Examples related to windows

Examples related to batch-file

Examples related to csv

Examples related to command-prompt