Merge multiple CSV with same Header

Creating Extension - 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 forum is not for asking questions about writing your own macros.

Merge multiple CSV with same Header

Postby musikai » Sun Dec 10, 2017 10:11 pm

If you have multiple csv-files with the same Header and want to merge them into 1 cvs-file:
Code: Select all   Expand viewCollapse view
    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 musikai on Thu Dec 14, 2017 12:05 am, edited 1 time in total.
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/libreofficesongbookarchitect.html
musikai
Volunteer
 
Posts: 230
Joined: Wed Nov 11, 2015 12:19 am

Re: Merge multiple CSV with same Header

Postby MrProgrammer » Tue Dec 12, 2017 5:20 am

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.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3739
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Merge multiple CSV with same Header

Postby Kalimo » Fri Apr 26, 2019 1:26 am

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   Expand viewCollapse view
    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
Kalimo
 
Posts: 1
Joined: Fri Apr 26, 2019 1:13 am

Re: Merge multiple CSV with same Header

Postby Villeroy » Fri Apr 26, 2019 5:01 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26858
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest