I am presented with a bit of a challenge and I am unsure if Talend has the ability to do what I want.
My problem is that I need to accept an excel file, whose columns will change, and import that data into a database.
The excel file will have all of the column names listed in the header row.
The format of the excel file will be as follows:
key_id, company_name, company_type, contact1_name, contact1_address, contact1_phone, contact2_name, contact2_address, contact2_phone
Each row will relate to two database tables. "companies" and "contacts" The "key_id" will be used as the surrogate key on both tables. The column names in the database match the column names in the excel file, except for the prefix. So, "company_name" would map to the "name" column on the "companies" table. Similarly, the "contact1_name" excel column will map to the "name" column on the "contacts" table.
One of my challenges is being able to accept 1:N contacts. The above example would have 2 contacts, but if they have 3 contacts, there would be another set of the same columns with a different prefix.
e.g.
key_id, company_name, company_type, contact1_name, contact1_address, contact1_phone, contact2_name, contact2_address, contact2_phone,
contact3_name, contact3_address, contact3_phone
This would be the same pattern for 4,5,6...N number of contacts. Each set of "contact#_*" excel columns would result in their own row in the "contacts" table in the database.
If this would be possible in Talend, that would be a huge hurdle for me.
Unfortunately, that's not my only problem. Along with that uncertainty, another feature I need to work out is how to account for our customers entering in more or less columns for the company information.
e.g.
key_id, company_name, company_type,
company_phone, company_email, company_fax, contact1_name, contact1_address, contact1_phone, contact2_name, contact2_address, contact2_phone
The "companies" table would have all of the possible columns that may be entered in the excel spreadsheet. Only the columns that are present will get inserted into the database. So, the first example I showed would create a row in the "companies" table with "name" and "type" populated, but nulls for the "phone", "email", and "fax" columns, while this example would have all of the fields populated.
I'm not sure how to accomplish either of these tasks using the tFileExcelInput component. When creating a "File Excel" metadata component, on "Step 3 of 4" there is a checkbox to "Set heading row as column names." I am able to use this feature to automatically set the schema for my tFileInputExcel component, but this only allows me to save one possible set of columns. It would be awesome if there was a way to use this function to set the schema for the excel file being inputted on the fly. Ideally, I could set up the possible list of columns to be accepted beforehand, and when the job is run, have Talend determine which columns are present out of the available set, and insert those columns into the related database tables.
Any advice would be much appreciated!
Hi jlolling,
could you add the regexp option to the tFileExcelSheetInput like you did for tFileInputTextFlat component?
Those components are great.
Thanks
Sebastien
I am currently developing that because of the request of a couple of developers. It is not a big deal because the functionality is already developed for another component.
I will test it a while and this week (probably tomorrow) I will publish a new release with this feature.
I want to know if talend can do this and if it can how to do it? I have an exel sheet having columns First name, last name, street, city, country, state and phone no. I want to insert this data, (based on id in a database table related to the name fields in exel sheet) into another database table. I want to know if we can write a query on a database table to find the ids related to the names in exel sheet and then inserting the resulting records from exel sheet to database table.
I want to insert this data, (based on id in a database table related to the name fields in exel sheet) into another database table. I want to know if we can write a query on a database table to find the ids related to the names in exel sheet and then inserting the resulting records from exel sheet to database table
Could you please set an example for your requirement? Best regards Sabrina
Example
Suppose I already have a table in my database which contains all columns as there are in the exel sheet except one more column named id. Now when I import this sheet to a table in another database, I would like to import only those records that have names related to the ids in first table.
That is, I want to check at run time whether the names in exel sheet are present in a predefined database table, and if it is, then I want the related Id of that name to be inserted into the id field of the output database table.
