I have input file , as shown above and I want to convert it into output, as shown above.
kindly let me know how to use sed command inside awk statement so that changes can be made.
the output is text file and output is to be aligned or formatted as table.
As this input file is received from MySQL query, and when it is converted to .csv file some columns file format gets changed ( column 2 and 3 are treated as general and is shown in decimal but they are numbers). Hence is it possible to set column format in unix for .csv file , so that I will not need it to save as .txt and can directly saved as .csv with ","separated file
I have tried to do it in sql server 2012 but the column length is flexible and I have also tried to get date format as desired from 100 to 114
now I am doing this
convert(varchar(19),call_start_time,100)
so that I can directly run awk command to remove spaces from my file.
but its not working.
i have also searched on net regarding this sed inside awk command and i came to know about gsub function but i am stuck in between.
Hence kindly help me, how to figure this out
So you want to do an SQL query followed by an awk script followed by a sed script. Hmmm - given the enormous formatting capabilities of usual SQL implementations, this doesn't seem too efficient.
MySQL provides the DATE_FORMAT(date, format_mask) function which would allow an immediately correct output. And the COLSEP (if I remember correctly) gives you control over the column separator character (e.g. ; ).
I'm not an SQL expert, but still believe this could all be done in SQL without needing awk or sed . And, you showed us the output you said you want with <space> as a field separator and then say that you want the output to be a CSV file with <comma> as the field separator??? Assuming that your input is in a file named file and that you do want output with <comma> as the field separator, the following awk script seems to do what I think you want:
awk -F'|' ' # Invoke awk and set input field separator to "|".
{ gsub(/^ | $/, "", $2) # Get rid of leading and trailing space in date
# and convert input FS (|) to output FS (space).
gsub(/ /, "-", $2) # Convert spaces to hyphens in date.
gsub(/ +/, ",") # Convert strings of adjacent spaces to commas.
gsub(/^,|,$/, "") # Get rid of leading and trailing commas.
print # Print updated lines.
}' file # Close awk script and specify input file.
If file contains the input you showed us in post #1 in this thread, this produces the output:
You haven't told us what operating system or shell you're using (which you should always do in threads in this forum so we can suggest solutions that will work in your environment). If you're using a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk .
I have already tried DATE_FORMAT(date, format_mask) function but it gives error
'DATE_FORMAT' is not a recognized built-in function name.
Details of database are as below.
Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Analysis Services Client Tools 11.0.2100.60
Microsoft Data Access Components (MDAC) 6.3.9600.16384
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.9600.17690
Microsoft .NET Framework 4.0.30319.34014
Operating System 6.3.9600
The sample input given, is output of query and I want to format it as given above.
Is it possible to use sed within awk statement or please let me how to do it with gsub function to get desired output.
There is almost never a need to invoke sed from awk (or even to use sed to filter output produced by awk . The awk editing functions can do most of the stuff sed can do and in this case, I find it easier to do it in awk than in sed . Your post #7 seems to ignore the suggestions provided in post #6. Please look at post #6 and let us know whether or not it works for you.
As with any system-provided utility, the manual pages supplied with your system are always a good place to look to figure out how that utility works. You can start with:
man man
to see how the man command works, and environment variables and options you can set to control what pager is used to view the output it provides. Then move on to:
man awk
to see how awk works on your system.
If the man page whets your appetite and you want to dig a lot deeper, there are hundreds (if not thousands) of examples of awk scripts on this forum and you could try the books:
The AWK Programming Language by Aho, Alfred V., Kernighan, Brian W., Weinberger, Peter J. You might note that the initials of the three authors Aho, Weinberger, and Kernighan form the name of this utility; this is no accident--these three authors designed and wrote the utility as well as the book that describes it. Note that this book was written in 1988 which was before some of the current features provided by internationalized regular expressions and some extensions provided by later language maintainers were around, but it still provides a very good overview of how the language works with examples.
sed & awk by Dougherty, Dale and Robbins, Arnold. I haven't read this book myself, but others have given it good reviews.