Hi All,
I am not too familiar with Korne script so I need as much help as possible on this 
What I want to do is: from the excel file on my computer, I want to take certain field from this xls file and put it in txt or cvs format in specific width column. Then fpt this file into the server. Is there anyway I can achieve this?
Thanks
CT
The best way I would do this is to save .xls as .csv file and then delete watever is not needed and then ftp this.
I feel you need to provide more info on this which will make others to help you better.
or you can do one thing ...
get the txt file from excel file ...
ftp the txt file to unix ... do data processing wahtever you want to do.
Ftp back that txt file to windows with comma delimited.
You can open that txt file in Excel with , as delimiter.
Try it ...
I want only certain field from that xls file, and the output file in txt or cvs has to be in certain width colume like ID is from column width 1-20, name from column width 21-40 ....up to 300+ character in one line.
I got a table set up in MS Access with those column width defined, then copy and paste the data that i want from the excel file into that table. After that, i export the table to txt or cvs format with "fix width" option.
Then i have to fpt this file into the server. This works but takes too much time. So i was just wonder if there is a better way.
Thanks
CT
after having the txt file with required fields from original excel file
use awk tool to format the require way you want.
Following is one of the examples
awk '{printf("%-30s%-20s%-15s\n", $1,$2,$3)}' file1
I understand this is what you want.
bhargav,
You mind to elaborate on the code? I am not familiar with awk so I am not quite sure what are those number are, and "file1" is the name of the file you open or the file you put the output to?
Thanks
CT
file1 is input file.
You can redirect the output by > operator
command > file2 will write the output to file called 'file2'
Using printf of awk , i'm trying to format your input name,id ... etc
with spaces.
man awk for more information.
See the following example to get the idea what i have done.
$ cat file1
1234 abcd 200
123478 abcd 200889
1234 abcd 200
1234 abcd 200
1234 abcd 200
1234 abcd 200
1234 abcd 200
$ awk '{printf("%-30s%-20s%-15s\n", $1,$2,$3)}' file1
1234 abcd 200
123478 abcd 200889
1234 abcd 200
1234 abcd 200
1234 abcd 200
1234 abcd 200
1234 abcd 200
bhargav,
So those number 30, 20, 15 are the width of the field that you want it to be? like id has 30 characters, name has 20 character..and so on?
what if i want to have other things that are not on the original file in the new file also? Example:
file1, original file, have 4 colume : id, first name, last name, amount
i want the file 2 to be in the format:
--------------------------------------------------------------------------
V2
Uid lastname, firstname date school account amount00
--------------------------------------------------------------------------
Here is the actual output that i export form the MS Access (it is all in one line, with V2 on the very first line. id= 000702687, lastname=Adams, firstname=Abigail, amount= 200 (last one on the line, with extra 2 zero's)
V2
U000702687 Adams, Abigail 01/01/10 School Name Inc. PA01001000100100PA01011010101101AD010010020000
(it is at certain column width, but it won't show that in here when i paste it in)
[Edit --- it will if you use code tags. I have added them. -- Perderabo]
Thanks
CT
Hello again 
I test some awk code, i think i get the idea how to manipulate the date, however, I can't quite get it the way i want
Here is the txt file that i have:
$ cat test.txt
000702687 Abigail Adams 200
000724961 Andrew Thompson 400
000710505 Anthony Esteves 20
And the code and output for the code is below:
$ awk '{printf("U%-20s %-0s,%-10s AD100100%-0s00\n", $1, $3, $2, $4)}' test.txt
U000702687 Adams,Abigail AD10010020000
U000724961 Thompson,Andrew AD10010040000
U000710505 Esteves,Anthony AD1001002000
How do i get the colume where "AD...." start to align? the last name doesn't have fix width so it's hard to specify how many character do i need.
Thanks
CT
awk '{printf("U%-20s %-0s,%-10s %-10s %-0s00\n", $1, $3, $2, "AD100100", $4)}' test.txt
vgersh99,
it still doesn't work
CT
sorry.
awk '{_i=$3 "," $2; printf("U%-20s %-20s %-10s %-0s00\n", $1, _i, "AD100100", $4)}' test.txt
vgersh99,
Is there a way i can specify the "lastname, firstname" colume width? let's say 30 for the combination of the name.
Thanks
CT
nawk -f cam.awk test.txt
default width=20
nawk -v w=30 -f cam.awk test.txt
width=30
nawk -v w=40 -f cam.awk test.txt
width=40
BEGIN {
if (w == "")
w=20
}
{_i=$3 "," $2; printf("U%-20s %-*s %-10s %-0s00\n", $1, w, _i, "AD100100", $4)}
I got it works...thanks all;)
However, I still have a question. If i want to add a line with "V2" on the very first line of those line, how can i do that?
Here is my actual code and output
$ cat tu.txt
000702687 Abigail Adams 200
000724961 Andrew Thompson 400
000710505 Anthony Esteves 20
$ awk '{_i=$3 "," $2; printf("U%-19s%-39s%-130s%-178s%-41s%-0s00\n", $1, _i, "01/01/10", "SCAD Group Inc.", "PA01001000100100PA01011010101101AD0100100", $4)}' tu.txt>tu1.txt
U000702687 Adams,Abigail 01/01/10 SCAD Group Inc. PA01001000100100PA01011010101101AD010010020000
U000724961 Thompson,Andrew 01/01/10 SCAD Group Inc. PA01001000100100PA01011010101101AD010010040000
U000710505 Esteves,Anthony 01/01/10 SCAD Group Inc. PA01001000100100PA01011010101101AD01001002000
What i want is :
V2
U000702687 Adams,Abigail 01/01/10 SCAD Group Inc. PA01001000100100PA01011010101101AD010010020000
U000724961 Thompson,Andrew 01/01/10 SCAD Group Inc. PA01001000100100PA01011010101101AD010010040000
U000710505 Esteves,Anthony 01/01/10 SCAD Group Inc. PA01001000100100PA01011010101101AD01001002000
Thanks
CT
awk 'FNR==1{print "V2"} {_i=$3 "," $2; printf("U%-19s%-39s%-130s%-178s%-41s%-0s00\n", $1, _i, "01/01/10", "SCAD Group Inc.", "PA01001000100100PA01011010101101AD0100100", $4)}' tu.txt>tu1.txt
vgersh00,
Thanks...that's work
Just wondering....how do put it in cron so it will automate to run everyday at certain time? Do i need to save it as a program (instead of type it in command line), and excecute it as the program from cron?
CT
I would.
Don't forget to make the script executable.
Don't forget to remove at 'relative' pathnames with FULL pathnames
I tried to put it in a file and run the command:
#! /bin/awk -f
awk 'FNR==1{print "V2"} {_i=$3 "," $2; printf("U%-19s%-39s%-130s%-178s%-41s%-0s00\n", $1, _i, "01/01/10", "SCAD Group Inc.", "PA01001000100100PA01011010101101AD0100100", $4)}' tu.txt>tu1.txt
Here is the output:
$ awk -f prog.txt
syntax error The source line is 4.
The error context is
awk >>> ' <<<
I also try:
awk -f prog.txt tu.txt>tu1.txt
It still gives me the same error message
cT
prog.awk:
#!/bin/nawk -f
FNR==1{print "V2"} {_i=$3 "," $2; printf("U%-19s%-39s%-130s%-178s%-41s%-0s00\n", $1, _i, "01/01/10", "SCAD Group Inc.", "PA01001000100100PA01011010101101AD0100100", $4)
chmod 755 prog.awk
prog.awk tu.txt > tu1.txt