Copy previous cell's value to current cell in CSV file
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!!
November 1st, 2012 2:55am

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
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2012 3:17am

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.
November 1st, 2012 3:25am

Yes. There may be consecutive multiple rows with "-" value. Thanks for your help!
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2012 3:36am

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
November 1st, 2012 3:36am

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
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2012 3:47am

Thanks a lot Rajkumar. That worked perfectly!!
November 1st, 2012 5:13am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics