rss
Search : in
By : Relevance Date Username
Statut : Not resolved

Using awk to extract Excel columns

sujathagio, on Monday April 14, 2008 12:45:32 PM
Hello,


Hi All,
I have Excel files (with TAB delimiter) that have different setting of columns. For Example:

1st Excel file has 18 fields:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

2nd and 3rd are the same which have 14 fields:
1 2 3 4 5 6 7 8 9 10 11 12 13 14

From each file I want to extract only column 1 thru 8 and last column (18/14 depends on the file). After extracting columns, for each file, I need to add the TODAY date column to the end of each file as a last column to indicate the current updating field.
In additional of this I need also to change TAB to '|' (pipe) delimiter in order to download to database.
Thank in Advance.
Configuration: Windows XP
Internet Explorer 6.0
Reply to sujathagio  Report this message to moderators Go to last message

1


  • This message seems useful, vote!
  • Report this message to moderators
jipicy, on Monday April 14, 2008 03:59:55 PM
Hi,

Try this with "sed" :
$ var=$(date '+%d-%m-%y')
$ sed -e  "s/\t/\n/8;s/\n.*\t/\t/;s/$/\t$var/;s/\t/|/g" file > new_file
;-))
JP - Eleveur de pingouins -
Faites un geste pour l'environnement, fermez vos fenetres et adoptez un manchot.
   
Reply to jipicy

2


  • 1    This message seems useful, vote!
  • Ce message ne vous semble pas utile, votez !
  • Report this message to moderators
vandi.vnd, on Tuesday April 15, 2008 07:56:47 AM
Hello


You can change the date format from %D to whatever you want, for example $(date +%Y%m%d)
The $NF means the last column/word/field, so this code will work for all three of your files.

awk does not have an internal date function, so I am passing the current date to it as a parameter.

Note that the first line of the code ends with a single quote.

awk -v today="$(date +%D)" '
BEGIN {OFS="|"}
{print $1,$2,$3,$4,$5,$6,$7,$8,$NF,today}
' infile

1|2|3|4|5|6|7|8|18|12/26/07
   
Reply to vandi.vnd

3


  • This message seems useful, vote!
  • Report this message to moderators
 Bernie, on Monday May 19, 2008 01:48:46 PM
Hellow, am a student of Msc. DCSE. Am using ns2 and i have to sum up 2 columns of data and plot the results. How do i do this in awk.
Secondly, how do i set up a proxy server in NS2?
Thanks so much.
BERNARD Msc. DCSE
   
Reply to Bernie
Ask your question Reply
Software found in the relevant downloads
Download Excel Viewer 2007 Excel Viewer - With Excel Viewer, you can open, view, and print Excel workbooks, even if you don't have Excel installed. You can also copy...Category: Office suite
License: Freeware/gratuit
Download MediaCoder 0.6.1.4140MediaCoder - MediaCoder is a free universal batch media transcoder, which nicely integrates most popular audio/video codecs and tools...Category: Video editing
License: Open Source
Download IZArc 3.81.1550IZArc - IZArc is the ultimate freeware archive utility supporting many archive formats like: 7-ZIP, A, ACE, ARC, ARJ, B64, BH, BIN,...Category: Compression/Decompression
License: Freeware/gratuit
Download ALZip 6.7ALZip - FREE for home users, ALZip is the easiest to use, most powerful, and by far the cutest compression utility available. ...Category: Compression/Decompression
License: Freeware/free
More freeware about « Using awk to extract Excel columns »