Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help with selecting column with awk for a txt file generated by excel

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help with selecting column with awk for a txt file generated by excel
# 1  
Old 09-22-2011
Question Help with selecting column with awk for a txt file generated by excel
I am new to scripting/programming, so I apologize for any novice questions.

I have a tab delimited text file that was saved from excel xls file.

I am trying to select only the third column using awk command. My command line is as below:
Code:
cat test.txt | awk '{print $3}'

However, above command line only generates the first row of the third column. How can I generate a tab delimited text file from excel spread sheet that has correct formatting, so that awk command will select the entire column and not just the first row of the column?

Thank you for all your help. I did try searching in this forum and google, but I could not find how to make the the txt file with correct format.

Thanks again.
Last edited by vbe; 09-22-2011 at 12:57 PM.. Reason: please use code tags for your code and data, thanks
# 2  
Old 09-22-2011
Can you give some sample lines from the text file?
# 3  
Old 09-22-2011
Hi Mobitron,

Here are some of my sample file (sorry for the long row):
Code:
157    160    161    163    167    181    216    247
PGP_AHY56    PGP_MOY83    PGP_JFY63    PGP_JCY63    PGP_SCY57    PGP_MHY59    PGP_JCY84    PGP_JRY66
COC076    COC072    COC084    COC079    COC094    COC105    COC134    COC144
Addicts    Addicts    Addicts    Addicts    Addicts    Addicts    Addicts    Addicts
F    F    M    F    M    M    M    M
49    22    42    42    48    46    21    40
-5.25    0.226190476    -0.785714286    3.952380952    1.892857143    0.833333333    -3.666666667    0
-3.25    -0.773809524    -0.785714286    3.952380952    1.892857143    4.833333333    -0.666666667    -9
-5.25    0.226190476    -0.785714286    -0.047619048    1.892857143    4.833333333    0.333333333    -4
-4.25    -0.773809524    -0.785714286    -5.047619048    1.892857143    0.833333333    -1.666666667    -6
-1.25    -0.773809524    -0.785714286    -6.047619048    1.892857143    2.833333333    -5.666666667    0
-1.25    0.226190476    -0.785714286    -6.047619048    1.892857143    0.833333333    -4.666666667    -4
2.75    2.226190476    2.214285714    0.952380952    1.892857143    -1.166666667    5.333333333    2
0.75    1.226190476    0.214285714    13.95238095    6.892857143    0.833333333    3.333333333    -2
-0.25    -0.773809524    -2.785714286    3.952380952    1.892857143    2.833333333    2.333333333    0
-0.25    -0.773809524    -5.785714286    5.952380952    -0.107142857    1.833333333    -1.666666667    0
-2.25    -0.773809524    -4.785714286    -2.047619048    -2.107142857    1.833333333    -1.666666667    0
-1.25    -0.773809524    0.214285714    -6.047619048    -0.107142857    1.833333333    -2.666666667    0
-0.25    -1.773809524    -4.785714286    -1.047619048    3.892857143    -0.166666667    0.333333333    0
-1.25    -2.773809524    -4.785714286    -5.047619048    0.892857143    -2.166666667    -3.666666667    0
-1.25    -1.773809524    -6.785714286    -3.047619048    0.892857143    -2.166666667    -3.666666667    0
-1.25    -0.773809524    -1.785714286    1.952380952    -0.107142857    -2.166666667    -3.666666667    3
-1.25    -1.773809524    -1.785714286    1.952380952    -3.107142857    -2.166666667    -2.666666667    0
-1.25    -1.773809524    -0.785714286    -1.047619048    -3.107142857    -2.166666667    -5.666666667    0
-1.25    1.226190476    -0.785714286    5.952380952    -2.107142857    -1.166666667    2.333333333    0
-4.25    -1.773809524    -0.785714286    8.952380952    3.892857143    0.833333333    2.333333333    0
-2.25    -0.773809524    -0.785714286    5.952380952    4.892857143    1.833333333    1.333333333    0
-1.25    -0.773809524    -0.785714286    0.952380952    4.892857143    1.833333333    -1.666666667    0
0.75    -0.773809524    -0.785714286    1.952380952    3.892857143    -0.166666667    1.333333333    0
-0.25    0.226190476    -0.785714286    0.952380952    -0.107142857    1.833333333    0.333333333    0
-0.25    -0.773809524    -0.785714286    -0.047619048    0.892857143    -2.166666667    -3.666666667    0
-1.25    -0.773809524    -0.785714286    -6.047619048    -4.107142857    -1.166666667    -4.666666667    0
-1.25    -2.773809524    -0.785714286    -6.047619048    -6.107142857    -2.166666667    -5.666666667    0
-1.25    -0.773809524    -0.785714286    -6.047619048    -5.107142857    -1.166666667    -2.666666667    0
-2.25    -1.773809524    -0.785714286    -6.047619048    -5.107142857    -1.166666667    -3.666666667    0
-2.25    -1.773809524    -0.785714286    -6.047619048    -4.107142857    1.833333333    -2.666666667    3
-4.25    1.226190476    -0.785714286    -6.047619048    -5.107142857    -1.166666667    2.333333333    0
-2.25    0.226190476    0.214285714    6.952380952    4.892857143    2.833333333    4.333333333    0
-0.25    0.226190476    1.214285714    3.952380952    6.892857143    2.833333333    3.333333333    0
-1.25    1.226190476    0.214285714    0.952380952    7.892857143    1.833333333    2.333333333    0
-1.25    0.226190476    -0.785714286    -5.047619048    6.892857143    1.833333333    1.333333333    0
0.75    -0.773809524    -0.785714286    5.952380952    6.892857143    -0.166666667    1.333333333    1
-1.25    -1.773809524    -0.785714286    0.952380952    3.892857143    -2.166666667    -1.666666667    1
-1.25    -3.773809524    -0.785714286    -3.047619048    -1.107142857    -2.166666667    -3.666666667    0
-1.25    -1.773809524    -0.785714286    -0.047619048    -5.107142857    -4.166666667    -3.666666667    0
-1.25    -1.773809524    -0.785714286    -1.047619048    -5.107142857    -2.166666667    -3.666666667    0
-1.25    -1.773809524    -0.785714286    3.952380952    -4.107142857    -0.166666667    -4.666666667    0
-1.25    -0.773809524    -0.785714286    -6.047619048    -8.107142857    -1.166666667    -3.666666667    0
-1.25    -0.773809524    -0.785714286    -6.047619048    1.892857143    -1.166666667    -4.666666667    0
-1.25    -0.773809524    -0.785714286    -1.047619048    -5.107142857    -1.166666667    -3.666666667    0
-1.25    -0.773809524    -0.785714286    -1.047619048    -5.107142857    -4.166666667    -3.666666667    -7
-1.25    -0.773809524    -0.785714286    -3.047619048    -5.107142857    -6.166666667    -3.666666667    -6
-1.25    -0.773809524    -0.785714286    -4.047619048    -5.107142857    -6.166666667    -3.666666667    -8
-0.25    -0.773809524    -0.785714286    -6.047619048    -5.107142857    -5.166666667    -3.666666667    -8
0.75    3.226190476    2.214285714    -6.047619048    5.892857143    4.833333333    5.333333333    -6
3.75    7.226190476    7.214285714    13.95238095    11.89285714    6.833333333    6.333333333    3
4.75    7.226190476    6.214285714    1.952380952    10.89285714    6.833333333    5.333333333    3
3.75    8.226190476    2.214285714    4.952380952    7.892857143    4.833333333    6.333333333    5
4.75    7.226190476    2.214285714    7.952380952    7.892857143    4.833333333    7.333333333    3
4.75    7.226190476    2.214285714    10.95238095    8.892857143    6.833333333    7.333333333    5
2.75    -0.773809524    -1.785714286    2.952380952    6.892857143    -6.166666667    7.333333333    5
-1.25    -1.773809524    -0.785714286    -1.047619048    -0.107142857    -5.166666667    3.333333333    -6
-1.25    -1.773809524    -0.785714286    -0.047619048    -4.107142857    -4.166666667    1.333333333    -3
-1.25    -1.773809524    -0.785714286    -4.047619048    -6.107142857    -4.166666667    -1.666666667    -2
1.75    -1.773809524    -0.785714286    -6.047619048    -6.107142857    -4.166666667    -2.666666667    -3
1.75    -1.773809524    -0.785714286    -6.047619048    -6.107142857    -4.166666667    -3.666666667    -4
-0.25    -1.773809524    -0.785714286    -6.047619048    -6.107142857    2.833333333    1.333333333    -3
5.75    3.226190476    2.214285714    11.95238095    9.892857143    4.833333333    2.333333333    7
5.75    5.226190476    3.214285714    13.95238095    11.89285714    6.833333333    5.333333333    5
4.75    5.226190476    4.214285714    5.952380952    11.89285714    7.833333333    6.333333333    5
5.75    3.226190476    4.214285714    12.95238095    10.89285714    7.833333333    7.333333333    5
4.75    5.226190476    3.214285714    -1.047619048    10.89285714    8.833333333    7.333333333    7
2.75    -1.773809524    2.214285714    -3.047619048    7.892857143    -6.166666667    6.333333333    9
-1.25    -2.773809524    -0.785714286    -6.047619048    2.892857143    -6.166666667    -1.666666667    -3
-2.25    -2.773809524    -0.785714286    -6.047619048    -8.107142857    -6.166666667    -3.666666667    -4
-1.25    -2.773809524    -0.785714286    -6.047619048    -1.107142857    -5.166666667    -3.666666667    -4
-2.25    -2.773809524    -0.785714286    -6.047619048    -6.107142857    -4.166666667    -3.666666667    -3
-2.25    -2.773809524    -0.785714286    -6.047619048    -7.107142857    -5.166666667    -3.666666667    -3
-2.25    -1.773809524    -0.785714286    -6.047619048    -0.107142857    -5.166666667    1.333333333    3
1.75    2.226190476    1.214285714    -6.047619048    -8.107142857    2.833333333    3.333333333    5
4.75    3.226190476    4.214285714    -6.047619048    -7.107142857    5.833333333    6.333333333    6
4.75    3.226190476    5.214285714    9.952380952    -8.107142857    4.833333333    7.333333333    8
6.75    3.226190476    6.214285714    10.95238095    -6.107142857    6.833333333    8.333333333    7
6.75    3.226190476    6.214285714    13.95238095    -6.107142857    8.833333333    4.333333333    9
5.75    4.226190476    6.214285714    13.95238095    -5.107142857    2.833333333    4.333333333    9
1.75    -6.773809524    -0.785714286    -6.047619048    -4.107142857    -0.166666667    -2.666666667    -6
-1.25    -1.773809524    -0.785714286    -6.047619048    -6.107142857    -5.166666667    -3.666666667    -6
-2.25    -2.773809524    -0.785714286    -6.047619048    -6.107142857    -6.166666667    -5.666666667    -6
-2.25    -2.773809524    -0.785714286    -6.047619048    -7.107142857    -5.166666667    -4.666666667    -3
-2.25    -1.773809524    -0.785714286    -6.047619048    -7.107142857    -6.166666667    -4.666666667    0

Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!
Last edited by vgersh99; 09-22-2011 at 01:34 PM.. Reason: code tags, pls!
# 4  
Old 09-22-2011
That works fine for me

Code:
$ awk '{ print $3 }' xl.txt | more
161
PGP_JFY63
COC084
Addicts
M
42
-0.785714286
-0.785714286
-0.785714286
-0.785714286
-0.785714286
-0.785714286
2.214285714
0.214285714
-2.785714286
-5.785714286
-4.785714286
0.214285714
-4.785714286

Are you sure your text file is in plain text format? Run this:

Code:
wc -l filename.txt

# 5  
Old 09-22-2011
when I do:
Code:
wc -l filename.txt

I get
Code:
0 filename.txt

Last edited by Franklin52; 09-23-2011 at 03:46 AM.. Reason: Please use code tags, thank you
# 6  
Old 09-22-2011
Quote:
Originally Posted by SangLad
when I do:
wc -l filename.txt

I get
0 filename.txt
OK, so I suspect the file is not in plain text format or doesn't contain any valid newline characters. Excel might have exported it in the wrong format.

Try the following

Code:
tr "\r" "\n" < filename.txt > newfilename.txt

And run the awk command again on newfilename.txt.
# 7  
Old 09-22-2011
Thank you again for your help!

I get the following output:
Code:
tr "\r" "\n" master_tab_delimited_file.txt > new_master.txt
usage: tr [-Ccsu] string1 string2
       tr [-Ccu] -d string1
       tr [-Ccu] -s string1
       tr [-Ccu] -ds string1 string2


Moderator's Comments:
Mod Comment Video tutorial on how to use code tags in The UNIX and Linux Forums.
Last edited by Franklin52; 09-23-2011 at 03:46 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to copy a column of multiple files and paste into new excel file (next to column)?

I have data of an excel files as given below, file1 org1_1 1 1 2.5 100 org1_2 1 2 5.5 98 org1_3 1 3 7.2 88 file2 org2_1 1 1 2.5 100 org2_2 1 2 5.5 56 org2_3 1 3 7.2 70 I have multiple excel files as above shown. I have to copy column 1, column 4 and paste into a new excel file as... (26 Replies)
Discussion started by: dineshkumarsrk
26 Replies

2. UNIX for Beginners Questions & Answers

How to insert data into black column( Secound Column ) in excel (.XLSX) file using shell script?

Source Code of the original script is down below please run the script and try to solve this problem this is my data and I want it column wise 2019-03-20 13:00:00:000 2019-03-20 15:00:00:000 1 Operating System LAB 0 1 1 1 1 1 1 1 1 1 0 1 (5 Replies)
Discussion started by: Shubham1182
5 Replies

3. UNIX for Beginners Questions & Answers

Copy txt file into excel vbscript

Hi everybody, I am working on Windows 10 and using cygwin. I have a vbscript as follows: Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set wb1 = objExcel.Workbooks.open("\\files\share\path\file1.xlsx") wb1.Worksheets("Sheet1").Range("A1:CR89").Clear Set... (0 Replies)
Discussion started by: supernono06
0 Replies

4. Shell Programming and Scripting

Converting txt file to Excel file

Hi All, I have a text file with below content. TradeDate Name SecurityMnc ReasonDesc ======================================= 20120501 Robin ABC FO System Defect 20120502 Robin ABC FO System Defect I would want this in an excel file in 4 columns,... (3 Replies)
Discussion started by: robinbannis
3 Replies

5. Shell Programming and Scripting

Store a column from an excel sheet (exported to txt) into a variable

I typically pull a bunch of data via SQL that lists a bunch of users and the server on which they want to access, as well as other attributes, in one row of an excel sheet and the number of rows is directly proportionate to the number of users. I'm trying to write a loop to read each line of the... (2 Replies)
Discussion started by: MaindotC
2 Replies

6. Shell Programming and Scripting

Script to Grep column 3 from csv file generated yesterday

Hello, Can any one please assist how to scirpt it: Every day a new log file is create and I want to process only the one generated yesterday and get the data of column 3 and 6. For example today's date is 24 then I want to get the data of log file created on 23rd. Log Files in... (7 Replies)
Discussion started by: sureshcisco
7 Replies

7. UNIX for Dummies Questions & Answers

Save Excel file as .txt in UNIX format

I have some files created in Excel that have to be saved as .txt files in order to load them into our accounting system. I can save the files as .txt files through Excel, but I then have to open them in TextPad and do a save as to change the Format from PC to UNIX. Is there a way to skip this step... (2 Replies)
Discussion started by: jroyalty
2 Replies

8. Shell Programming and Scripting

AWK CSV to TXT format, TXT file not in a correct column format

HI guys, I have created a script to read 1 column in a csv file and then place it in text file. However, when i checked out the text file, it is not in a column format... Example: CSV file contains name,age aa,11 bb,22 cc,33 After using awk to get first column TXT file... (1 Reply)
Discussion started by: mdap
1 Replies

9. Shell Programming and Scripting

Issues in the Format in excel file generated from unix machine

Hi, I have generated a report that contains many columns and since I need ir in excel format.. I just renamed te file to excel as follows: cp vijay.txt vijay.xls I have just attached this spreadsheet in the mail and I am getting it to my mail id. But, in the output excel, the columns that... (10 Replies)
Discussion started by: Vijay06
10 Replies

10. Shell Programming and Scripting

Need a script to Append date to generated .txt file

Hi, Can anyone plz share their experience with - Building shell script to append the file with date in following format- Filename_MMDDYYYY.txt Thanks in advance (2 Replies)
Discussion started by: prince_of_focus
2 Replies
Login or Register to Ask a Question