Forum Discussion
Device Magic to Excel
I am using an online platform, Device Magic, for collecting daily reports from location managers. The reports are pretty simple, numbers of work events completed and the income for the day. The loc...
The alternate method is to import the csv and xlsx files from the Device Manager in to your excel file.
You could use Power Query which will make things easier and save a lot of time. You'll have to define what column to keep, which ones to remove, what format would those columns be in, add any new columns and define a calculation for that. Then you are set. Just refresh every time you have the exported file in hand.
If it sounds to cumbersome, here is short way to do it:
1. Import the data in to your excel sheet (in data tab - from text/csv)
2. Click on Load after selecting your file
2. Do the formatting on the columns you need
3. Add or remove any column you need and define a calculation (in your case, summing of columns) in the first cell. It will flash fill down the column
You are set!
Next time you get the exports, go to Data tab and click on refresh. Note the exported files from Device Manager should have the same name.
Or you could a small change while importing:
1. In the Data tab, click on Get Data --> From File --> From Folder
2. Select the folder
3. Select Combine and Load
Then the rest is same
You could use Power Query which will make things easier and save a lot of time. You'll have to define what column to keep, which ones to remove, what format would those columns be in, add any new columns and define a calculation for that. Then you are set. Just refresh every time you have the exported file in hand.
If it sounds to cumbersome, here is short way to do it:
1. Import the data in to your excel sheet (in data tab - from text/csv)
2. Click on Load after selecting your file
2. Do the formatting on the columns you need
3. Add or remove any column you need and define a calculation (in your case, summing of columns) in the first cell. It will flash fill down the column
You are set!
Next time you get the exports, go to Data tab and click on refresh. Note the exported files from Device Manager should have the same name.
Or you could a small change while importing:
1. In the Data tab, click on Get Data --> From File --> From Folder
2. Select the folder
3. Select Combine and Load
Then the rest is same
My columns are all fine - it's a row I need to delete. I also need column formatting. I'll give the formatting a try but what about the row?