Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Splitting delimited string into rows

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Splitting delimited string into rows
# 1  
Old 05-18-2015
Splitting delimited string into rows
Hi,

I have a requirement that has 50-60 million records that we need to split a delimited string (Delimeter is newline) into rows.

Source Date:

SerialID UnidID GENRE
100 A11 AAAchar(10)BBB
200 B11 CCCchar(10)DDD(10)ZZZZ

Field 'GENRE' is a string with new line as delimeter and not sure how many it may have?

Please advise!

Thanks
# 2  
Old 05-18-2015
Please use code tags as required by forun rules!

I guess this is from MS EXCEL where <NL> (0x0A, \n) is used as a marker to split strings into rows within a cell?

Taking your sample into *nix makes it look like
Code:
SerialID UnidID GENRE
100 A11 AAA           
BBB
200 B11 CCC           
DDD 
ZZZZ

WHAT exactly do you want to split into rows?
# 3  
Old 05-18-2015
Hi,

I have a requirement that has 50-60 million records that we need to split a delimited string (Delimeter is newline) into rows.

Source Data

Code:
SerialID UnidID GENRE
100 A11 AAAchar(10)BBB
200 B11 CCCchar(10)DDD(10)ZZZZ

Expected Output

Code:
SerialID UnidID GENRE
100 A11 AAA
100 A11 BBB
200 B11 CCC
200 B11 DDD
200 B11 ZZZZ

Field 'GENRE' is a string with new line as delimeter and not sure how many it may have?

Please advise!

Thanks
Last edited by Don Cragun; 05-18-2015 at 08:34 PM.. Reason: Add CODE tags.
# 4  
Old 05-18-2015
Are you sure the input data looks like you posted, and, if yes, are you sure you're on *nix?
# 5  
Old 05-18-2015
Field Genre can have any number of values separated by a newline delimeter.
# 6  
Old 05-18-2015
As I said, newline has a special meaning on *nix.

Given my suspicion (see post#2) is true, try:
Code:
awk 'NR==1 {print; next} NF==3 {TMP=$1 OFS $2} {print TMP OFS $NF}' file3
SerialID UnidID GENRE
100 A11 AAA
100 A11 BBB
200 B11 CCC
200 B11 DDD
200 B11 ZZZZ

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help/Advise please for converting space delimited string variable to comma delimited with quote

Hi, I am wanting to create a script that will construct a SQL statement based on a a space delimited string that it read from a config file. Example of the SQL will be For example, it will read a string like "AAA BBB CCC" and assign to a variable named IN_STRING. I then concatenate... (2 Replies)
Discussion started by: newbie_01
2 Replies

2. Shell Programming and Scripting

Splitting a delimited text file

Howdy folks, I've got a very large plain text file that I need to split into many smaller files. My script-fu is not powerful enough for this, so any assistance is much appreciated. The file is a database dump from Cyrus IMAP server. It's basically a bunch of emails (thousands) all... (13 Replies)
Discussion started by: lupin..the..3rd
13 Replies

3. Shell Programming and Scripting

Convert Columns in Rows delimited by a strings

Hi Gurus, I have a file that contain inventory information from someones computers: UserName domain\user1 DNSHostName machine1 Caption Microsoft Windows 7 Professional OSArchitecture 64 bits SerialNumber XXX Name HP EliteBook Revolve 810 G1 NumberOfProcessors 1 Name Intel(R)... (2 Replies)
Discussion started by: gilmore666
2 Replies

4. Shell Programming and Scripting

Need help in splitting the string to diff rows

Hi, I have file with values as below 1~ab~456~ac:bd:de:ef~yyyy-mm-dd 2~cd~458~af:fg:ty:er:ty:uj:io:~yyyy-mm-dd I want the o/p as for frist row 1~ab~456~ac~yyyy-mm-dd 1~ab~456~bd~yyyy-mm-dd 1~ab~456~de~yyyy-mm-dd 1~ab~456~ef~yyyy-mm-dd and for the second row 2~cd~458~af~yyyy-mm-dd... (4 Replies)
Discussion started by: rithushri
4 Replies

5. Shell Programming and Scripting

splitting tab delimited strings

hi i have a requirement to input a string to a shell script and to split the string to multiple fields, the string is copied from a row of three columns (name,age,address) in an excel sheet. the three columns (from excel) are seperated with a tab when pasted in the command prompt, but when the ... (2 Replies)
Discussion started by: midhun19
2 Replies

6. Linux

Splitting a Text File by Rows

Hello, Please help me. I have hundreds of text files composed of several rows of information and I need to separate each row into a new text file. I was trying to figure out how to split the text file into different text files, based on each row of text in the original text file. Here is an... (2 Replies)
Discussion started by: dvdrevilla
2 Replies

7. Shell Programming and Scripting

splitting tab-delimited file with awk

Hi all, I need help to split a tab-delimited list into separate files by the filename-field. The list is already sorted ascendingly by filename, an example list would look like this; filename001 word1 word2 filename001 word3 word4 filename002 word1 word2 filename002 word3 word4... (4 Replies)
Discussion started by: perkele
4 Replies

8. Shell Programming and Scripting

Splitting file based on number of rows

Hi, I'm, new to shell scripting, I have a requirement where I have to split an incoming file into separate files each containing a maximum of 3 million rows. For e.g: if my incoming file say In.txt has 8 mn rows then I need to create 3 files, in which two will 3 mn rows and one will contain 2... (2 Replies)
Discussion started by: wahi80
2 Replies

9. UNIX for Dummies Questions & Answers

splitting a column into rows

I have a column of data of the format: EDITORIAL OPED 193987141 193986701 193987451 193986321 STATISTICS 193986351 COLUMN EDITORIAL OPED 193987171 NEWS 193321171 NEWS 193321111 NEWS 193320891 NEWS 193321841 (3 Replies)
Discussion started by: spindoctor
3 Replies

10. Shell Programming and Scripting

splitting a pipe delimited file in unix

Could one of you shad some light on this: I need to split the file by determining the record count and than splitting it up into 4 files. Please note, this is not a fixed record length but rather a "|" delimited file. I am not sure as how to handle reminder/offset for the 4th file. For... (4 Replies)
Discussion started by: ddedic
4 Replies
Login or Register to Ask a Question