Import CSV data with units into Excel

Question

I've got a bunch of CSV files that contain values with trailing units. This makes Excel think it's text so I can't sum them in a pivot table.

The data looks something like this:

Date; Text; Amount;
2016-12-11; Gas Station; -152,00 kr
2016-12-10; Food Stare; -130,00 kr
2016-12-09; Payment; 500,00 kr

I've managed to "solve" it by inserting a new column where I strip the unit and convert the text to a number using =NUMBERVALUE(SUBSTITUTE(C2; " kr"; "")) and then telling Excel how to format that column and the value in the pivot table. But this feels really hackish.

Is there any way I can import the data and tell Excel that it already has the correct unit so I can use the data from the CSV directly?

Removing the units from the CSV files is not an option and I'd rather stay away from VB unless it's absolutely necessary.

Answer 1

If you have a sufficiently up-to-date version of Excel, one of the best ways to do this is to use PowerQuery.

You can use PowerQuery to import the data from 1 or a load of CSV files, you can also transform the data column as you need to.

---

Create a new data query: Data/New Query/From file/From Folder. Specify the folder path. You get a preview, click on edit. Filter on extension to .csv and on any filename pattern you want. You currently have a list of files.

To get the data, delete the columns in the file list you don't want. Now insert a custom column with the following formula:

Table.PromoteHeaders(Csv.Document([Content]))

That is the best way to get the content though you can sometimes get the result you want simply by expanding the Content column. But using the custom column makes it easier to keep the file metadata columns (e.g. Date accessed) if you need them.

Then you may need to dedupe the data if the file contents overlap.

So most of that is done with point and click with no programming, no harder than working with a spreadsheet.