Automating Data Transformation for import

[Sobhy_Sonbol]

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

[FlywheelConsult]

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.

[TheTimeSavingCo]

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 · Accepted solution]

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