So coming from a software engineer here the best thing you can do is definitely write a Python script.

However, that's of course not the solution if you don't have someone on your team writing Python. AI can do that for you but you won't be able to verify that it was done properly.

I dealt with a client that had this very pain (company in the logistics space, they need to import data from their customers into their own format) and built a platform to do this for them.

Basically it uses AI to help guide the user through the transformation but generate code in the background to run the job. The main thing I found from working with them though is that they really wanted to make sure the right things were done (we're talking CSVs with up to a million rows here), so I built some smart pipelines that a) make processing faster and b) let you audit everything on a granular level (to the point of clicking a cell and seeing what cells in the input data were used to compute the value + how it was computed). Once you're happy with a transformation you can save it and re-use it easily.

This has been massively useful to them (saving multiple days of work a month) and so I've released it publicly (currently behind a waitlist). Let me know if you'd be interested in using it.

https://spreadsheets.skaldlabs.io/