Copy previous cell's value to current cell in CSV file

Question

Hi,

I am working on an SSIS package where I need to read data from a CSV file and insert into SQL table. But before I do that, I need to do some pre-processing on the file.

The CSV file looks like this:

3004891W,Chander Shekhar Sud,In Force
3022180P,Carole Margaret Hill,In Force
-,-,-
3022181I,James Allen Hill,In Force
-,-,-
N601635J,Spencer John Privett,Auto Pup
-,-,-

I have to replace the "-" with previous (one row above) cell value.
Can someone please suggest how to do this?

I am flexible with the approach. It can be a script task or using a temporary table to hold the data, and then processing that data in a Data Flow Task.

Thanks!!

Answer 1

Hi,

You can extract the data into a Temporary table and use an execute sql task to load main table. Make sure you have an identity column in the temp table.

Hold on I will get you the query you may need.

DECLARE @TEST TABLE (
ID INT IDENTITY(1,1),
COLUMN1 VARCHAR(20),
COLUMN2 VARCHAR(20),
COLUMN3 VARCHAR(20)
)

INSERT INTO @TEST
SELECT '3004891W','Chander Shekhar Sud','In Force' UNION ALL
SELECT '3022180P','Carole Margaret Hill','In Force' UNION ALL
SELECT '-','-','-' UNION ALL
SELECT '3022181I','James Allen Hill','In Force' UNION ALL
SELECT '-','-','-' UNION ALL
SELECT 'N601635J','Spencer John Privett','Auto Pup' UNION ALL
SELECT '-','-','-' 


SELECT T2.COLUMN1,T2.COLUMN2,T2.COLUMN3 FROM @TEST T1 LEFT JOIN @TEST T2 ON T1.ID - 1 = T2.ID
WHERE T1.COLUMN1 = '-'
UNION ALL
SELECT T2.COLUMN1,T2.COLUMN2,T2.COLUMN3 FROM @TEST T2 WHERE T2.COLUMN1 <> '-'

The above query will work perfectly when you dont have two consecutive rows with '-' values. If you expect such data, let me know query needs a little tuning.

---

Rajkumar

Answer 2

Thanks Rajkumar.

Adding Identity column is a good idea.

But can you please elaborate on the way I should proceed after importing data from csv file into temp table.

Answer 3

Yes. There may be consecutive multiple rows with "-" value.

Thanks for your help!

Answer 4

Thanks Rajkumar.

Adding Identity column is a good idea.

But can you please elaborate on the way I should proceed after importing data from csv file into temp table.

Follow the edited query in my first post. You need to use Execute sql task with a query like below

INSERT INTO MAINTABLE
SELECT T2.COLUMN1,T2.COLUMN2,T2.COLUMN3 FROM @TEST T1 LEFT JOIN @TEST T2 ON T1.ID - 1 = T2.ID
WHERE T1.COLUMN1 = '-'
UNION ALL
SELECT T2.COLUMN1,T2.COLUMN2,T2.COLUMN3 FROM @TEST T2 WHERE T2.COLUMN1 <> '-'

---

Rajkumar

Answer 5

Yes. There may be consecutive multiple rows with "-" value.

Thanks for your help!

Good! Then Try below,

DECLARE @TEST TABLE (
ID INT IDENTITY(1,1),
COLUMN1 VARCHAR(20),
COLUMN2 VARCHAR(20),
COLUMN3 VARCHAR(20)
)

INSERT INTO @TEST
SELECT '3004891W','Chander Shekhar Sud','In Force' UNION ALL
SELECT '3022180P','Carole Margaret Hill','In Force' UNION ALL
SELECT '-','-','-' UNION ALL
SELECT '-','-','-' UNION ALL
SELECT '3022181I','James Allen Hill','In Force' UNION ALL
SELECT '-','-','-' UNION ALL
SELECT 'N601635J','Spencer John Privett','Auto Pup' UNION ALL
SELECT '-','-','-' 

SELECT COLUMN1,COLUMN2,COLUMN3 FROM(
SELECT T1.ID,T2.COLUMN1,T2.COLUMN2,T2.COLUMN3,RANK() OVER(PARTITION BY T1.ID ORDER BY T2.ID DESC) AS RANK1 FROM @TEST T1 CROSS JOIN @TEST T2 
WHERE T1.COLUMN1 = '-' AND T2.ID < T1.ID AND T2.COLUMN1 <> '-') A
WHERE RANK1 = 1
UNION ALL
SELECT T2.COLUMN1,T2.COLUMN2,T2.COLUMN3 FROM @TEST T2 WHERE T2.COLUMN1 <> '-'

---

Rajkumar

Answer 6

Thanks a lot Rajkumar.

That worked perfectly!!