Multiple csv/ods files into individual tabs in single spreadsheet [closed]

[sambrookjm]

I’ve been attempting to import multiple csv files into an ods spreadsheet. I ran across the answer at the page (Merge multiple spreadsheets files into one) which says “Do it manually.” That works if you have 12 csv files, or if you only need to do it once. I have about 50-60 csvs, and I will need to do this multiple times, so in this case automation will be required.

I also tried the Macro at the top of the page here ([Solved] Multiple import csv/txt to Calc - save as ods (View topic) • Apache OpenOffice Community Forum). This code seems to import only one csv/ods file into the sheet, rather than all of them in a directory. The code beneath it, by Villeroy, converts each of the csvs into an ods file after you’ve made a database of your csv files, but does not import them all into the same spreadsheet.

How would I go about importing each csv (or ods, if easier) into an individual tab in an ods file? With the 50 csv files, I would like a spreadsheet with 50 tabs. Not everyone I work with has a database program installed on their machine; in fact, I had to ask the admins here to install Base on my machine specifically so I could try Villeroy’s suggestion, so please try to avoid any answer that requires using Base.

Thanks for the help.

[JohnSUN]

The general macro algorithm can be like as this:

Sub collectCSV
Const FILTER_NAME = "Text - txt - csv (StarCalc)"
Const FILTER_OPTIONS = "59,34,33,0,,0,false,false,true,false,false"

How to create a string of FILTER_OPTIONS see here and in this answer

Dim sPath As String, sFileName As String, sSheetName As String
Dim oDoc As Variant, oSheets As Variant, oSheet As Variant
	GlobalScope.BasicLibraries.LoadLibrary("Tools")	

What is the Tools library see here

oDoc = CreateNewDocument("scalc")
oSheets = oDoc.getSheets()
sPath = CurDir
sPath = InputBox ("Please enter a folder name:","Source folder", sPath)

You can use any other method to get the folder name with your files into the sPath variable

sFileName = Dir(sPath + getPathSeparator + "*.csv",0) 
Do 

How to use the Dir function in a loop, see here.

    	sSheetName = CheckNewSheetname(oSheets, _
    	        	GetFileNameWithoutExtension(sFileName, getPathSeparator))
    	oSheets.insertNewByName(sSheetName,0)
    	oSheet = oSheets.getByName(sSheetName)
    	oSheet.link(ConvertToURL(sPath + getPathSeparator + sFileName), _
    				sSheetName, FILTER_NAME, FILTER_OPTIONS, _
    				com.sun.star.sheet.SheetLinkMode.VALUE)
    	oSheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
        sFileName = Dir()
    Loop Until sFileName = "" 
End Sub

How to use the link and its modes to import sheets into a spreadsheet Villeroy explains here.

[sambrookjm]

Thanks so much! That’s very helpful, and it almost works perfectly. I just have one further question. I looked at the link you included for the FILTER_OPTIONS, as my separators are a comma followed by a space. If I’m reading the documentation correctly, the 59 in your FILTER_OPTIONS line should be switched to a 44/32/MRG, for the combination of a comma (44) followed by a space (32). Doing that causes the headings to be broken up at every space, though. How do I correctly use the /MRG token?

[JohnSUN]

Sorry, I have never before encountered such a complex text file format, so I can not advise anything. Can you manually upload this CSV file by setting the import options so that the download result meets your expectations? In this case, you can use the macro in the second link - it will return the generated string of filter options to you. Otherwise (if the file cannot be loaded manually), the macro will not be able to do this either.

[sambrookjm]

The header line is something like “A, B, C, D1 D2 D3, E, F1 F2 F3, G, H” Changing the 59 (semicolon delimiter) to a 44 (comma delimiter) makes the column headers

A B C D1 D2 D3 E F1 F2 F3 G H (edit - each letter on their own line, all but A with leading space)

which is close enough to what I’m trying to do to be acceptable. Replacing 59 with 44/32/MRG, which I thought would work, made no leading spaces in any of the header columns, but D2, D3, F2, and F3 were in their own columns

[JohnSUN]

In this case, it may be worthwhile to import data using only a comma as the field separator. In this case, “D1 D2 D3” and “F1 F2 F3” will be single columns. A further separation of these columns into separate fields can make a macro, rather than an import filter.

[sambrookjm]

After quite a bit more messing around, that’s what I did. I ended up just using the comma (44) as the delimiter, since the /MRG didn’t seem to be working quite right. Thanks again for the help!