Aligning data across columns from e-mail attachment

Hi ​@abgstv 

Issue may be related to how the original file is formatted.

Is it an XLS or a CSV?

• It needs to be a flat file with headers/rows.
• No grouped rows/columns
• No gap rows/columns

Otherwise, more advanced approaches have to be used to first prep the file data.

Example attached. 
It’s a CSV
 

I think the issue is that the first colum has a row in it’s that’s blank in the other colums. 
Is there a way to account for that? 

I can’t change the formmating of the report I receive unfortuently. 

​@abgstv 

For this to work in a simple way, row 1 would need to be the headers.

Since the CSV is not formatted as a true flat file, that means the file would need to be manipulated before it can be processed as expected in the Zap steps.

That would mean deleting the first 6 rows.

Troy Tessalone wrote:

​@abgstv 

For this to work in a simple way, row 1 would need to be the headers.

Since the CSV is not formatted as a true flat file, that means the file would need to be manipulated before it can be processed as expected in the Zap steps.

That would mean deleting the first 6 rows.

 

Appreciate you letting me know. 
And there isn’t an easy way to remove those rows with Zapier? 
I don’t have control of how the report is generated. 

Looks like I need to look into other options. 

Thanks for the support 

Hi ​@abgstv,
 
We just wanted to see how everything is going with your Zap. Did Troy's recommendation get the job done? Feel free to reach out if you need further assistance with your Zap. We're glad to address any concerns and assist you.
 
We're looking forward to your response.

It did provide me with enough insight to understand that given the limitations of the platforms I have to work with that I likely won’t be able to accomplish what I’d hoped for. 
But that’s not an issue with your product, just limitations of the format the data I want to work with comes in. 

Hi ​@abgstv,

I appreciate your understanding and am glad the insights helped clarify the situation! While the current data format may present challenges, depending on your use case, creative workarounds might still be possible.

Feel free to ask if you're having trouble setting this up or have further questions, or contact our Zapier support team for additional assistance.

I’m open to ideas. 
The main issue seems to be getting the report into a format that can be injested corrected. 
I can’t change the format of how the report is delivered and I, for the most part, only have access to Office 365 Suite so no google suite options will work. This is for work so everything has to work with what I’ve got. 

Hi ​@abgstv,

Please specify the report's current and desired formats to provide the best solution. Also, let us know which Office 365 applications you use and whether Zapier is part of your workflow. If so, mention the Zapier apps involved. With this information, so we can give a more precise recommendation.

My goal is to take a daily e-mail CSV report i receive and use it to update an excel spreadsheet automatically so I can keep track of a variety of ad campaigns. 

The report comes as an e-mail attachment in Outlook. I’m not able to change the format or access the information in different ways except from the API.

Because of the format of the report I haven’t can’t get the columns to align. 

I have the suite of Office 365 including Power Automate and Power BI

The set up I used for Zapier are Outlook, Formatter , and Excel.

I’m a novice user so there may be other zapier apps I could use for this. 

Hi ​@abgstv,

Welcome to the Community.

You can automate updating an Excel spreadsheet from a CSV email attachment using Zapier. First, set an Outlook trigger for new emails in a specific folder. Use Email Parser by Zapier to extract the CSV file, then Formatter by Zapier to convert the data into a readable format. Finally, insert the processed data into your spreadsheet using the Excel "Add Row" action.

Please note that this workflow is a general guide and might need to be adjusted to fit your specific needs. If you need more detailed help setting this up or if you encounter any issues, please let me know. I'm here to assist you.

The issue I’ve run into with that approach is that the formmatting of the report I have to use means the colums don’t align. The report isn’t a flat file and has an extra row in column A. 

My issue is getting the each row to line up with the next. 
Example report attached and an image to show the mismatch when importaned into excel. 

Hi ​@abgstv,

If your report's rows are misaligned in Excel due to an extra row in column A, you may need to adjust the import range to start from A2 instead of A1. If the extra row appears within the data, preprocessing may be required to remove it. Excel's "Text to Columns" feature can also help with alignment.