Ask Your Question
0

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

asked 2018-10-09 16:40:50 +0200

sambrookjm gravatar image

updated 2018-10-15 15:29:24 +0200

I've been attempting to import multiple csv files into an ods spreadsheet. I ran across the answer at the page (https://ask.libreoffice.org/en/questi...) 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 (https://forum.openoffice.org/en/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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-10-09 19:46:30 +0200

JohnSUN gravatar image

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.

edit flag offensive delete link more

Comments

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?

sambrookjm gravatar imagesambrookjm ( 2018-10-12 15:07:53 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2018-10-12 16:33:06 +0200 )edit

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

sambrookjm gravatar imagesambrookjm ( 2018-10-12 16:51:43 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2018-10-12 17:08:49 +0200 )edit

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!

sambrookjm gravatar imagesambrookjm ( 2018-10-15 15:28:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-10-09 16:40:50 +0200

Seen: 324 times

Last updated: Oct 15 '18