Find Data in test file and write each out to a line
I have a .csv file that has been create from a google form and I need to extract the data from it that has been entered by users.
The CSV will have anywhere between 100 and 1000 lines which comprise entr data for a sports carnival
A few typical line is shown here to show the problem I have
From this data, I need to extract the data in the last part of the lines,
This data can be Alpha or numeric or combination of and between 2-4 chanractes in length. It could be in any position within the last blank fields.
there could be anywhere between 1 and 6 entries on each line that ineed to capture.
For each entry, I need to write out a single formatted line which I already have most of in the form
How do I capture to a variable, The Last data on each line from random positions and then proceed to the next line when done?
I am stumped!
Thanks in advance
Ken
---------- Post updated at 04:20 PM ---------- Previous update was at 03:51 PM ----------
I had thought of using cut to query the first Column to test ( cut -d , -f9 ), if it has a non zero value, then must have data so write line, and then increment column and test again.
If value = LF? then proceed to next line
is there a better way that interativley testing each column?
You seem to want to use awk to produce one line of output as you loop through $10 through $NF on each line of your input file. But I have no idea where some of the data you are showing in your sample output comes from. Your output contains "D", "Name2", "KIN", "M", and "1" that don't appear anywhere in the 1st line of your input.
Or, you could use the shell to read lines setting positional parameters or an array variable to the values found on each input line and loop through positional parameters 10 through $# or array elements 9 through ${#array}-1.
Without knowing what operating system and shell you're using, it is hard to make any specific suggestions that might work in your environment.
Hi Don
The extra data you mention is hardcoded in the output format, or comes from the input line which I have already, written to variables as i need.
No issue with that it is just how i get the data from the line quoted and write to a file for each one
My OS linux and Bash is my shell
Just to clarify, i have highlighted in red the part of the line i need to extract from and to write each of these in turn to a single variable ( ie event)
for the line below, i need to write 4 individual lines to the output file, one for each event.
Saying you have some variables without telling us what language you're using doesn't really help us much. Telling us you have some variables without telling us the variable names doesn't help us much. Please help us help you.
Tell us what operating system you're using.
Tell us what shell you're using.
Show us the code you're currently using to print everything except the field you have shown in red.
Then we can help you fill in the data you're shown in red.
As mentioned in the previous reply
OS = Linux, Bash Shell
Part of my existing code below which pulls my required variables out already
Code:
#**********************************************************************
#Main Proram code
#**********************************************************************
while read line
do
echo $line
echo
cname=$(echo $line | cut -d, -f2) # Extract Centre Short name
IFS="," read X csname centnum < <(grep "^${cname}," centers.txt) # "read the Center Name and ShortName from External file eg $centnum,$cname,$csname
athnum=$(echo $line | cut -d, -f4) # Extract Athlete Number
firstname=$(echo $line | cut -d, -f5) # Extract Athlete First Name
lastname=$(echo $line | cut -d, -f6) # Extract Athlete Last Name
gender=$(echo $line | cut -d, -f7) # Extract Athlete Gender
age=$(echo $line | cut -d, -f8) # Extract Athlete Age
#extract event numbers from line
echo D';'$lastname';'$firstname';;'$gender';;'$csname';'$cname';'$age';;'$event';;M;1;'$athnum';;;;' >> $outfile
done <"$file" # specifly the import file to work on
The only bit i am having an issue with is writing the indivdual event codes in turn to a variable so I can write my output line
Thought to do something like recusively check each colun in the input file and test to see if that is valid data, and if so write out the the line. Every check would have to check for the CR to find endof line and then go to nex one
Code:
column = 9
until [ some Test for end of line ] #Test for end of line
echo "Column to test is" $column
do
event=$(echo $line | cut -d, -f$column) # Event that athlete has entered
echo $event
((column++))
done
done
yes I know this does not work as is, but is this an efficient ( and even possible way to do it or is there some better methods?
My full script so far ( with RudiC's )addition is here
Code:
#set initial variables
# ask for the input filename from Zenity
file=`zenity --file-selection --title="Select a Wardells Input File"`
case $? in
0)
echo "\"$file\" selected.";;
1)
echo "No file selected.";;
-1)
echo "An unexpected error has occurred.";;
esac
name=${file%.*} # finds the Name part of the input filename
fileevt="$name.evt" # Output filename for event listing
outfile="$name.txt" # Output filenme
echo "the input file name to use will be $file"
echo $name
echo $outfile
centnum=53 # Center Number
cname="Center Name" # Center Name
csname="Center short Name" # Center Short Name
lastname=Poole # Athlete Last Name
firstname=Natalie # Athlete RFirst Name
gender=F # Athlete Sex
age=15 # Athlete Age Group
athnum=1 # Athlete Number
event=100 # Event Code
mmcodes=1 # have MM codes been done?
rm -f $outfile # Delete Output file
#**********************************************************************
#Main Program code
#**********************************************************************
while read line
do
echo $line
echo
cname=$(echo $line | cut -d, -f2) # Extract Centre Short name
IFS="," read X csname centnum < <(grep "^${cname}," centers.txt) # "read the Center Name and ShortName from External file eg $centnum,$cname,$csname
athnum=$(echo $line | cut -d, -f4) # Extract Athlete Number
firstname=$(echo $line | cut -d, -f5) # Extract Athlete First Name
lastname=$(echo $line | cut -d, -f6) # Extract Athlete Last Name
gender=$(echo $line | cut -d, -f7) # Extract Athlete Gender
age=$(echo $line | cut -d, -f8) # Extract Athlete Age
echo Name = $firstname
echo Surname = $lastname
#extract event numbers from line
while read A cname B athnum firstname lastname gender age REST
do for event in $REST
do [ $event ] && echo D';'$lastname';'$firstname';;'$gender';;'$csname';'$cname';'$age';;'$event';;M;1;'$athnum';;;;'
done
done
done < "$file" # specify the import file to work on
# echo D';'$lastname';'$firstname';;'$gender';;'$csname';'$cname';'$age';;'$event';;M;1;'$athnum';;;;' >> $outfile
My output file generated by the script needs to be ( for this input file)
Hi,
I need to compare 2 text files with around 60000 rows and 1 column. I need to compare these and write the mismatch data to 3rd file.
File1 - file2 = file3
wc -l file1.txt
58112
wc -l file2.txt
55260
head -5 file1.txt
101214200123
101214700300
101250030067
101214100500... (10 Replies)
Hi friend i have input as following XML file
<?xml version="1.0"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.02">
<BkToCstmrDbtCdtNtfctn>
<GrpHdr><MsgId>LBP-RDJ-TE000000-130042430010001001</MsgId><CreDtTm>2013-01-04T03:21:30</CreDtTm></GrpHdr>... (3 Replies)
hi..i would ask about how to write over data to new file with BASH.
so..assume my data looks like this :
11
12
13
14
15
...and so on. It's always line by line. and that's for the first file.
i want to write over those numbers into second file but by using space. so my second file should be... (5 Replies)
Hi all
I am here for help once again
I have two files
One file is like this with one columns
F2
B2
CAD
KGM
HTC
CSP
Second file is like this in 5 columns where firs column contain sometime entries of first file with space and other entries (12 Replies)
Hi all,
I have 2 files:
second file
I want if entries in one file will match in other file. It shuld wite approve before it
so output shuld be (1 Reply)
Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted!
1. The problem statement, all variables and given/known data:
I must write a shell script that calls two external Perl functions--one of which sorts the data in a file, and... (6 Replies)
Hi Forum.
I was trying to search the following scenario on the forum but was not able to.
Let's say that I have a very large file that has some bad data in it (for ex: 0.0015 in the 12th column) and I would like to find the line number and remove that particular line.
What's the easiest... (3 Replies)
Hi,
I am working in device drivers. I am new to device drivers. i have invoked chardev.c.
the driver is insmoded. now i want to write something into this and i want to look what i have written. but i don't know how to write and see. please help me (0 Replies)
Hi All,
I am having an XML tag like:
<detail sim_ser_no_1="898407109001000090"
imsi_1="452070001000090">
<security>ADM1=????</security>
<security>PIN1=????</security>
<security>PIN2=????</security>
... (2 Replies)
