Splitting a CSV file every 65535 lines

[spac3m0nk3y]

I need to read in a CSV file and split it up into a new file every 65536 lines.  The reason is because we are using an older version of Excel that can only view 65536 lines at a time.  Please also note that we do NOT have Excel installed on the PC where the script will run.

Here is what I have so far:

#include <file.au3>

$sFilePath = @ScriptDir & "\testFile.csv"
If NOT FileRead($sFilePath) Then
    ConsoleWrite("DEBUG: Error: " & @error & @CRLF)
    Exit
Else
    ConsoleWrite("DEBUG: Lines: " & _FileCountLines($sFilePath) & @LF)
EndIf

$i = 65536
$hFileRead = FileRead($sFilePath)
$i_pos = StringInStr($hFileRead, @CRLF, 0, $i)
FileWrite(@ScriptDir & "\first.csv", StringLeft($hFileRead, $i_pos - 1))
FileWrite(@ScriptDir & "\second.csv", StringMid($hFileRead, $i_pos + 2))

I pieced this together from a couple other threads.  I can split up the file into two files, but I need to keep going, so that if "testFile.csv" has 150,000 lines, it gets split up into 3 files, or if it has 200,000 lines, it gets split up into 4 file, etc.  I'm not sure how to incorporate this into my script.  I was thinking about a While loop, but I'm not sure how to do it.

[Jos]

Untested but should be close:

$sFilePath = @ScriptDir & "\testFile.csv"
$hFR = Fileopen($sFilePath)

$of = 1
$i = 65536

while 1
        $fho = FileOpen(@ScriptDir & "\output" & $of & ".csv",2)
        for $x = 1 to $i
            $irecord = FileRead($hFR)
            if @error then ExitLoop 2
            FileWrite($fho,$irecord)
        Next
        FileClose($fho)
        $of += 1
WEnd

Jos

[spac3m0nk3y]

Untested but should be close:

$sFilePath = @ScriptDir & "\testFile.csv"
$hFR = Fileopen($sFilePath)

$of = 1
$i = 65536

while 1
        $fho = FileOpen(@ScriptDir & "\output" & $of & ".csv",2)
        for $x = 1 to $i
            $irecord = FileRead($hFR)
            if @error then ExitLoop 2
            FileWrite($fho,$irecord)
        Next
        FileClose($fho)
        $of += 1
WEnd

Jos

Running that just puts everything in output1.csv and keeps creating output#.csv files.

[Jos]

Did you try it and was that the outcome?

Jos

[spac3m0nk3y]

Did you try it and was that the outcome?

Jos

Tried it, it creates output1.csv with the contents as testFile.csv and then keeps creating empty output#.csv files until I kill the script.

[UEZ]

Here another version:

#include <File.au3>

$sFilePath = @ScriptDir & "\test.csv"
SplitCSVtoNewFile($sFilePath, 50)

Func SplitCSVtoNewFile($sFilePath, $iLine)
    Local $aCSV_Lines = StringSplit(StringStripCR(FileRead($sFilePath)), @LF, $STR_CHRSPLIT)
    If @error Then Return SetError(1, 0, 0)
    Local $i = 1, $c = 1, $sLine, $hFile, $d = StringLen(UBound($aCSV_Lines))
    Do
        $sLine &= $aCSV_Lines[$i] & @CRLF
        If Not Mod($i, $iLine) Then
            $hFile = FileOpen(StringTrimRight($sFilePath, 4) & StringFormat("%0" & $d & "i", $c) & ".csv", $FO_OVERWRITE)
            FileWrite($hFile, StringTrimRight($sLine, 2))
            FileClose($hFile)
            $sLine = ""
            $c += 1
        EndIf
        $i += 1
    Until $i = $aCSV_Lines[0]
    If $sLine <> "" Then
        $hFile = FileOpen(StringTrimRight($sFilePath, 4) & StringFormat("%0" & $d & "i", $c) & ".csv", $FO_OVERWRITE)
        FileWrite($hFile, StringTrimRight($sLine, 2))
        FileClose($hFile)
    EndIf
EndFunc

 

Edited June 23, 2015 by UEZ

[Jos]

ah ...i see it... this line should be:

$irecord = FileReadLine($hFR)

 

[spac3m0nk3y]

ah ...i see it... this line should be:

$irecord = FileReadLine($hFR)

 

I had to edit

FileWrite($fho,$irecord)

to:

FileWriteLine($fho,$irecord)

Otherwise, everything is written on one line.

Do you know what I would add to get the column headers from the first file into the start of each subsequent one as the first line?

[Jos]

Yea, sorry about the FilexxxLine() mess I made. That happens when you code without testing.

Just read the first line and save it in a variable and write that as first line after the FileOpen statement within the loop?

Jos