Alfred Workflow to convert .xls file to .csv

[Cauchy]

 

Hello guys, 

 

I ofter work with data and do lots of data cleaning. I often need to manually convert .xls, .txt file into .csv file. It would be great if there is a workflow, for example using VBA or command line, to automatically do such converting. Unfortunately I am not familiar with VBA or other tools to accomplish this task. Any suggestion? 

 

Thanks! 

[vitor]

There’s xls2csv, which should be able to do it easily. If you need help in making it a Workflow, I’ll need you to provide an XLS file for testing.

Also note you don’t convert TXT to CSV. Both CSVs and TXTs are plain-text files. A properly formatted CSV can be changed to TXT (and vice-versa) by changing the file extension.

[Cauchy]

Hi vitor,

 

Here is a XLS file for testing.

https://github.com/chenx2018/xls2csv_alfredWorkFlow

 

Could you please help me make a workflow?

 

Thanks for your help!

[Martin Packer]

You mentioned VBA, which suggests you have Excel. Can’t you drive Excel to save as CSV?

[Cauchy]

3 hours ago, Martin Packer said:

You mentioned VBA, which suggests you have Excel. Can’t you drive Excel to save as CSV?

Since I have a ton of XLS files, it is hard to use Excel to convert them all

[deanishe]

14 minutes ago, Cauchy said:

Since I have a ton of XLS files, it is hard to use Excel to convert them all

 

Which is why @Martin Packer is suggesting you script Excel to do it…

[Martin Packer]

Yes. Hazel, for one, could watch a folder for XLS files and automate converting each one - perhaps with help from Keyboard Maestro. (Alfred, though very nice, isn’t the only Mac automation tool.)

[vitor]

9 minutes ago, Martin Packer said:

Alfred, though very nice, isn’t the only Mac automation tool.

 

True. However…

 

9 minutes ago, Martin Packer said:

Hazel (…) with help from Keyboard Maestro.

 

That’s two extra apps constantly running and two more apps you need to pay for. Yes, Alfred isn’t the only automation app, but there are real benefits to picking one automation tool (whichever it is) and trying to adhere to it as much as you can. If you have three, there’s a good chance you’re doing something wrong and not using the others to the fullest.

[nikivi]

41 minutes ago, vitor said:

but there are real benefits to picking one automation tool (whichever it is) and trying to adhere to it as much as you can. If you have three, there’s a good chance you’re doing something wrong and not using the others to the fullest.

 

I disagree. There are many automation tools that do certain things too well. Alfred is a launcher, it displays things and lets you act on them. KM let’s you easily make and call macros (any code/action you want to run either triggered by you or some event). And Karabiner glues everything together and provides an interface to it all. 

 

Same for programming languages or any other tools. There’s a quote related to this I really love and live by. 

 

> Only when you master a tool, do you learn when not to use it.

Edited October 17, 2018 by nikivi

[deanishe]

1 hour ago, vitor said:

If you have three, there’s a good chance you’re doing something wrong and not using the others to the fullest

 

Sure, if you're using multiple apps that are designed to do the same thing.

 

But I don't think that applies to apps designed for different (kinds of) tasks because their entire point is to make certain classes of tasks a lot easier than building the whole thing from AppleScripts, Launch Agents and the other bits and pieces macOS gives you. I don't do a whole lot with KM, but the things I do use it for would be a huge PITA to do without it. Hazel much less so, but it's worth it just so I don't have an extra 40 Launch Agents to manage.

[vitor]

1 hour ago, nikivi said:

There are many automation tools that do certain things too well.

 

How can a tool do something too well? “Too well” implies the tool does its job so effectively it becomes inappropriate for the job. I’ll need an example, because I can’t think of any case where that makes sense.

You may argue that some tools make certain tasks so practical that you’d rather do it in them. That’s valid, but that doesn’t mean your other tool can’t do the task of the first with a bit more effort (i.e. using it to the fullest). Sure, you can have fifteen automation tools running at once, using each for the tasks it excels at, but maybe you could also reduce those fifteen to five, and reap benefits to your wallet and the stability of your system.

I’m not saying there’s never a use case for many automation tools. What I am saying is that if you have many, maybe you should take a harder look and confirm if you really need all of them. The answer, for most people, is that probably you don’t.

Your case is particular because you enjoy tinkering. Even if you don’t need (in a practical sense) to use several tools, you’ll do so for the enjoyment and exploration. I’d wager that’s not the case for most people that use multiple automation tools, and I’d wager even more that the number of tools a heavy automator uses (out of necessity, not tinkering) is, as a rule, inversely proportional to their ability to code and/or think like a programmer. I make this distinction because I’ve seen people who can code but then overegineer solutions because they didn’t take the time to stop and evaluate what the result (not the process) should be. 

 

1 hour ago, nikivi said:

Karabiner

 

Is a keyboard customisation tool, not an automation tool. Even if you can use it for automation, that’s not what it is and you’re bending it to your setup. You can also use a web browser for automation (bookmarklets, headless mode) but it’s not an automation tool in and of itself because it’s not its primary goal. My comment concerned tools whose purpose is to automate others.

 

1 hour ago, nikivi said:

Only when you master a tool, do you learn when not to use it.

 

This is a deepity. I don‘t need to master a crossbow to know I shouldn’t use it to write a novel. I don’t need to master a paintbrush to know not to use it to kill a lion. I don’t need to master Microsoft Word to know I shouldn’t use it to code fractals. I don’t need to master vectr (or a multitude of other vector drawing tools) to know they’re not good enough to make a mandala. In some of these cases it’s by mastering the task or a different tool that I can in a few minutes recognise another tool is insufficient; in the others I have no need to master either because the design of the tool makes its strengths and weaknesses obvious. In fact, we might even argue that if we need to completely master a tool before its shortcomings are clear, it might be a badly designed tool¹.

 

---

1. But I will not say that right now, as I’ll need to first think about it some more and try to disprove the hypothesis.

Edited October 17, 2018 by vitor

[vitor]

@Cauchy Going back to your issue, I found a few xls2csv converters but they all got into some kind of snag running or trying to convert your file (it doesn’t help that there are so many with varying degrees of quality), and since I don’t have the tools (Excel, access to multiple XLS files) to properly test and debug, I’ll have to abandon the effort for now. As suggested above, automating Excel (or LibreOffice, as it seems like it can convert XLS to CSV from the command line) might be your best bet.

[deanishe]

@Cauchy I use the Python xlrd library in I Sheet You Not. It does a pretty good job of reading .xls(x) files.

 

I've tested it with your file, and it can read all the data perfectly.

 

The problem is that the internal representation of the data are not the same as the formatted values you see in Excel—or what you'll want in your CSV file—so you need to write your own code to format the data for CSV output, such as using the correct date format.

 

You can probably piece together any formatting code from ISYN.

[nikivi]

2 hours ago, vitor said:

Is a keyboard customisation tool, not an automation tool.

 

In my view, every tool is an automation tool. Even Affinity Designer is an automation tool in that it saves you time doing designs. And I think all ‘good’ tools are like that, they save you time. True automation however means doing something without my input at all and that’s hard to do with physical things (today at least). 

 

Wanted to write a blog post post about it but never got to it. I still need to release my Karabiner article. ?

[vitor]

36 minutes ago, nikivi said:

In my view, every tool is an automation tool.

 

I was going to include that point in the original post, but refrained from it for length. Yes, in theory every software tool is an automation tool because they automate the flipping of ones and zeroes, but that’s not a useful metric. Words have meanings but those meanings are contextual. We could call them “bananas” instead of “automation tools” and still have a productive discussion, as long as we both agreed to that nomenclature.

I specified what I mean by “automation tool”. I defined them as “tools whose purpose is to automate others”. Affinity Designer and Karabiner do not fit that definition and thus are not automation tools in the scope of this discussion. If we were discussing the meaning of “automation tool” then we could certainly argue about it, but in this case it was a shorthand for a point about a specific type of app.

[nikivi]

17 minutes ago, vitor said:

Karabiner do not fit that definition

 Karabiner does. In fact it’s the app that saved me the most time in life by cutting access time to apps/macros/workflows. So yeah karabiner does ‘automate other apps’ although not directly.

[vitor]

4 minutes ago, nikivi said:

Karabiner does.

 

It doesn’t. Again, the definition is (emphasis added): tools whose purpose is to automate others. And like you said (emphasis added):

 

5 minutes ago, nikivi said:

karabiner does ‘automate other apps’ although not directly.

 

“Not directly” because it’s not its purpose and that’s the definition I presented. It could be the best automation tool in the world; if it was not made for automation then it doesn’t fit the definition.

[Martin Packer]

This topic has gone off on a(n interesting) tangent. ?

As the OP has Excel I would say automating it would be the best way - if feasible. I’m not sure whether AppleScript automation can get the OP the whole way. I’m more concerned about driving Excel than I am about getting a list of files to work on.

Some of these other tools could be useful. I’ve yet to see evidence that Alfred has much of a role to play here; Somebody educate me if they think it does.

[deanishe]

8 minutes ago, Martin Packer said:

I’ve yet to see evidence that Alfred has much of a role to play here

 

It doesn't. Unless you want to use it to choose the Excel file(s) to convert. Alfred, after all, is a launcher, not an automation tool.

 

11 minutes ago, Martin Packer said:

if feasible

 

That's why we're talking about Perl and Python scripts: Vítor doesn't have Excel and I don't know how to write VBA. Also, using a bloated app like Excel or LibreOffice for automating conversions is a terribly slow and awkward way to go about it if you can use a small library instead.

 

In any case, I think all the code @Cauchy needs can be pulled out of the workflow I linked above. He just has to put it together in a script and plug that into an Alfred File Filter/a Launch Agent/Automator app/Hazel/whatever.