Combine 2 rows of CSV file in SSIS

Question

I have one CSV file where the information is spread on two lines
Line 1 contains Name and age
Line 2 contains detail like address, city, salary, occupation

I want to combine 2 rows to insert it in a database.
CSV file :

Raju, 42
12345 west andheri,Mumbai, 100000, service

In SQL Server I can do by using cursor. But I have to do in SSIS.

Some options: 1. Write a script that removes the carriage return beforehand; 2. Use a script transform to transform it on the way in (like this https://stackoverflow.com/questions/31442325/ssis-combine-multiple-rows-into-single-row); 3. Use a lookup and SQL Command destination to insert the first row and update the second row. If any of these interest you I could explain further — Nick.Mc, Jun 05 '19 at 09:32

score 0 · Answer 1 · answered Jun 05 '19 at 17:34

0

For a similar case, i will read each line as one column and use a script component to fix the structure. You can follow my answer on the following question. It contains a step-by-step guide:

SSIS reading LF as terminator when its set as CRLF

answered Jun 05 '19 at 17:34

Hadi

36,233
13
65
124

KeithL · Answer 2 · 2019-06-05T19:21:37.357

I like using a script component in order to be able to store data from a different row in this case.

Read the file as a single column CSV into Column1.
Add script component and add a new Output called CorrectedOutput and define all columns from both rows. Also, mark Column1 as read.
Create 2 variables outside of row processing to 'hold' first row
```
string name = string.Empty;
string Age = string.Empty;
```
Use a split to determine line 1 or line 2
```
string[] str = Row.Column1.Split(',');
```

Use an if to determine row 1 or 2

if(str.Length == 2)
{
  name = str[0]; 
  age=str[1];}
else
{
     CorrectedOutputBuffer.AddRow();
     CorrectedOutputBuffer.Name = name; //This uses the stored value from prior row
     CorrectedOutputBuffer.Age = age; //This uses the stored value from prior row
     CorrectedOutputBuffer.Address = str[0];
     CorrectedOutputBuffer.City = str[1];
     CorrectedOutputBuffer.Salary = str[2];
     CorrectedOutputBuffer.Occupation = str[3];
}

The overall effect is this... On Row 1, you just hold the data in variables On Row 2, you write out the data to 1 new row.

Combine 2 rows of CSV file in SSIS

2 Answers2