Visit Our UNIX and Linux User Community


AWK delimiting


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting AWK delimiting
# 1  
Old 09-04-2002
Question AWK delimiting

I have a directory of files DATA1,DATA2,DATA3, etc... each file has 1 column of data

Example:

File DATA1

name
date
time
time requested
approved

I need to change these to CSV files so I can import the data. I believe AWK is what i need but I'm new to AWK and can't seem to get the syntex correct.

awk -F , '{ print $1 }' DATA1 > DATA.TEST

can someone help me ???
# 2  
Old 09-04-2002
use join in vi... with a loop

I used to have this problem when transferring client lists to Foxpro or Excel.

You are going to have to run a for loop or some kind of loop. Do all of your data files have the same number of fields? I keep thinking of the "join" function of vi. I know you can join X lines with the command in command mode "7 J". This will join 7 lines together, space delimited which should be fine for your purposes or you can then use AWK to fill in commas.

OR try this.

Forgive me if the logic is wrong this was off the top of my head.
The ; is for a new record. You can then use AWK to break it out 1 record per line from there. I think there is a way to do a CRLF but I don't remember right now.

i=1
for name in `cat file`
do
if [$i -le 8 ]
awk '{ print $1 "," }' >> file.out
i=i+1
else
awk '{ print ";" }' >> file.out
i=1
done


I will have to research this, but you probably can figure it out from here.


////EDIT HERE:

That first attempt doesn' t seem to work. Here is something else I have tried.

#!/bin/ksh

for name in `cat todd`
do
for i in `1 2 3 4 5 6 7 8`
do
awk '{ print $name "," }' >> file.out
if [$i -eq 8]
then
awk '{ print ";" }' >> file.out
fi
done
done

Although I can't get it to work either... Heheh Smilie

Smilie Smilie

Last edited by Kelam_Magnus; 09-04-2002 at 03:37 PM..
# 3  
Old 09-04-2002
Re: AWK delimiting

Quote:
Originally posted by barrro
I have a directory of files DATA1,DATA2,DATA3, etc... each file has 1 column of data

Example:

File DATA1

name
date
time
time requested
approved

I need to change these to CSV files so I can import the data. I believe AWK is what i need but I'm new to AWK and can't seem to get the syntex correct.

awk -F , '{ print $1 }' DATA1 > DATA.TEST

can someone help me ???
If your files only have 1 set of name/date/time/time requested/approved entries then this will work.

If you have multiple lines i would put a counter in teh foreach loop to restart every 6th line.

Code:
#! /usr/bin/perl -w

$TESTFILE="test.file";
$OUTPUT="new.file";
open (INPUT, $TESTFILE) || die "NO GO ($!)";
open (OUTPUT, ">${OUTPUT}") || die "NO GO ($!)";

foreach (<INPUT>) { chomp, push(@contents,$_); };

$_ = join(",",@contents);
print OUTPUT $_;;

Code:
[hostname]optimus_P$cat new.file
name,date,time,time requested,approved
[hostname]optimus_P$cat test.file
name
date
time
time requested
approved

# 4  
Old 09-05-2002
Maybe a bit more clarity on what you're trying to do here....I took it differently to the others....

first file

name
date
time
time requested
approved

second file

john smith
20020808
12:00PM
12:01PM
Yes

Third File

comment for line 1
comment for line 2
comment for line 3
comment for line 4
comment for line 5



Then paste would work and you can specify your delimiter.
paste -s -d"," file1 file2 file3

Is this what you are after. Careful with awk...as there are spaces in your file and these will be treated as delimiters.
# 5  
Old 09-06-2002
Yes, need more clarity, but I think each file is an independent set of data, rather than needing a paste job, one line from each. On that assumption, the following code will create one comma-delimited line for each 5 lines of input. Each file is assumed to have a multiple of 5 lines. In the event that a file is incorrect and does not contain a multiple of 5 lines, output lines could be shifted for that file, but the FNR check will ensure alignment at start of next file. I assume an entire line represents one data column, such as "john smith" which includes a space.
Code:
awk '{\
if (FNR==1 && linecount!=0)
  {print ""
   linecount=0}
linecount++
if (linecount<5)
   printf "%s,",$0
else
  {print $0
   linecount=0}
}' DATA?

Jimbo
# 6  
Old 09-07-2002
This worked for me in a similar situation as you described...

It works in two parts...

First the join command to create the delimited file from the two separate files. (The -j1, and the ~/file1 correlate to one another, and the -j2 and the ~/file2 correlate to one another):

join -a1 -t"|" -j1 1 -j2 1 -o 1.1 -o 1.2 /directory/path/here/file1 /directory/path/here/file2 > joinedfile.name

This join compares the first field of each file and joins them together if a match if found...Kind of like a query in a database matching keys. The -o specifies which fields are kept and their order in the final output file. So 1.1 would mean keep field one in file one, and 2.1 would mean keep field one in file two...

You can join multiple files this way by simply adding another line of code and specifying that the output concantenate (>>) to the end of the file you're building.

join -a1 -t"|" -j1 1 -j2 1 -o 1.1 -o 2.1 1.2 /directory/path/here/file3 /directory/path/here/file2 >> joinedfile.name


then the awk command. The delimiter is set using the switch \|:

awk -F"|" '{printf "%s\|%s\|\n",$1,$2}' joinedfile.name > finalfile.name

Let me know if this makes sense or if it even serves your purpose.

Previous Thread | Next Thread
Test Your Knowledge in Computers #188
Difficulty: Easy
Python is an interpreted, high-level, general-purpose programming language, created by Linus Torvalds and first released in 1991
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk output yields error: awk:can't open job_name (Autosys)

Good evening, Im newbie at unix specially with awk From an scheduler program called Autosys i want to extract some data reading an inputfile that comprises jobs names, then formating the output to columns for example 1. This is the inputfile: $ more MapaRep.txt ds_extra_nikira_usuarios... (18 Replies)
Discussion started by: alexcol
18 Replies

2. Shell Programming and Scripting

Passing awk variable argument to a script which is being called inside awk

consider the script below sh /opt/hqe/hqapi1-client-5.0.0/bin/hqapi.sh alert list --host=localhost --port=7443 --user=hqadmin --password=hqadmin --secure=true >/tmp/alerts.xml awk -F'' '{for(i=1;i<=NF;i++){ if($i=="Alert id") { if(id!="") if(dt!=""){ cmd="sh someScript.sh... (2 Replies)
Discussion started by: vivek d r
2 Replies

3. Shell Programming and Scripting

delimiting using sed command

Hi, I have an output after performing a grep: FAN and i did awk '{print $2}' it shows: is there a way, by using sed that it will only show output as NO_FAULT without the brackets ? i tried doing sed 's/^*//' but the output is NO_FAULT] with the ] at the... (12 Replies)
Discussion started by: mosies
12 Replies

4. Shell Programming and Scripting

HELP with AWK one-liner. Need to employ an If condition inside AWK to check for array variable ?

Hello experts, I'm stuck with this script for three days now. Here's what i need. I need to split a large delimited (,) file into 2 files based on the value present in the last field. Samp: Something.csv bca,adc,asdf,123,12C bca,adc,asdf,123,13C def,adc,asdf,123,12A I need this split... (6 Replies)
Discussion started by: shell_boy23
6 Replies

5. Shell Programming and Scripting

awk command to compare a file with set of files in a directory using 'awk'

Hi, I have a situation to compare one file, say file1.txt with a set of files in directory.The directory contains more than 100 files. To be more precise, the requirement is to compare the first field of file1.txt with the first field in all the files in the directory.The files in the... (10 Replies)
Discussion started by: anandek
10 Replies

6. Shell Programming and Scripting

Problem with awk awk: program limit exceeded: sprintf buffer size=1020

Hi I have many problems with a script. I have a script that formats a text file but always prints the same error when i try to execute it The code is that: { if (NF==17){ print $0 }else{ fields=NF; all=$0; while... (2 Replies)
Discussion started by: fate
2 Replies

7. Shell Programming and Scripting

scripting/awk help : awk sum output is not comming in regular format. Pls advise.

Hi Experts, I am adding a column of numbers with awk , however not getting correct output: # awk '{sum+=$1} END {print sum}' datafile 2.15291e+06 How can I getthe output like : 2152910 Thank you.. # awk '{sum+=$1} END {print sum}' datafile 2.15079e+06 (3 Replies)
Discussion started by: rveri
3 Replies

8. Shell Programming and Scripting

Awk problem: How to express the single quote(') by using awk print function

Actually I got a list of file end with *.txt I want to use the same command apply to all the *.txt Thus I try to find out the fastest way to write those same command in a script and then want to let them run automatics. For example: I got the file below: file1.txt file2.txt file3.txt... (4 Replies)
Discussion started by: patrick87
4 Replies

9. Shell Programming and Scripting

AWK- delimiting the strings and matching the fields

Hello, I am newbie in awk. I have just started learning it. 1) I have input file which looks like: {4812 4009 1602 2756 306} {4814 4010 1603 2757 309} {8116 9362 10779 } {10779 10121 9193 10963 10908} {1602 2756 306 957 1025} {1603 2757 307} and so on..... 2) In output: a)... (10 Replies)
Discussion started by: kajolo
10 Replies

10. Shell Programming and Scripting

help with delimiting columns with a space

Hi all, i am writting a script to fix some problems we have with data that we need that contains 1000s of records. I have a text file with 3 columns of data. The problem is there should be a space between the end of the first column and the start of the second column. The majority of the data is... (7 Replies)
Discussion started by: borderblaster
7 Replies

Featured Tech Videos