Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Trouble reading from a tab delimited excel file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Trouble reading from a tab delimited excel file
# 8  
Old 06-02-2016
Given myFile:
Code:
9545641 123     "Neighbor and Labrador,Canada"  54895   'CANADA'
9456465 456     "Neighbor and Labrador,Canada"  54893   'CANADA'
8746512 789     "Neighbor and Labrador,Canada"  54893   'CANADA'

running awk -F '\t' '{print "(" $1,$2,$3,$4,$5 ");"}' OFS=, myFile produces:
Code:
(9545641,123,"Neighbor and Labrador,Canada",54895,'CANADA');
(9456465,456,"Neighbor and Labrador,Canada",54893,'CANADA');
(8746512,789,"Neighbor and Labrador,Canada",54893,'CANADA');

Is that what you've expected?
# 9  
Old 06-02-2016
Quote:
Originally Posted by dan139
.
.
.
The output becomes:
Code:
);(8746512,789,"Neighbor and Labrador,Canada",54893,'CANADA'

.
.
.
I guess that's due to "DOS line terminators" using <CR><NL> instead of *nix <NL> alone. How did you produce the input file? There's various methods to eliminate the <CR> char; one of those is adding sth like sub (/\r$/, "") to your awk script.
# 10  
Old 06-02-2016
Quote:
Originally Posted by RudiC
I guess that's due to "DOS line terminators" using <CR><NL> instead of *nix <NL> alone. How did you produce the input file? There's various methods to eliminate the <CR> char; one of those is adding sth like sub (/\r$/, "") to your awk script.
I produced my file by saving my excel spreadsheet as a tab delimited file & then it produced a file with formatting like this:

Code:
9545641	123	"Neighbor and Labrador,Canada"	54895	'CANADA'
9456465	456	"Neighbor and Labrador,Canada"	54893	'CANADA'
8746512	789	"Neighbor and Labrador,Canada"	54893	'CANADA'

Not sure I understand what your saying by adding sth. What would full comman dbe?
# 11  
Old 06-02-2016
Code:
awk -F '\t' '{sub(/\r$/, "");print "(" $1,$2,$3,$4,$5 ");"}' OFS=, myFile

# 12  
Old 06-02-2016
Quote:
Originally Posted by vgersh99
Code:
awk -F '\t' '{sub(/\r$/, "");print "(" $1,$2,$3,$4,$5 ");"}' OFS=, myFile

This worked perfectly. Thanks.

---------- Post updated at 03:37 PM ---------- Previous update was at 03:31 PM ----------

Sorry One more question, if I wanted field 2 to be printed with quotes around it e.g.

Code:
(9545641,"123","Neighbor and Labrador,Canada",54895,'CANADA');

Would I just make the command have quotes around "$2"? It didn't work, says its an error.

Nvm got it-
Just add:
Code:
awk -v q="'" '{print q $2 q}' Input.txt
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Replace a column in tab delimited file with column in other tab delimited file,based on match

Hello Everyone.. I want to replace the retail col from FileI with cstp1 col from FileP if the strpno matches in both files FileP.txt ... (2 Replies)
Discussion started by: YogeshG
2 Replies

2. UNIX for Beginners Questions & Answers

Convert Excel File (xls) to tab delimited text file on AIX

Hi i have a problem in my job i try to convert an excel file (xls extention) to text file (tab delimited), but no result with this comand cat xxx.xls > xxx.txt Do you have eny idea? PS: sorry for my english Thanks!! (4 Replies)
Discussion started by: frisso
4 Replies

3. UNIX for Dummies Questions & Answers

Need to convert a pipe delimited text file to tab delimited

Hi, I have a rquirement in unix as below . I have a text file with me seperated by | symbol and i need to generate a excel file through unix commands/script so that each value will go to each column. ex: Input Text file: 1|A|apple 2|B|bottle excel file to be generated as output as... (9 Replies)
Discussion started by: raja kakitapall
9 Replies

4. Shell Programming and Scripting

How to make tab delimited file to space delimited?

Hi How to make tab delimited file to space delimited? in put file: ABC kgy jkh ghj ash kjl o/p file: ABC kgy jkh ghj ash kjl Use code tags, thanks. (1 Reply)
Discussion started by: jagdishrout
1 Replies

5. Shell Programming and Scripting

Help with converting Pipe delimited file to Tab Delimited

I have a file which was pipe delimited, I need to make it tab delimited. I tried with sed but no use cat file | sed 's/|//t/g' The above command substituted "/t" not tab in the place of pipe. Sample file: abc|123|2012-01-30|2012-04-28|xyz have to convert to: abc 123... (6 Replies)
Discussion started by: karumudi7
6 Replies

6. UNIX for Dummies Questions & Answers

tab delimited file that is not tab delimited.

Hi Forum I have a tab delimited file that opens well in Openoffice calc (excel). But when I perform any operation in command line, it reads the file incorrectly. When I 'save As' the same file in office as tab delimited then it works fine. The file that I think is tab delimited is actually... (8 Replies)
Discussion started by: imlearning
8 Replies

7. UNIX for Dummies Questions & Answers

Converting Space delimited file to Tab delimited file

Hi all, I have a file with single white space delimited values, I want to convert them to a tab delimited file. I tried sed, tr ... but nothing is working. Thanks, Rajeevan D (16 Replies)
Discussion started by: jeevs81
16 Replies

8. Shell Programming and Scripting

Reading columns in tab delimited file

I want to read only one column in "|" delimited file and write that column to a new file. For Ex: Input File 1|abc|324|tt 2|efd|11|cbcb 3||1|fg 4|ert|23|88 Output : I want to read column 3 in diff file. 324 11 1 88 Can anyone give me inputs on this ? (2 Replies)
Discussion started by: net
2 Replies

9. Shell Programming and Scripting

Converting Tab delimited file to Comma delimited file in Unix

Hi, Can anyone let me know on how to convert a Tab delimited file to Comma delimited file in Unix Thanks!! (22 Replies)
Discussion started by: charan81
22 Replies

10. Shell Programming and Scripting

tab delimited file to commas

Hi there Just wondered if someone could help me out I have a file that has been delimited by tabs, ie field1<tab>fiield2<tab>field3 Does anybody know a command that will convert tabs to commas throughout the entire file? Note: there are a number of unpopulated fields in the file so... (6 Replies)
Discussion started by: hcclnoodles
6 Replies
Login or Register to Ask a Question