I have a single job that iterates through a bunch of files based on dynamically loaded contexts.
It then gets whatever's in these files and uploads it to a sheet.
My challenge currently is with the fact these files will have a different number of columns - how can I add via a context variable "NumColumns" a dynamic schema option so each of the files has the correct number of columns uploaded?
I am thinking of setting the tFileInputDelimited schema to a max number of columns and then using tMap to dynamically only pull the number of columns available for the current file.
I am very novice with Talend and Java so not sure how and if I can do this transofrmation in tMap - any ideas?
This tutorial may work for you. It might make sense to extend the ideas here and write a routine to handle the processing of the logic, but that is up to you. However this shows a relatively easy way of achieving the majority of your goal
When you say "sheet" are you talking Excel or just another CSV type file? There are ways around this, but you may end up having to use a bit of Java. If you are just outputting to a CSV (or similar) file, this example may help. It was actually written to deal with a scenario where every row may need to be added to a different file (depending on a key in the file). However, you can ignore the multiple file part and look at the section where the file content is built. If you build the structure manually you could add checks for null fields and remove them.
Of course, the tutorial above will only work with flat file creation. If you are wanting to do something with Excel it will still be possible, but may require a bit more thought.
OK, my thinking was regarding avoiding a fixed schema. While Talend is an awesome tool, sometimes the functionality to make it "drag and drop" and "easy" are somewhat limiting. Loading to a CSV file you can take total control of the columns being loaded using Java. You simply need to create a String of columns formatted in a suitable format for CSV. When you take control of that yourself, you can handle dynamically removing null columns. The part of the tutorial I was aiming at you was the building of the CSV row. I didn't actually include any logic to dynamically remove columns, but that is easy. The bit I was hoping you would latch onto was the creation of the rows and how easy it is to write to a flat file with a tiny bit of Java.
That aside, it does not really suit your case here. The tGoogleSheetOutput I have not used, but assume you are required to use a fixed schema. If you really do not want to add null columns at the end (or cannot for fear of breaking your file) you can use the Google API for manually creating your data row in a similar way to above....although you will have to code this yourself. Take a look here (https://developers.google.com/sheets/api/) for an idea of what you will need. You can call any web API available using Talend components and all you need to think about after that is formatting JSON or XML and maybe dealing with OAuth authentication. These are big steps to start with, but really beneficial in the long run and open a lot of doors.
I use the tRestClient or tRest component (there are subtle differences between the two).
If you can shift your columns to the left and use the component you mentioned earlier, that will certainly be easier, but may leave you open to your formula issue with nulls. However I am sure there is another workaround for that, maybe within the spreadsheet.
Don't be afraid to use third party APIs with Talend. That is one of the best things about it from my perspective. It allows you to do things in a simplified manner, while still allowing you to do some technical stuff. Good luck