Device Magic to Excel

[BStahl1956]

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 location manager completes the daily report and it is uploaded to the Device manager website in our business location.  It is then transformed into a chart for review.  It can also be exported (downloaded) in either a CVS or XLSX file. I have no control over the format from Device Magic. 

I am trying to control the formatting of the downloaded file when it is opened in Excel and I am not having any luck.  I have tried using a template that opens when Excel opens, but after I click on the downloaded file, both the template and the file opens.  

Importing or exporting the data in the downloaded file seems too cumbersome and to many extra steps in the process.  

I will do this at least once a month for each of 4 locations. 

I also need to sum several of the columns.

Does anyone have an idea of how I might set column widths and limit the number of columns and rows when opening the downloaded file - either the CVS or XLSX versions? 

[#2]

@BStahl1956 

One thought: try just importing into an existing workbook the data from that Device Magic exported file. 

On a very regular basis I export data from Fidelity having to do with an investment portfolio. It is exported as an Excel file (although the option exists to do it as CSV). I open that file in Excel, but then use the FILTER function to import the data into a formatted section of my active workbook where I can do whatever analyses of specific elements I wish.

The single formula that brings in the data is this:

=FILTER([filename.xls]Worksheet1!$A$5:$T$99,[filename.xls]Worksheet1!$A$5:$A$99<>"")

Which basically says FILTER in all rows and columns from $A$5 to $T$99 where the cells in column A are not blank. "filename" only contains data in a subset of those rows, I just specify up to 99 rows to allow for the file to change in size.

In other words, I don't try to format the exported file; I do all the processing and formatting on a stable workbook sheet into which I've brought (via that formula) the data from the exported data.

So that's just a possibility. (Or did I misunderstand what you wrote, and that's actually what you've been trying?)

[#3]

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

[#4]

Thanks. I was trying for a very simple solution. I have resorted to opening my template worksheet and then the DM excel file and copy and pasting the data from the DM file to the template file and renaming that file.

[#5]

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?