Need Help in converting


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need Help in converting
# 1  
Old 12-20-2018
Need Help in converting

I have Excel file with the below three columns, i need your expertise in converting this to .csv file delimiter "|"

Excel -
Code:
Serial Number	Serial Name	   Serial Brand
111                   test                     sample
123                   test2                   sample1
134                  134_test3             sample2
135                  135_test4            sample3

Reuested this to converted to .csv file with delimiter "|" and for the column Serial name.. if any of the records having serial number with Serial name, the serial number should be ignored... and the desired csv file should be like below

Code:
Serial Number|Serial Name	|Serial Brand
111|test|sample
123|test2|sample1
134|test3|sample2
135|test4|sample3


Last edited by Corona688; 12-20-2018 at 01:06 PM.. Reason: Code tags for code please
# 2  
Old 12-20-2018
Which is it? Excel, CSV, or flatfile? Looks like a flatfile to me, a comma-separated values file tends to have its values separated by commas. What's the delimiter?
# 3  
Old 12-20-2018
Taking a wild guess that it's tabs which may or may not contain carriage returns:

Code:
awk -F"\t" -v OFS="|" '{ gsub(/\r/,""); $1=$1 }1' inputfile > outputfile

outputfile must not be the same as inputfile.

Last edited by Corona688; 12-20-2018 at 01:04 PM.. Reason: typo
# 4  
Old 12-20-2018
Thanks for the quick reply, Inputs are in Excel. and i need your help in converting this excel to .csv file.

--- Post updated at 05:30 PM ---

After converting the Excel File to .csv file

Excel -
Code:
Serial Number	Serial Name	   Serial Brand
111                   test                     sample
123                   test2                   sample1
134                  134_test3             sample2
135                  135_test4            sample3

Desired output in CSV File - ( | delimiter)

Code:
Serial Number|Serial Name	|Serial Brand
111|test|sample
123|test2|sample1
134|test3|sample2
135|test4|sample3

if you see the row 3 and row4, the "serial number_ " is ignored.. if any.

Last edited by Corona688; 12-20-2018 at 02:11 PM..
# 5  
Old 12-20-2018
CSV means "comma separated values". None of those files contains a single comma.

Once again I'm left to wild-guess that your text files are tab-separated.

Code:
awk -F"\t" -v OFS="|" '{ sub("^" $1 "_", "", $2); $2=$2 } 1' inputfile > outputfile

# 6  
Old 12-20-2018
Quote:
Originally Posted by Corona688
CSV means "comma separated values". None of those files contains a single comma.
Well that's true. But if the separator is some other character than it's still called CSV regularly. Nevertheless, the shown source data is nothing I ever heard to be called "csv".
# 7  
Old 12-20-2018
Attached is the sample Excel, How do i convert this Excel in to pipe delimiter flat file (.csv) through the script? looking to automate this.
Need Help in converting-sample-excelpng
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

converting empty value into NA

I have a set of values separated by a tab ch:12 1 3 4 ch:13 3 3 4 ch:25 1 8 ch:23 2 8 1 There is a missing value in the third column and i would like replace it with NA such that the final output would look like ch:12 1 3 ... (7 Replies)
Discussion started by: johnkim0806
7 Replies

2. Solaris

Help converting my Script

So i have been asked to write a script for a friend who has a server running solaris 11. My problem is the correct syntax i can't seem to get it work with the follow code, i have read through the date man page and found no work around Below is the code for part of the rsync script. The code i... (2 Replies)
Discussion started by: paperghost
2 Replies

3. UNIX for Advanced & Expert Users

Need help converting ctlU (^U) to a \12...

I have a file that ends with a ctlU: > cat -v test.file blah,blah,blah,GEAEA*1*xx0000111xxx^UIEA*xxx0^U would like to replace the ctlU (^U) with a "\12"...due to printers or something. I believe I might be able to utilize the tr command, if I could only identify the correct... (4 Replies)
Discussion started by: mr_manny
4 Replies

4. SCO

library converting

Hi everybody Is there any sco unix command to convert .so library to .a (under sco unix openserver.5.0.6) tnx (2 Replies)
Discussion started by: javad1_maroofi
2 Replies

5. Shell Programming and Scripting

Converting Coins

Hi all, I have never written a script so I guess you could say I am very yellow.:) However, I could use some help.... I need to write a Korn Shell Script that converts coins. Meaning if I print or echo "Please enter amount:" and the user puts in 3.87, I need to know the least amount of coins... (11 Replies)
Discussion started by: tzeman
11 Replies

6. Shell Programming and Scripting

Converting \0 to a \n

Hi - I have seen some similar posts but I am a bit stumped here below is the first line of a 'od -c filename' command. I want to change the \0 to \n 0000000 l s \0 c d - \0 c d . . \0 l s I have tried a sed construct in a script......... sed... (2 Replies)
Discussion started by: ajcannon
2 Replies

7. SCO

Converting

I use Sco_Sv 3.2v5.0.5 with parellel conection using dump terminals and i want to convert them to desktop pc. Anybody knows what hardware and other thing that would be involved? (3 Replies)
Discussion started by: seeyou
3 Replies

8. Shell Programming and Scripting

Converting to Uppercase

I want to convert string into uppercase string. How can i do that ? Ex: Enter the user name: read name show=upper(name) echo $show --- This output should be the uppercase output. Thanks (3 Replies)
Discussion started by: dreams5617
3 Replies

9. UNIX for Dummies Questions & Answers

converting .txt

Hello, I transferred some .txt files from windows to Unix. When i used the editor in Unix to open up the file, all the <cr> show up. How to I get rid of all of them? (4 Replies)
Discussion started by: laila63
4 Replies

10. UNIX for Dummies Questions & Answers

converting kb to mb

When I create filesystems in AIX i often get confused(using smit) When you specify size in aix, it is asked like this SIZE of file system (in 512-byte blocks) I never seem to grasp this, what is the equation to get say 500mb? Or is there a program anyone knows of that does this, like a... (1 Reply)
Discussion started by: csaunders
1 Replies
Login or Register to Ask a Question