Page 1 of 1

Merge multiple CSV with same Header

Posted: Sun Dec 10, 2017 10:11 pm
by musikai
If you have multiple CSV files with the same Header and want to merge them into 1 CSV file:

Code: Select all

    Sub merge_Folder_of_CSV_with_same_Headers
    If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then GlobalScope.BasicLibraries.LoadLibrary("Tools")
    oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
    sFolderpath=""
    rem---folderpicker
    rem to not use the folderpicker set the folder here and uncomment:
    rem sFolderpath=converttourl("C:\Users\kai\Desktop")
    if sFolderpath="" then
    oDialog = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")

    If oDialog.Execute() = 1 Then
    sFolderpath = oDialog.getDirectory
    else
    exit sub
    end if

    end if
    if not oSFA.Exists(sFolderpath) then
    msgbox "Folder not found!"
    exit sub
    end if

    outputfile = sFolderpath & "/" & "merged.csv"
    if fileexists(outputfile) then kill(outputfile)   
    
    oTextoutputStream = CreateUnoService("com.sun.star.io.TextOutputStream")
    ooutputStream = oSFA.openFileWrite(outputfile)
    oTextoutputStream.setOutputStream(ooutputStream)

    sFileName=""
    sFileName = Dir(sFolderpath & "/", 0)
    Do While (sFileName <> "")
    if GetFileNameExtension(sFileName)="csv" then
    Fileurl=sFolderpath & "/" & sFileName

	oTextinputStream = CreateUnoService("com.sun.star.io.TextInputStream")
	inputfile = converttourl(Fileurl)
	oinputStream = oSFA.openFileRead(inputfile)
	oTextinputStream.setInputStream(oinputStream)
	
	stringA = oTextinputStream.readLine()
	if Headerwritten=0 then 
	oTextoutputStream.writestring(stringA & chr(13) & chr(10))
	Headerwritten=1
	end if
	do while oTextinputStream.isEOF()=0
	stringA = oTextinputStream.readLine()
	oTextoutputStream.writestring(stringA & chr(13) & chr(10))
	loop
       
	oTextinputStream.closeInput
    end if
    sFileName = Dir()
    loop

    oTextoutputStream.closeOutput()
    msgbox "Saved: " & convertfromurl(outputfile)
    OpenCSVinCalc(outputfile)
    End Sub

    rem-----for opening in Calc
    Sub OpenCSVinCalc(Url)
    Dim oDocument as Object
    fnURL=ConvertToURL(Url)

    'Create new document and import data
    oDocument = StarDesktop.LoadComponentFromURL( fnURL, "_blank", 0, _
    Array(MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ), _
             MakePropertyValue( "FilterOptions", "59,34,76,1,1" )

    End Sub

    Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
       oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" )
       If Not IsMissing( cName ) Then
          oPropertyValue.Name = cName
       EndIf
       If Not IsMissing( uValue ) Then
          oPropertyValue.Value = uValue
       EndIf
       MakePropertyValue() = oPropertyValue
    End Function

Re: Merge multiple CSV with same Header

Posted: Tue Dec 12, 2017 5:20 am
by MrProgrammer
musikai wrote:If you have multiple csv-files with the same Header and want to merge them into 1 csv-file:
On a good operating system (anything except Windows) these three commands will merge the CSV files in a directory as long as the common header line doesn't contain embedded NewLines. These commands should work for bash or ksh, probably other shells too.

test -e Merged.csv && rm Merged.csv
for QQF in *.csv; do CSVHDR=$(head -1 "$QQF"); echo "$CSVHDR" >Merged.csv; break; done
fgrep -ahvxe "$CSVHDR" *.csv >>Merged.csv

Re: Merge multiple CSV with same Header

Posted: Fri Apr 26, 2019 1:26 am
by Kalimo
Hey @musikai, thx heaps for your code, it was exactly what I was looking for. Incidentally, it works perfectly in LibreOffice too :D
One small modification I made after your code seemed to get into an endless loop was to change the output filename extension to .txt instead of .csv. I found on my machine it was adding merged.csv to merged.csv endlessly. So, the complete code, with my modification below. Another solution would be to place the output file into a different folder but just adding the .txt extension worked for me as a quick solution:
musikai wrote:If you have multiple csv-files with the same Header and want to merge them into 1 cvs-file...

Code: Select all

    Sub merge_Folder_of_CSV_with_same_Headers
    If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then GlobalScope.BasicLibraries.LoadLibrary("Tools")
    oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
    sFolderpath=""
    rem---folderpicker
    rem to not use the folderpicker set the folder here and uncomment:
    rem sFolderpath=converttourl("C:\Users\kai\Desktop")
    if sFolderpath="" then
    oDialog = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")

    If oDialog.Execute() = 1 Then
    sFolderpath = oDialog.getDirectory
    else
    exit sub
    end if

    end if
    if not oSFA.Exists(sFolderpath) then
    msgbox "Folder not found!"
    exit sub
    end if

    outputfile = sFolderpath & "/" & "merged.csv.txt"
    if fileexists(outputfile) then kill(outputfile)   
    
    oTextoutputStream = CreateUnoService("com.sun.star.io.TextOutputStream")
    ooutputStream = oSFA.openFileWrite(outputfile)
    oTextoutputStream.setOutputStream(ooutputStream)

    sFileName=""
    sFileName = Dir(sFolderpath & "/", 0)
    Do While (sFileName <> "")
    if GetFileNameExtension(sFileName)="csv" then
    Fileurl=sFolderpath & "/" & sFileName

	oTextinputStream = CreateUnoService("com.sun.star.io.TextInputStream")
	inputfile = converttourl(Fileurl)
	oinputStream = oSFA.openFileRead(inputfile)
	oTextinputStream.setInputStream(oinputStream)
	
	stringA = oTextinputStream.readLine()
	if Headerwritten=0 then 
	oTextoutputStream.writestring(stringA & chr(13) & chr(10))
	Headerwritten=1
	end if
	do while oTextinputStream.isEOF()=0
	stringA = oTextinputStream.readLine()
	oTextoutputStream.writestring(stringA & chr(13) & chr(10))
	loop
       
	oTextinputStream.closeInput
    end if
    sFileName = Dir()
    loop

    oTextoutputStream.closeOutput()
    msgbox "Saved: " & convertfromurl(outputfile)
    OpenCSVinCalc(outputfile)
    End Sub

    rem-----for opening in Calc
    Sub OpenCSVinCalc(Url)
    Dim oDocument as Object
    fnURL=ConvertToURL(Url)

    'Create new document and import data
    oDocument = StarDesktop.LoadComponentFromURL( fnURL, "_blank", 0, _
    Array(MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ), _
             MakePropertyValue( "FilterOptions", "59,34,76,1,1" )

    End Sub

    Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
       oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" )
       If Not IsMissing( cName ) Then
          oPropertyValue.Name = cName
       EndIf
       If Not IsMissing( uValue ) Then
          oPropertyValue.Value = uValue
       EndIf
       MakePropertyValue() = oPropertyValue
    End Function

Re: Merge multiple CSV with same Header

Posted: Fri Apr 26, 2019 5:01 pm
by Villeroy
See also Merge sheets of spreadsheet documents which imports all types of spreadsheet documents and text files with proper import options for all flavours of csv.