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: 223
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
Volunteer
 
Posts: 3612
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 2 guests