Merge multiple CSV with same Header

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
musikai
Volunteer
Posts: 294
Joined: Wed Nov 11, 2015 12:19 am

Merge multiple CSV with same Header

Post 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
Last edited by MrProgrammer on Sat Sep 12, 2020 5:32 pm, edited 2 times in total.
Reason: Changed "CVS" typo to "CSV"
Win7 Pro, Lubuntu 15.10, LO 4.4.7, OO 4.1.3
Free Project: LibreOffice Songbook Architect (LOSA)
http://struckkai.blogspot.de/2015/04/li ... itect.html
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Merge multiple CSV with same Header

Post 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
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Kalimo
Posts: 1
Joined: Fri Apr 26, 2019 1:13 am

Re: Merge multiple CSV with same Header

Post 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
OpenOffice 4.1 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Merge multiple CSV with same Header

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply