This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Welcome! I think the answer to your question really depends - is this a one-off transformation or will it be a recurring process? If a recurring process, does the data come in the same format each time (same number of rows in each section, same header names, etc.)? What data is important to capture on this input file, and what data is okay to be dropped (if any)? What's the ideal output?
The forms come from a recurring process and always in this format, so same headers, same number of attributes and such. The re-formatted version will be used for statistical analysis. As I mess with it more, I am inclined to drop the headers as they contain product specific information which can also be distinguished by batch. So the heart of the form lies with the attributes and data.
The way I would suggest approaching this is by splitting your input data into chunks and processing each separately. Then after all processing is complete you can union it all back together. Without knowing the finer details of your data or what the output should look like, I put together this (very) rough example of what you could do. I used a few multi-field and multi-row formulas to process the data, then filtered based on 'section' in the form. From there I used Select tools to grab columns separately, standardize naming, and then union them back together. Your middle section there is a little tricky and I haven't nailed that one down yet, but hopefully what I started doing proves helpful to you in some way. My hope is that this gives you some ideas and a place to start. It's not the prettiest solution, but one thing I've learned is that if it works it works