Help needed - data manipulation

June 16, 2010 at 11:13:01
Specs: Windows XP
Data manipulation strategy - before loading to a SQL Server table (using SSIS package/ SQL/ Excel Macro)


Hi Everyone,

I am trying to load a flat file to a SQL server table. But before I load the file I need to manipulate the data as below (such that the user becomes a part of every row and the values in the 4th column are transposed to form rows -

INPUT -

BBSA000 , DENNIS MACKENZIE
BBSA000 , ,MEMA , YNY , EMP ,12121
BBSA000 , ,MEMB , YYYY, EMP ,12121
BBSA000 , ,MEMC , YYYY, EMP ,12121
BBSA002 , GEORGE ROWLAND
BBSA002 , ,MEMA , YYYY, EMP ,12122
BBSA002 , ,MEMB , YNNY, EMP ,12122


OUTPUT -

BBSA000 , DENNIS MACKENZIE ,MEMA,Y,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMA,N,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMA,Y,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMA, ,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMB,Y,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMB,Y,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMB,Y,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMB,Y,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMC,Y,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMC,Y,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMC,Y,EMP,12121
BBSA000 , DENNIS MACKENZIE ,MEMC,Y,EMP,12121

(output split for clarity)
BBSA002 , GEORGE ROWLAND ,MEMA,Y,EMP,12122
BBSA002 , GEORGE ROWLAND ,MEMA,Y,EMP,12122
BBSA002 , GEORGE ROWLAND ,MEMA,Y,EMP,12122
BBSA002 , GEORGE ROWLAND ,MEMA,Y,EMP,12122
BBSA002 , GEORGE ROWLAND ,MEMB,Y,EMP,12122
BBSA002 , GEORGE ROWLAND ,MEMB,N,EMP,12122
BBSA002 , GEORGE ROWLAND ,MEMB,N,EMP,12122
BBSA002 , GEORGE ROWLAND ,MEMB,Y,EMP,12122

Can some one please suggest a data manipulation strategy/ script etc

Thanks in advance!

Regards,

Adam


See More: Help needed - data manipulation

Report •


#1
June 17, 2010 at 09:34:21
@echo off & setlocal enabledelayedexpansion
set cc=0
for /f "tokens=1-6 delims=," %%a in (forum14) do call :!cc! %%a "%%b" "%%c" "%%d" %%e %%f
goto :eof
:0
echo %1:%~2:%~3
set base1=%1,%~2 %~3
echo zero base1 %base1%
set cc=1
goto :eof
:1
if %4 equ "" (
set /a cc=0
goto :0
)
set base2=%~3
echo base2:%base2%
set base3=%5,%6
echo base3:%base3%
echo one bases %base1%,%base2%::%base3%
set yny=%~4
set /a pos=0
call :2
goto :eof
:2
set /a pos+=1
set ch=!yny:~%pos%,1!
:echo two, %pos% is:%ch%:
if "%ch%" equ "" (set /a cc=1) else (>> newfil echo %base1%,%base2%,%ch%,%base3% & goto :2)
::----- end
i left most of the echos in because it will probably require some debugging.
Not sure about the extra spaces, they were in the file, so i left them in there.
hopefully, the sql import will trim them (leading, trailing) otherwise they will
have to be coded out.

Report •
Related Solutions


Ask Question