Shell command to Strip white spaces at specific location


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell command to Strip white spaces at specific location
# 1  
Old 12-16-2013
Shell command to Strip white spaces at specific location

Hello,
I have a Comma separated input file with one of the sample records as below:

Code:
-9223372036854477528,"834","834003325515BXNI00101012013C","5","PLAN_VALUE","PPO, General
Cable","C",20130101,99991231,"A","2012-12-25-13.58.14.434000","ZPL2    ","2012-12-25-13.58.14.434000","ZPL2    ","2013-06-12-17.29.21.451000","BX99    ",-9223372034051003587,"R","30","Health Benefit Plan Coverage","G","Out of Pocket (Stop Loss)","N","Out Of Network","IND","Individual","23","Calendar Year",+0000000000006000.00,,,,,"00226","N",,,,,,,"3 ","1 ",,,,,,,,,,,,,,,"2013-06-12-17.29.22.005000","ZPL2    ","2013-06-12-17.29.22.005000","BX99    ",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1,"EXCLUDES DEDUCTIBLE AND COPAYMENTS",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Please note that huge amount of space between "GENERAL" & "CABLE" (marked in Bold). Due to this a single record is getting split into 2 and I am not able to import them correctly to Excel.

I need help with a command to strip multiple white spaces, Tab or new line character between WORDS (not at beginning or end of data) to be replace with a single space at specific location. For instance, in the example, perform the action between 5th and the 6th COMMA(,).

Please note that there is a comma in "PPO, General
Cable"
which should not considered as that is Data not a column separator. In this instance, the action should be performed for ,"PPO, General
Cable",
Data


Thanks a lot for your help in advance.

Last edited by Scott; 12-16-2013 at 02:03 PM.. Reason: Code tags
# 2  
Old 12-16-2013
Are you sure they are whitespaces?
Copy-n-Paste reveals a single newline character NOT spaces...
Code:
AMIGA:~> od -tx1 /tmp/text
0000000 2d 39 32 32 33 33 37 32 30 33 36 38 35 34 34 37
0000020 37 35 32 38 2c 22 38 33 34 22 2c 22 38 33 34 30
0000040 30 33 33 32 35 35 31 35 42 58 4e 49 30 30 31 30
0000060 31 30 31 32 30 31 33 43 22 2c 22 35 22 2c 22 50
0000100 4c 41 4e 5f 56 41 4c 55 45 22 2c 22 50 50 4f 2c
0000120 20 47 65 6e 65 72 61 6c 0a 43 61 62 6c 65 22 2c
0000140 22 43 22 2c 32 30 31 33 30 31 30 31 2c 39 39 39
0000160 39 31 32 33 31 2c 22 41 22 2c 22 32 30 31 32 2d
0000200 31 32 2d 32 35 2d 31 33 2e 35 38 2e 31 34 2e 34
0000220 33 34 30 30 30 22 2c 22 5a 50 4c 32 20 22 2c 22
0000240 32 30 31 32 2d 31 32 2d 32 35 2d 31 33 2e 35 38
0000260 2e 31 34 2e 34 33 34 30 30 30 22 2c 22 5a 50 4c
0000300 32 20 22 2c 22 32 30 31 33 2d 30 36 2d 31 32 2d
0000320 31 37 2e 32 39 2e 32 31 2e 34 35 31 30 30 30 22
0000340 2c 22 42 58 39 39 20 22 2c 2d 39 32 32 33 33 37
0000360 32 30 33 34 30 35 31 30 30 33 35 38 37 2c 22 52
0000400 22 2c 22 33 30 22 2c 22 48 65 61 6c 74 68 20 42
0000420 65 6e 65 66 69 74 20 50 6c 61 6e 20 43 6f 76 65
0000440 72 61 67 65 22 2c 22 47 22 2c 22 4f 75 74 20 6f
0000460 66 20 50 6f 63 6b 65 74 20 28 53 74 6f 70 20 4c
0000500 6f 73 73 29 22 2c 22 4e 22 2c 22 4f 75 74 20 4f
0000520 66 20 4e 65 74 77 6f 72 6b 22 2c 22 49 4e 44 22
0000540 2c 22 49 6e 64 69 76 69 64 75 61 6c 22 2c 22 32
0000560 33 22 2c 22 43 61 6c 65 6e 64 61 72 20 59 65 61
0000600 72 22 2c 2b 30 30 30 30 30 30 30 30 30 30 30 30
0000620 36 30 30 30 2e 30 30 2c 2c 2c 2c 2c 22 30 30 32
0000640 32 36 22 2c 22 4e 22 2c 2c 2c 2c 2c 2c 2c 22 33
0000660 20 22 2c 22 31 20 22 2c 2c 2c 2c 2c 2c 2c 2c 2c
0000700 2c 2c 2c 2c 2c 2c 22 32 30 31 33 2d 30 36 2d 31
0000720 32 2d 31 37 2e 32 39 2e 32 32 2e 30 30 35 30 30
0000740 30 22 2c 22 5a 50 4c 32 20 22 2c 22 32 30 31 33
0000760 2d 30 36 2d 31 32 2d 31 37 2e 32 39 2e 32 32 2e
0001000 30 30 35 30 30 30 22 2c 22 42 58 39 39 20 22 2c
0001020 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c
*
0001060 2c 31 2c 22 45 58 43 4c 55 44 45 53 20 44 45 44
0001100 55 43 54 49 42 4c 45 20 41 4e 44 20 43 4f 50 41
0001120 59 4d 45 4e 54 53 22 2c 2c 2c 2c 2c 2c 2c 2c 2c
0001140 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c
0001160 2c 2c 2c 2c 2c 2c 2c 2c 2c 0a
0001172
AMIGA:~> _

# 3  
Old 12-16-2013
I came to the same conclusion when trying with sed... In the sample given there is no spcae but a newline after General...
# 4  
Old 12-16-2013
Based on assumptions:
Code:
awk '/,$/{ORS=RS}!/,$/{ORS=FS}1' file

# 5  
Old 12-16-2013
This is the typical behaviour of a .csv file generated by MS EXCEL, having a single <new line> char in a cell to enforce multiline formatting in that cell. Are you sure you really really need that formatting or can you get rid of it at the root, in EXCEL, before exporting the .csv file?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replace spaces at a specific Location

Hello All, I have a comma separated file which needs to be loaded to the database. But, I need to trim the white spaces for a specific column before its loaded. Below is the sample line from the input file: 690,690,0575,"01011940","01011940", , , , , ,36720,36722,V2020,V2999,... (6 Replies)
Discussion started by: Praveenkulkarni
6 Replies

2. Shell Programming and Scripting

White spaces issue with shell variables

Hi all, I've a requirement as below Source file src.txt sample data: A<10 white spaces>B12<5 white spaces>C<17 white spaces> A1<5 white spaces>B22<5 white spaces>C13<17 white spaces> when I'm fetching a record from this file into a shell variable like below: vRec=`head -1 src.txt... (2 Replies)
Discussion started by: madhu_1126
2 Replies

3. Shell Programming and Scripting

How to strip the spaces in file names?

please somebody tell me what is wrong with this, while the thumbnail grabbing works and encoding works, but what is not working is, mv $i.jpg /var/www/thumbs/ and mv $i.mp4 /var/www/uploads/ #!/bin/bash # MINT 9 - FFMPEG - QT-FASTSTART - X264 - MP4 DIR=/var/www/tmp for i in... (9 Replies)
Discussion started by: mysoogal
9 Replies

4. Shell Programming and Scripting

Leading white spaces

Hi, I am having problem in deleting the leading spaces:- cat x.csv baseball,NULL,8798765,Most played baseball,NULL,8928192,Most played baseball,NULL,5678945,Most played cricket,NOTNULL,125782,Usually played cricket,NOTNULL,678921,Usually played $ nawk 'BEGIN{FS=","}!a... (2 Replies)
Discussion started by: scripter12
2 Replies

5. Shell Programming and Scripting

How to Use Sed Command to replace white spaces with comma from between two fields - Mayank

SHELL SCRIPT Hi I have a file in the following format Mayank Sushant Dheeraj Kunal ARUN Samir How can i replace the white space in between and replace them with a comma?? The resultant output should be Mayank,Sushant Dheeraj,Kunal ARUN,Samir i tried using sed -e... (8 Replies)
Discussion started by: mayanksargoch
8 Replies

6. Shell Programming and Scripting

Include white spaces while using CUT command

Hi I tried to extract 19 characters (default) enclosed with in tag from a file using cut command. If the characters comprises of double space, the cut command gives the output with a single spacing. file 1 <name>Kumar Rajasekaran</name> cut -c7-26 "file1" the out put i received is ... (48 Replies)
Discussion started by: Sekar1
48 Replies

7. Shell Programming and Scripting

trimming white spaces

I have a variable that calls in a string from txt file. Problem is the string comes with an abundance of white spaces trailing it. Is there any easy way to trim the tailing white spaces off at the end? Thanks in advance. (9 Replies)
Discussion started by: briskbaby
9 Replies

8. Shell Programming and Scripting

delete white spaces

hi all... i have the next question: i have a flat file with a lot of records (lines). Each record has 10 fields, which are separated by pipe (|). My problem is what sometimes, in the first record, there are white spaces (no values, nothing) in the beginning of the record, like this: ws ws... (2 Replies)
Discussion started by: DebianJ
2 Replies

9. Shell Programming and Scripting

Strip leading and trailing spaces only in a shell variable with embedded spaces

I am trying to strip all leading and trailing spaces of a shell variable using either awk or sed or any other utility, however unscuccessful and need your help. echo $SH_VAR | command_line Syntax. The SH_VAR contains embedded spaces which needs to be preserved. I need only for the leading and... (6 Replies)
Discussion started by: jerardfjay
6 Replies

10. UNIX for Dummies Questions & Answers

deleting white spaces

How would I delete white spaces in a specified file? Also, I'd like to know what command I would use to take something off a regular expression, and put it onto another. ie. . . . expression1 <take_off> . . . expression2 (put here) . . . Any help would be great, thanks! (10 Replies)
Discussion started by: cary530
10 Replies
Login or Register to Ask a Question