I'm new to Talent Open Studio so sorry if this is a basic question. I have a csv file which I need to split into multiple related tables. The file contains a number of fields relating to a survey and if the user has provided their contact details they are stored in the same line. I want to load this into 2 tables, 1 which has the survey answers and a contact ID and the second has the contact details.
The problem I have currently is that I'm getting 1 row in the contact table for each survey. How can I structure my job so contacts are numbered sequentially with no gaps and that surveys are linked to the correct contact?
Thanks for your suggestions. I was hoping to have different ID's for each type of data (surveys and contacts). I've created a sample input data file to give you an example of what I'm working with as input and 2 output files showing what I'd like to achieve
The input file has a row for each survey and if contact details are supplied they are included. The first output I'd like is the survey data with a SurveyID (Numeric.sequence). The second output is a list of contacts with a contactID. I'd also like the appropriate contact ID to appear in the survey data.
In my job the input is a csv file and the outputs are Database tables in a SQL Server database.