Saving a very large page by dividing it into 100 lines of csv files

[mountain_human]

Good days all.

I have 7-12 thousand lines of invoice files every month. While transferring them to the main program, the main program accepts a maximum of 101 lines of csv files. The first line of each section should also be a fixed header line. It is necessary to assign successive numbers to each file.
Example;
I have 10000 (ten thousand) lines of june.ods file.

• The first line (header line) has column headings such as Date, Description, InvoiceNo, Amount, VAT Base, VAT. (When the file is split, this line should be fixed in the first lines of all csv files.)

• I need a macro which will create files like;

june-001.csv
june-002.csv
…
…
…
june-099.csv
june-100.csv

by dividing the (10000 line) june.ods file into 100 line files and adding the title in the first line of the main file to the first line of each file.

Thanks for your help in advance, regards.

[JohnSUN]

It was not trivial

Option Explicit 

Sub SplitBigTable
Const MAX_ROW_COUNT = 100
Dim oSheet As Variant
Dim oCursor As Variant
Dim aData As Variant
Dim aRes As Variant
Dim i As Long, nRow As Long, nFile As Long 
 
	oSheet = ThisComponent.getCurrentController().getActiveSheet()
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	aData = oCursor.getFormulaArray()
	ReDim aRes(0 To MAX_ROW_COUNT)
	aRes(0) = aData(0)
	nRow = 1
	nFile = 1
	For i = 1 To UBound(aData)
		aRes(nRow) = aData(i)
		nRow = nRow + 1
		If nRow > MAX_ROW_COUNT Then 
			SaveDataToFile(Replace(ThisComponent.getURL(), ".ods", "-" & Format(nFile,"000") & ".csv"), aRes)
			ReDim aRes(0 To MAX_ROW_COUNT)
			aRes(0) = aData(0)
			nRow = 1
			nFile = nFile + 1
		EndIf 
	Next i
	If nRow > 1 Then
		nRow = nRow-1
		ReDim Preserve aRes(0 To nRow)
		SaveDataToFile(Replace(ThisComponent.getURL(), ".ods", "-" & Format(nFile,"000") & ".csv"), aRes)
	EndIf 
End Sub

Sub SaveDataToFile(FilePath as String, DataList())
Dim FileChannel as Integer
Dim i as Integer
Dim oFile as Object
Dim oOutputStream as Object
Dim oStreamString as Object
Dim oUcb as Object
Dim sCRLF as String
Dim outputString as String
	sCRLF = CHR(13) & CHR(10)
	oUcb = createUnoService("com.sun.star.ucb.SimpleFileAccess")
	oOutputStream = createUnoService("com.sun.star.io.TextOutputStream")
	If oUcb.Exists(FilePath) Then
		oUcb.Kill(FilePath)
	End If
	oFile = oUcb.OpenFileReadWrite(FilePath)
	oOutputStream.SetOutputStream(oFile.GetOutputStream)
	For i = 0 To Ubound(DataList())
		outputString = """" & Join(DataList(i),""";""") & """" & sCRLF
Rem ...or just outputString = Join(DataList(i),",") & sCRLF or any other
		oOutputStream.WriteString(outputString)
	Next i
	oOutputStream.CloseOutput()
End Sub

[JohnSUN]

setEncoding()

[mountain_human]

Thank you very much, because this was a big problem for me and you solved 99% of it. The macro runs very well and does its job perfectly (it saves-as the files with UFT-8 code page). Now, I have a small problem about code pages. Is it possible to save-as the csv files with code page ISO-8859-9 in this macro?

Fore thanks for your kind assists.

[ajlittoz]

This is not a solution for the initial problem but a follow-on question. Move it as a comment under the relevant answer and delete this “non-answer”. Thanks.

Regarding the initial problem, have you considered using a common utility like awk or split which would do the job more easily than LO (taking care specifically of the first line)? In addition it would be faster. These utilities have equivalents in nearly all OSes.

Changing the encoding of a file is done with utility iconv.

[mountain_human]

Thanks a lot. iconv works for text files. I have hundreds of csv files which were automatically produced by the (precious) macro. If I can’t save them all with a predefined code page by the relevant macro, then I need a batch file which uses iconv command to convert all csv files (the names of the files includes sequential numbers like May001, May002, …, May250, etc).

[ajlittoz]

CSV are text files.