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 [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("")
    rem to not use the folderpicker set the folder here and uncomment:
    rem sFolderpath=converttourl("C:\Users\kai\Desktop")
    if sFolderpath="" then
    oDialog = CreateUnoService("")

    If oDialog.Execute() = 1 Then
    sFolderpath = oDialog.getDirectory
    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("")
    ooutputStream = oSFA.openFileWrite(outputfile)

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

   oTextinputStream = CreateUnoService("")
   inputfile = converttourl(Fileurl)
   oinputStream = oSFA.openFileRead(inputfile)
   stringA = oTextinputStream.readLine()
   if Headerwritten=0 then
   oTextoutputStream.writestring(stringA & chr(13) & chr(10))
   end if
   do while oTextinputStream.isEOF()=0
   stringA = oTextinputStream.readLine()
   oTextoutputStream.writestring(stringA & chr(13) & chr(10))
    end if
    sFileName = Dir()

    msgbox "Saved: " & convertfromurl(outputfile)
    End Sub

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

    '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
       oPropertyValue = createUnoStruct( "" )
       If Not IsMissing( cName ) Then
          oPropertyValue.Name = cName
       If Not IsMissing( uValue ) Then
          oPropertyValue.Value = uValue
       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)
Posts: 217
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
Posts: 3554
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 1 guest