Sponsored Content
Top Forums UNIX for Dummies Questions & Answers Normalize Data and write to a flat file Post 302523349 by sp999 on Wednesday 18th of May 2011 02:31:28 PM
Old 05-18-2011
Normalize Data and write to a flat file

All,

Can anyone please help me with the below scenario.

I have a Flat file of the below format.

Code:
ID|Name|Level|Type|Zip|MAD|Risk|Band|Salesl|Dealer|CID|AType|CValue|LV|HV|DCode|TR|DU|NStartDate|UserRole|WFlag|EOption|PName|NActivationDate|Os|Orig|Cus|OType|ORequired|DType
03| Prog|3||90001,90002,90003,90004-90006,90007,90008|881,866,862-839,828||99-99,88-88,11-19||||REG||||MOQA,MODD,9999,8229,8228,8227,8226||||Confffers||Leased|||FREE-TO-GO-2011,FREE-TO-GO-2012|NMS|new|Acquis,Acquis|mandat,mandat|


Rules

File Column delimiter is |
ID column will not have multipe values seperated by commas.
Columns which have values separated by commas, should be made into a New row.
Columns which do not have values , no need make them into New row.

Output file format requested

Code:
ID|Col_Name|Value
03|Name|Prog
03|Level|3
03|Zip|90001
03|Zip|90002
03|Zip|90003
03|Zip|90004-90006
03|Zip|90007
03|Zip|90008
03|MAD|881
03|MAD|866
03|MAD|862-839
03|MAD|828
03|BAND|99-99
03|BAND|88-88
03|BAND|11-19
03|AType|REG
03|DCode|MOQA
03|DCode|MODD
03|DCode|9999
03|DCode|8229
03|DCode|8228
03|DCode|8227
03|DCode|8226
03|UserRole|Confffers
03|EOption|Leased
03|Os|FREE-TO-GO-2011
03|Os|FREE-TO-GO-2012
03|Orig|NMS
03|Cus|new
03|OType|Acquis
03|OType|Acquis
03|ORequired|mandat
03|ORequired|mandat

---------- Post updated at 11:31 AM ---------- Previous update was at 11:29 AM ----------

Also forgot to mention in my original post
  • Values separated by commas in columns is variable (changing)

Last edited by radoulov; 05-18-2011 at 05:08 PM.. Reason: Code tags.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help with Data Positioning from Columns in a flat file.

Hi All, I have used this forum many times to solve my many scripting problems. This time, I would like to seek some answers to a problem that I've been head scratching quite a bit on. My Example: I am converting a 2000-byte file into a 300-byte file this file has no delimiters and hardly any... (3 Replies)
Discussion started by: oott1
3 Replies

2. Shell Programming and Scripting

inserting data into a table from a flat file

Hi, I want to insert data into a table from a flat file, the file is having around 25 columns and some 10,000 records. The columns values are seperated by a space. Thanks (1 Reply)
Discussion started by: ss_ss
1 Replies

3. Shell Programming and Scripting

Load data to flat file from table.

Hi all, I need to know how to copy data from a table say ABC to a flat file say XYZ.dat in unix, Please leave ur comments and the fastest way to do so, I need to load the table records into flat file. Regards Ann (4 Replies)
Discussion started by: Haque123
4 Replies

4. Shell Programming and Scripting

Load data from a flat file to oracle.

I have a flat file with records like Header 123 James Williams Finance2000 124 Pete Pete HR 1500 125 PatrickHeather Engg 3000 Footer The structure is: Eno:4 characters Name:8 characters Surname : 9 characters Dept:7 characters Sal:4characters These are sample... (1 Reply)
Discussion started by: Shivdatta
1 Replies

5. Shell Programming and Scripting

Extracting data into flat file thru unix

Hi, I need to extract a oracle staging table to a flat file thru unix batch process.We are expecting more than 4million records in the table.I know I can do it using "UTL_FILE" .But,since "UTL_FILE" takes a lot of time I am looking for better options.Can any body suggest some better options? ... (3 Replies)
Discussion started by: Beena
3 Replies

6. Shell Programming and Scripting

To read a flat file containing XML data

I have a file something like this:aaaa.xml content of the file is 0,<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <storeInformation xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <s> <BRANCH_NO>3061</BRANCH_NO> <BRANCH_NAME>GREEN EXPRESS</BRANCH_NAME> ... (4 Replies)
Discussion started by: kmanivan82
4 Replies

7. Shell Programming and Scripting

Reading XML data in a FLAT FILE

I have a requirement to read the xml file and split the files into two diffrent files in Unix shell script. Could anyone please help me out with this requirement. Sample file --------------- 0,<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Information... (3 Replies)
Discussion started by: kmanivan82
3 Replies

8. Shell Programming and Scripting

Data is available or not in a flat file generated by Oracle

Hello, please help me an the below issue. i need to check whether data is available or not in a flat file generated by oracle (sometimes sql didn't any records) to overcome this. without opening flat file. Thanks....... (1 Reply)
Discussion started by: mahesh1987
1 Replies

9. Shell Programming and Scripting

Getting data from a flat file based on condition

Hi, I have a flaty file from which i am fetching few columns in tablular form as below code. Now i want to fetch the column 6 and 7 in below code only if it either of them is non zero.However below startement awk -F, '$6==0 && $7==0{exit 1}' ${IFILE} is not working..Not sure where is the... (36 Replies)
Discussion started by: Vivekit82
36 Replies

10. Programming

Need sql query to string split and normalize data

Hello gurus, I have data in one of the oracle tables as as below: Column 1 Column 2 1 NY,NJ,CA 2 US,UK, 3 AS,EU,NA fyi, Column 2 above has data delimited with a comma as shown. I need a sql query the produce the below output in two columns... (5 Replies)
Discussion started by: calredd
5 Replies
Archive::Zip::MemberRead(3)				User Contributed Perl Documentation			       Archive::Zip::MemberRead(3)

NAME
Archive::Zip::MemberRead - A wrapper that lets you read Zip archive members as if they were files. SYNOPSIS
use Archive::Zip; use Archive::Zip::MemberRead; $zip = Archive::Zip->new("file.zip"); $fh = Archive::Zip::MemberRead->new($zip, "subdir/abc.txt"); while (defined($line = $fh->getline())) { print $fh->input_line_number . "#: $line "; } $read = $fh->read($buffer, 32*1024); print "Read $read bytes as :$buffer: "; DESCRIPTION
The Archive::Zip::MemberRead module lets you read Zip archive member data just like you read data from files. METHODS
Archive::Zip::Member::readFileHandle() You can get a "Archive::Zip::MemberRead" from an archive member by calling "readFileHandle()": my $member = $zip->memberNamed('abc/def.c'); my $fh = $member->readFileHandle(); while (defined($line = $fh->getline())) { # ... } $fh->close(); Archive::Zip::MemberRead->new($zip, $fileName) Archive::Zip::MemberRead->new($zip, $member) Archive::Zip::MemberRead->new($member) Construct a new Archive::Zip::MemberRead on the specified member. my $fh = Archive::Zip::MemberRead->new($zip, 'fred.c') setLineEnd(expr) Set the line end character to use. This is set to by default except on Windows systems where it is set to . You will only need to set this on systems which are not Windows or Unix based and require a line end different from . This is a class method so call as "Archive::Zip::MemberRead"->"setLineEnd($nl)" rewind() Rewinds an "Archive::Zip::MemberRead" so that you can read from it again starting at the beginning. input_record_separator(expr) If the argument is given, input_record_separator for this instance is set to it. The current setting (which may be the global $/) is always returned. input_line_number() Returns the current line number, but only if you're using "getline()". Using "read()" will not update the line number. close() Closes the given file handle. buffer_size([ $size ]) Gets or sets the buffer size used for reads. Default is the chunk size used by Archive::Zip. getline() Returns the next line from the currently open member. Makes sense only for text files. A read error is considered fatal enough to die. Returns undef on eof. All subsequent calls would return undef, unless a rewind() is called. Note: The line returned has the input_record_separator (default: newline) removed. getline( { preserve_line_ending => 1 } ) Returns the next line including the line ending. read($buffer, $num_bytes_to_read) Simulates a normal "read()" system call. Returns the no. of bytes read. "undef" on error, 0 on eof, e.g.: $fh = Archive::Zip::MemberRead->new($zip, "sreeji/secrets.bin"); while (1) { $read = $fh->read($buffer, 1024); die "FATAL ERROR reading my secrets ! " if (!defined($read)); last if (!$read); # Do processing. .... } AUTHOR
Sreeji K. Das <sreeji_k@yahoo.com> See Archive::Zip by Ned Konz without which this module does not make any sense! Minor mods by Ned Konz. COPYRIGHT
Copyright 2002 Sreeji K. Das. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. perl v5.18.2 2014-01-08 Archive::Zip::MemberRead(3)
All times are GMT -4. The time now is 07:39 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy