LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 11-21-2016, 06:59 AM   #1
trickydba
Member
 
Registered: Nov 2016
Location: Atlanta,Georgia
Posts: 310

Rep: Reputation: Disabled
Adding carriage return to excel file header


I have an Excel file that has 'CR LF' in every line except the header. The header only has 'LF'. How can I add 'CR' to the header using command line? I assume then the file would have to be rewritten so all lines have 'CR LF'.

Last edited by trickydba; 11-21-2016 at 07:16 AM.
 
Old 11-21-2016, 09:08 AM   #2
cliffordw
Member
 
Registered: Jan 2012
Location: South Africa
Posts: 509

Rep: Reputation: 203Reputation: 203Reputation: 203
Hi there,

For a text file in general, one way to solve the problem might be to run "dos2unix" on it first (which will remove the CRs from all lines where present), then run "unix2dos", which will add CRs to all lines (including the header).

Regarding the Excel file specifically, what format exactly are you talking about? Is the the XML file you extracted from a .xlxs file? Purely out of curiosity, why is the CR/LF details important?

Regards,

Clifford
 
1 members found this post helpful.
Old 11-21-2016, 09:09 AM   #3
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 26,937

Rep: Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017
Quote:
Originally Posted by trickydba View Post
I have an Excel file that has 'CR LF' in every line except the header. The header only has 'LF'. How can I add 'CR' to the header using command line? I assume then the file would have to be rewritten so all lines have 'CR LF'.
As with your other threads, this is no different: SHOW US YOUR CODE.

We can't guess as to what you have to work with. You've not told us ANYTHING about this file (we can only ASSUME it's a CSV/TSV file, since Excel files are a lump of XML/binary junk). We don't know how this is getting generated either, on what version/distro of Linux. Read the "Question Guidelines" link in my posting signature...you need to provide usable details when asking a question; having us ask you each time to provide code/samples/etc., isn't good. Neither is closing one of your threads by simply saying you "added a few lines of code", and not actually SHOWING what you did...the community is here to help each other. You came here for a solution, found one, and then aren't sharing it.

Without knowing ANYTHING about what you have to work with, you could just run
Code:
sed -i 's/\r/\n/' <filename>
...which will replace the first CR with a CRLF.
 
1 members found this post helpful.
Old 11-21-2016, 09:28 AM   #4
trickydba
Member
 
Registered: Nov 2016
Location: Atlanta,Georgia
Posts: 310

Original Poster
Rep: Reputation: Disabled
@ TB0ne.........what you have stated makes sense and I apologize for my lack of providing code. Will try to do better from this point forward. I am using an application called EDQ that generates these reports so it doesn't show any code. It's sort of like SQL in a GUI environment. I use EDQ for everything, which shows no code, which is th reason why I only ask for solutions in a command line that I can put in a bash script.

All lines show "CR LF" when viewed in Notepad ++ except the first line (header). The first line only shows "LF", just need it to show "CR LF".

I hope all understands why I cannot provide code and only ask for help that I can incorporate into a bash script.

The file is pipe delimited, no quote characters and the encoding is 'windows-1252'. Another team that I am sending these reports to requires that each line has "CR LF".

Oh, and it is a text file.

Last edited by trickydba; 11-21-2016 at 09:34 AM.
 
Old 11-21-2016, 09:35 AM   #5
cliffordw
Member
 
Registered: Jan 2012
Location: South Africa
Posts: 509

Rep: Reputation: 203Reputation: 203Reputation: 203
Hi again,

We still don't know what file type this is? Is this a CSV file as TB0ne suggested?

Please try either TB0ne's "sed" command or my "dos2unix" & "unix2dos" approach & let us know whether that solves the problem?

Regards,

Clifford
 
1 members found this post helpful.
Old 11-21-2016, 09:42 AM   #6
trickydba
Member
 
Registered: Nov 2016
Location: Atlanta,Georgia
Posts: 310

Original Poster
Rep: Reputation: Disabled
It is a text file.
 
Old 11-21-2016, 09:44 AM   #7
trickydba
Member
 
Registered: Nov 2016
Location: Atlanta,Georgia
Posts: 310

Original Poster
Rep: Reputation: Disabled
@TB0ne.............the other thread you was referring to that I have not marked as solved and did not provide the solution for other to see, I have since added the code that solved my issue and marked it as solved. Sorry all, I am new to this site and will do better.

Last edited by trickydba; 11-21-2016 at 09:46 AM.
 
Old 11-21-2016, 09:52 AM   #8
cliffordw
Member
 
Registered: Jan 2012
Location: South Africa
Posts: 509

Rep: Reputation: 203Reputation: 203Reputation: 203
Quote:
Originally Posted by trickydba View Post
It is a text file.
OK, have you tried the suggested solutions?
 
1 members found this post helpful.
Old 11-21-2016, 10:03 AM   #9
trickydba
Member
 
Registered: Nov 2016
Location: Atlanta,Georgia
Posts: 310

Original Poster
Rep: Reputation: Disabled
I have. I tried TB0ne's suggestion, which is:

sed -i 's/\r/\n/' testfile <------ with no extension

But it converts all lines to just have "LF". All lines have "CR LF" except the header line, which shows only "LF". I need only the remaining line(header) to show "CR LF".
 
Old 11-21-2016, 10:18 AM   #10
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 26,937

Rep: Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017Reputation: 8017
Quote:
Originally Posted by trickydba View Post
I have. I tried TB0ne's suggestion, which is:

sed -i 's/\r/\n/' testfile <------ with no extension

But it converts all lines to just have "LF". All lines have "CR LF" except the header line, which shows only "LF". I need only the remaining line(header) to show "CR LF".
Ok, but it would be helpful to actually see part of what you've got to work with. Again, without an actual 'something' to look at, details are sparse.

Typically, things go from Linux TO Windows, so the \n (LF) is replaced with a CRLF (\r). Try reversing the positions in the command I gave you initially. Also, the behavior you described shouldn't happen...without specifying the entire file with a 'g', sed should only replace the FIRST occurrence. Try:
Code:
sed -i 's/\n/\r/1' <file name>
...and see what happens.
 
1 members found this post helpful.
Old 11-21-2016, 10:21 AM   #11
cliffordw
Member
 
Registered: Jan 2012
Location: South Africa
Posts: 509

Rep: Reputation: 203Reputation: 203Reputation: 203
try this instead:

Code:
sed -i '1s/$/\r/' testfile
Or try the dos2unix/unix2dos approach...
 
2 members found this post helpful.
Old 11-21-2016, 10:22 AM   #12
szboardstretcher
Senior Member
 
Registered: Aug 2006
Location: Detroit, MI
Distribution: GNU/Linux systemd
Posts: 4,278

Rep: Reputation: 1694Reputation: 1694Reputation: 1694Reputation: 1694Reputation: 1694Reputation: 1694Reputation: 1694Reputation: 1694Reputation: 1694Reputation: 1694Reputation: 1694
I think he is looking to change only the first line (the header) to have a CR/LF at the end, which only has a LF currently.. That would be:

Code:
sed -i 's|\n|\r\n|1' <file name>
 
2 members found this post helpful.
Old 11-21-2016, 10:36 AM   #13
trickydba
Member
 
Registered: Nov 2016
Location: Atlanta,Georgia
Posts: 310

Original Poster
Rep: Reputation: Disabled
@szboardstretcher........... I would've thought your suggestion would resolve my issue, but the 1st line(header) still shows only 'LF'.

What is happening after attempting to add "LF CR" to the 1st line of the text file is a timestamp is added to the file along with an extension. Would this affect the text file after adding CR LF ?

Last edited by trickydba; 11-21-2016 at 10:39 AM.
 
Old 11-21-2016, 10:43 AM   #14
trickydba
Member
 
Registered: Nov 2016
Location: Atlanta,Georgia
Posts: 310

Original Poster
Rep: Reputation: Disabled
This line of code, suggested by cliffordw added to my bash script has solved my issue:

sed -i '1s/$/\r/' testfile

Now my text file is showing at the end of the line "CR LF".

Thank you for all for such speedy responses and attempts to help!!!
 
Old 11-21-2016, 10:45 AM   #15
cliffordw
Member
 
Registered: Jan 2012
Location: South Africa
Posts: 509

Rep: Reputation: 203Reputation: 203Reputation: 203
Cool. Please remember to mark this thread as "solved".
 
1 members found this post helpful.
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to remove carriage return from a file without creating any other file? sysmicuser Linux - Newbie 12 04-30-2013 11:01 AM
how to find if a file is having return carriage and new line characters ? pradeepdee6 Linux - Newbie 8 01-10-2013 04:28 AM
Cut adding $ and carriage return characters for MAC addresses DaveQB Linux - Software 4 09-24-2009 11:39 PM
Remove New Line or Carriage return from Text File DIMonS Programming 13 10-01-2007 10:31 AM
FAQ: Internal Server Error / Premature End of Script Header / MS-DOS Carriage Return rebel Red Hat 2 04-30-2005 03:11 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 01:27 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration