[Calc] Output text file automation: macro or other solution?

[marineris]

I would like to automate creating a text file of one column’s data.

I think this post and solution solves it, but I’d need to modify the code (I don’t need every column) and I’m a beginner so I don’t know how to implement any of it!

Any help would be appreciated!

============
I wrote a bunch of details out before finding that post/solution, so only read on if you need/want more detail…

The context is I am creating m3u files (normal text file but with extension *.m3u instead of *.txt) to transfer iTunes playlists to a USB for my car. My Calc sheet is already set up to read and update from the text file iTunes exports using Link to External Data, each playlist in its own eponymous sheet. I then have a column with a formula to alter the location path slightly. I copy this column into a text file named “[Playlist1].m3u”. I’ve been doing these steps manually, but as there are 20+ playlists, I would really like to learn how I can automate the process.

I couldn’t find how to only get one column, so have downgraded my expectation to having to create a separate sheet holding only that column’s data. My first attempts with macros didn’t work. I tried to record a macro to:

1. Copy the sheet’s name (using Rename Sheet, ctrl+c, enter)

2. “Save a Copy” (Save a copy, change Save as type to Text CSV, click File Name, ctrl+v and type".m3u", enter, enter, enter).

Neither of those steps worked and being a total newbie to macros, I’m not sure if I’m doing it wrong or if I’ve run into the limitations of macros. The code shows for step 1 it hardcoded whatever the current sheet’s name is (effectively always renaming the sheet to that) and did not record anything once the Save a copy dialogue box opened.

Let me know if macros are the way to go or another solution is applicable.

Version: 7.2.5.2 (x64) / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US

Calc: CL

[JohnSUN]

In principle, your idea can be implemented in this way - a macro that will iterate through all the sheets of the current workbook and save a certain column to a file with the name of the sheet. But let’s try to take a step back. Where does Calc import sheet data from? From text files? Perhaps it makes sense to abandon Calc as an intermediate tool? Take the original file and immediately create the modified file.

[marineris]

I already felt that I may not need Calc but was drawing a blank as to where to start without it. Writing a reply to you made me re-think every step of my process and question any assumptions I initially had made. In doing that, I found the answer is insanely simple, I’m kicking myself. Thank you for the reality check!

Solution (if curious, or for the rare person in the same situation stumbling on this in the future):
For this very specific situation, it is solved in 2 steps:

1. A simple google search provided the key information that iTunes CAN export playlists as .m3us. I was using its default .txt playlist export which is a text file with 31 columns of metadata (strings of variable length or no data at all, so I don’t think @JohnSUN 's linked script would’ve worked?) for every song in the playlist. Changing the ‘save as type’ to .m3u provides just the path of the songs in the playlist, which is all I ever wanted.

2. From there it is a simple Find and Replace since I just need to change the root folders of the path - easily done in powershell, or an even better solution: Notepad++ can batch find and replace in all text files of a whole folder at once!!

[Wanderer]

In the macro you linked above you have to adapt the path /home/mau/ with the folder. you want to use.

Then there is a loop over all columns

    For c = 0 To UBound(data(0))

For testing to save one column try with a fixed value (as 0 is the first column 4 should be column E)

    For c = 4 To 4

If this works one can replace for/next with an if clause

    if 4 <= UBound(data(0))

Then you may adapt the name of the exported file.

[marineris]

I found an easier solution, but thank you for the modifications!

[joshua4]

I think LO BASIC Print to file is the way to go here. I happened to know that I needed some serious automatic CSV export coming this summer, though, so I spent a little time here on a solution that uses CSV exporting. It may act as an example for similar situations where Print to file isn’t ideal. It executes the OP’s original automation pattern by inserting sheets that only repeat the first column of their “master” sheets, saving those as CSV (which means text file here), and removing the inserted sheets.

SaveColumn.ods (11.4 KB)

[marineris]

Thanks for thinking this through earlier than you needed! If I had not found iTune’s innate m3u option, I would have used this.

For fun I wanted to understand the code enough to make the simple switch of exporting col C instead of col A, but even running the original file unaltered didn’t appear to work. You note in the macro " Export to the same directory As the original workbook (ODS)", but on pressing the button no new files are in the same folder as the workbook. Could the output path have a mistake and it is putting the .m3us elsewhere?

Edit: BTW, I tried the Print to File → print only selected text option, but it was unreadable. Searched quickly to troubleshoot, found that Print to file creates a print file (.PRN) that can be printed later, so not usable text.

[joshua4]

Hmmm. I have the macro working and saving files in the same directory as the ODS, even when that is not the default directory. Here are the two systems I’ve tried it on:

1. Linux Mint 20/Calc 7.2.2.2
2. Windows 10/Calc 6.4.2.2

I did notice that at first on the Windows machine I didn’t have macro security set correctly, and while running the macro from the IDE I would get a message, when trying to run the macro from the button it would just fail silently. You might check on your macro security levels under Tools>Options>LibreOffice(a header at left)>Security(an entry at left)>Macro Security(a button at the right).

Changing the macro to save column C would require just changing A to C on one line, so you’ll have that in a jiff once it’s up and running.

[marineris]

Yes, it was failing silently. I lowered the security temporarily and it worked.
I had already identified where the column change would be so once the security was lowered I successfully exported col C . Thank you very much and good luck with your CSV work!