Help

Automating Data Transformation for import

Solved
Jump to Solution
3314 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Sobhy_Sonbol
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Fellow Community Members, 

I get these inventory summary sheets from my vendors that I first have to run a macro on in Excel to transform the summary to individual records to update my inventory in airtable. The sheets are for dresses so each row has the style and color and then the columns have the sizes and under each size is the stock amount. Each style color and size is an SKU in my base so I cannot import the data directly before transforming it in Excel. My question is there a way for me to automate the importation process without having to run transform the data each time or is there an easier way to do this process?

I have attached an example of the sheet i get from the vendor and a link to my base. I would greatly appreciate the community's input.    

I attached a sample file and a link to my base 

https://airtable.com/shrRSgvhQ1Y1naavE

Screen Shot 2023-04-19 at 11.38.37 PM.png

Screen Shot 2023-04-19 at 11.37.10 PM.png

1 Solution

Accepted Solutions
Sobhy_Sonbol
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks for everyone’s help this is the solution I arrived at to automate the process     
1. I transformed the data using excel‘s power query and saved it as a template 

2. I wrote a script that everytime I add a file with a specific name to a certain folder that opens up the excel template , refreshes the data and exports it as a CSV to another folder 

3. when that new csv file is created it triggers a zap to export the new data to airtable 

See Solution in Thread

3 Replies 3
FlywheelConsult
6 - Interface Innovator
6 - Interface Innovator

You could use Zapier to trigger when a file is received which can be done via receiving an email or a file added to Dropbox/GoogleDrive/OneDrive that then runs a script to split out the data. Zapier can then send that data to Airtable.

Hard to say without knowing exactly what you're doing with the macros really.  I'd be very surprised if you couldn't get it done with formulas and automations though!

I assume you've tried importing the CSV to Airtable and modifying the data on Airtable before?  What issues did you face when attempting that?

Sobhy_Sonbol
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks for everyone’s help this is the solution I arrived at to automate the process     
1. I transformed the data using excel‘s power query and saved it as a template 

2. I wrote a script that everytime I add a file with a specific name to a certain folder that opens up the excel template , refreshes the data and exports it as a CSV to another folder 

3. when that new csv file is created it triggers a zap to export the new data to airtable