Remove ^L from csv not working...


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Remove ^L from csv not working...
# 1  
Old 10-01-2014
Remove ^L from csv not working...

hello...
i have a requirement to convert a xls file to csv in RHEL 6.5 and ftp it to a windows location.
i am using xls2csv utility to convert the file in linux.

Input xls file attached...


I have used below commands to convert the file to csv:

Code:
xls2csv -x test.xls -s cp1252 -d 8859-1 > test.csv

And,
Code:
xls2csv -c\, test.xls > test.csv

The issue i am facing is after conversion, an empty line is adding everytime and it contains only "^L"

Code:
"A","B","C","D"
"1","2","3","4"
"2","3","4","5"
"3","4","5","6"
"4","5","6","7"
^L

To remove this control-L i tried below sed command...the output looks fine.

sed 's/^L//g' test.csv

But when I am redirecting this output to another csv file for example test2.csv, ^L appears again.

Can anybody please help?

Thanks in advance...

Last edited by Scrutinizer; 10-02-2014 at 12:24 PM.. Reason: CHANGED ICODE to CODE tags
# 2  
Old 10-01-2014
That's the \f or form feed representation.
Try to remove it as:
Code:
sed -e 's/\x0C//g' test.csv > another.test.csv

After that use another.test.csv
If you want to delete "in place" RHEL6 uses gnu sed which allows you to do this:

Code:
sed -ie 's/\x0C//g' test.csv

Now is gone from test.csv and you can use it.

Last edited by Aia; 10-01-2014 at 10:25 PM.. Reason: Adding gnu sed
This User Gave Thanks to Aia For This Post:
# 3  
Old 10-02-2014
Or use:
Code:
tr -d '\f' < file > newfile

This User Gave Thanks to Scrutinizer For This Post:
# 4  
Old 10-02-2014
All the suggestion works.. Thanks you so much...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to remove unwanted commas from a .csv file?

how to remove unwanted commas from a .csv file Input file format "Server1","server-PRI-Windows","PRI-VC01","Microsoft Windows Server 2012, (64-bit)","Powered On","1,696.12","server-GEN-SFCHT2-VMS-R013,server-GEN-SFCHT2-VMS-R031,server-GEN-SFCHT2-VMS-R023"... (5 Replies)
Discussion started by: ranjancom2000
5 Replies

2. Shell Programming and Scripting

Using sed to remove a column from a CSV

I found that the following works to remove the first column in my file when my CSV is delimited with a simple comma: sed -i 's/*,//' file.csv However, I have a new file where the fields are encapsulated with double quotes - general example of file: "Internal ID", "External ID", "Name"... (8 Replies)
Discussion started by: lojkyelo
8 Replies

3. Shell Programming and Scripting

How to remove space and delimit a csv file?

Hi All , I am facing a small challenge i need unix command to remove space as well as replace "|" to "," in a csv file . original file : A | B | c | D E | F | G | H I | J | K | L P | M | N | O Expected o/p: A,B,c,D E,F,G,H I,J,K,L P,M,N,O (4 Replies)
Discussion started by: Sweety532
4 Replies

4. Shell Programming and Scripting

Reg - Working on a CSV File in a script

Dear All, I have 2 CSV files and want to create a new CSV based on a common value in both of the CSV file. ColumnA ColumnB ColumnC Client 1 XXXXX Server A Client 2 XXXXX Server B Client 3 XXXXX Server C 2nd CSV file ColumnA CloumnB Server A value... (14 Replies)
Discussion started by: rrb2009
14 Replies

5. Shell Programming and Scripting

ShellScript to Remove Newline in ouput.csv

Hi friends, I am running one SQL though .sh file and in output I am seeing newline whevnever There is a new line in column. I WANT TO REMOVE NEWLINE IN OUTPUT FILE. Present out put in Column: ------------------------- Hi How Are You. Required output in Column: ------------- Hi How... (4 Replies)
Discussion started by: crmsachin
4 Replies

6. Shell Programming and Scripting

Need to remove some value in CSV file

Hi All A .csv file containing multiple value on it. i need to remove some value from it. Please advise how can i find multiple value from that and remove itself. Please provide solution for this. Thanks in advance. (4 Replies)
Discussion started by: yash1978
4 Replies

7. Shell Programming and Scripting

How to remove line break in a csv file

Hi Experts, My requirement is to read the csv file and need to remove if any line break in it. sample data: Row1: "Oslo, Symra kino",Oslo,130-7,Symra 1,130-7-91 Row2:"Tønsberg, Brygga Kino SF",Tønsberg,202-1, Tønsberg SF 4,202-1-4 Expected data: Row1: "Oslo, Symra... (6 Replies)
Discussion started by: cnraja
6 Replies

8. Shell Programming and Scripting

How to remove Blank rows in a csv file

Hi, I need help to remove blank rows at the end of file. Sample data: "Oslo, Symra kino",Oslo,130-7,Symra 1,130-7-91 "Tønsberg, Brygga Kino SF",Tønsberg,202-1,Tønsberg SF 4,202-1-4 ,,,, ,,,, ,,,, ,,,, Expected data: "Oslo, Symra kino",Oslo,130-7,Symra 1,130-7-91 "Tønsberg, Brygga... (6 Replies)
Discussion started by: cnraja
6 Replies

9. Shell Programming and Scripting

Remove text from a csv file using sed

I am trying to remove the ita from this file: "1234ita","john","smith" "56789ita","jim","thomas" the command i am using is: sed '/^ita/d' infile.csv > outfile.csv it is running but doing nothing, very rarely use sed so trying to learn it any help would be appreciated (2 Replies)
Discussion started by: Pablo_beezo
2 Replies

10. Shell Programming and Scripting

sed csv remove conditionally

Hello, I have many csv file, but I would like to delete lines with some values in a column conditionally. My example look like this, ex1e, ex2g, ex39, pasg, ssg, mrlc, pc, kb, coop -112, -53, -177, 64, 62, 71, 1, 487, 20 -101, -61, -53, 0, 32767, 51, 0, ... (6 Replies)
Discussion started by: Jae
6 Replies
Login or Register to Ask a Question