About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Generate CSV from range of excel rows

E
Userlevel 2
Badge +1

I have a spreadsheet in Excel that is periodically updated throughout the day with new rows. Each row includes a date. What I’d like to do is, once monthly, export rows with dates from the previous month as a CSV and email it.

I figure my Zap will utilize Schedule by Zapier and (maybe) @ikbelkirasan’s excellent Advanced Utilities integration. I’m just not really sure how to go about making sure I’m only capturing the rows from the previous month. What do you think your approach to this would be?

I’m not opposed to thinking differently about, what is essentially, a scheduled report. Though I would prefer to keep the data in Excel. I could be talked out of that.

icon

Best answer by nicksimard 7 June 2022, 02:40

View original
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

14 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @ericg 

Good question.

Maybe try Transfer by Zapier?

https://transfer.zapier.com/new

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Troy Tessalone
Userlevel 7
Badge +14

@ericg 

Alternatively, you could use Airtable instead of Excel: https://zapier.com/apps/airtable/integrations#triggers-and-actions

Airtable has Views.

Views have Filters (e.g. Date = Last Month)

Views can be shared.

Views can be exported as CSV (not programmatically)

Airtable has APIs for exporting records.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
E
Userlevel 2
Badge +1

@Troy Tessalone, I’d considered AirTable and it looks pretty rad. Just trying to avoid another subscription. Checking Transfer out now. Forgot that existed. Will post back with my findings. Thanks!

Troy Tessalone
Userlevel 7
Badge +14

@ericg 

Depends on your requirements and number of records, but Airtable has a free plan.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
E
Userlevel 2
Badge +1

Tried Transfer. Going from Excel to Excel (both same account). Ran into an “unexpected error.”

I just realized that, another reason I need to stay in Excel, is because the worksheet I would export to CSV is also used in another worksheet to generate a different type of report. If I were to keep that in place, I think the best I could do is have the rows zapped over to both Excel and Airtable. 

http://parabola.io seems like it could be a potential tool for this, too.

T
Userlevel 3
Badge +4

Hey @ericg! Were you able to figure out a workflow here? 🤔

E
Userlevel 2
Badge +1

@TamRazzleDazzle, I have not been successful in automating my entire process and am not sure I will. Maybe time for a proper ERP. 
 

That said, Airtable is a good solution for what I originally needed in this thread. 

nicksimard
Userlevel 7
Badge +11

Hey @ericg!

This is a little workaround I came up with that *should* do the trick.

With Google Drive (and Dropbox) you can enter text into the Upload File action:

 

Specifying the extension means that it will become a CSV:

 

Now, this is the important part. When rows are added to your spreadsheet and they include a date, will that date stay the same? And will the row always remain?

In other words, could you trigger on rows being added (and filter on the date having to include something that indicates it’s from the month you’re interested in) and then add them to Storage or Digest by Zapier?

Or, is it the case that a row might be added, have the month you want, but by the time you’d want to create the CSV it may have changed or been deleted?

That will determine whether my idea can work. Let me know!

E
Userlevel 2
Badge +1

@nicksimard, the row date would stay the same and, once a row is added, it will remain in the sheet.

nicksimard
Userlevel 7
Badge +11

Hey@ericg!

Thanks for confirming.

It seems like my idea could work. After thinking it through some more, I realized it’s kind of complex and there are a couple of sticky points.

If you’re up for the challenge, let me know and I’ll lay it all out so you can take a stab at it :)

jesse
Userlevel 7
Badge +9

Hey @ericg! Just checking in here to see if you saw Nick’s reply. Let us know if you’re up for partnering together to solve this one!

E
Userlevel 2
Badge +1

Hey@ericg!

Thanks for confirming.

It seems like my idea could work. After thinking it through some more, I realized it’s kind of complex and there are a couple of sticky points.

If you’re up for the challenge, let me know and I’ll lay it all out so you can take a stab at it :)

Howdy! Sorry for the delay. Other projects have taken priority. That said, I’m willing to give something complex a shot but I’m not sure when I’ll be able to get to it. Appreciate your help!

nicksimard
Userlevel 7
Badge +11

Hey@ericg,

I should preface this by saying that I’m far from an expert when it comes to Excel, but here’s a pretty concise way you could do it.

Based on this, I figured out how you can auto-add a formula to new rows in Excel.

In Excel

I used this formula…

=IF(MONTH(C2)=MONTH(TODAY()),"Yes","No") 

… to see if a date is within the current month:

Trigger: Excel — New Row

This part is straightforward. trigger on new rows being added to your desired spreadsheet/worksheet.

Action 1: Filter

Only continue if that formula field says “Yes”:

Action 2: Digest —Append Entry and Schedule Digest

In this one step we append the items to the digest as well as define when we want it to be released. Midnight on the 1st is right after the very end of the previous month, so that makes sense to release.

NOTE: You can also set up a separate Zap to release this, by triggering it Schedule by Zapier.

(Optional) Delay by Zapier

If you don’t want to send the email right away, you can add a Delay by Zapier step, and specify the number of hours to delay the next action by.

Action 2: Dropbox — Upload File (you could also use Google Drive)

This step creates the CSV by using the digest you just released. Make sure you add the header row by typing the column names as they’ll appear in your digest.

Action 3: Email

Now you can email the CSV file, by mapping this field into your Attachments field:

 

Whew, ok. Hopefully this helps! It’s ONE way you could tackle this, and without making the Zap too complex :)

E
Userlevel 2
Badge +1

@nicksimard Wow! This is clever and efficient. I could see all sorts of uses for this sort of thing. I’ll add this to my workflow! Much appreciated:-)

Terms & Conditions