cat file in unix

data no align after i do cat in unix this is my script
cat data*.txt|grep -v ^UNIT_CDE|awk '{print $1,",",$2,",",$3,","$4,",",$5,",",$6,",",$7,",",$8,",",$9,","$10,",",$11,",",$12,",",$13,",",$14,",",$15,","$16,",",$17,",",$18,",",$19,",",$20}' >>  mergetstneg.txt
catfileimg.jpg
chalie001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Likely some sort of carriage control character(s) like TAB(s) embedded in your data, which may be stripped out using awk.

As I recall, awk/gawk/sed will collapse white space, which will include \t (TAB).

Attach your actual data file. Likely someone can tell you exactly what's happening.
nociSoftware EngineerCommented:
What exactly is not aligned, or the actual problem.
arnoldCommented:
Echo David's and noci's comment, white space is the basis on which awk is splitting the line


Since your results are comma separated values, allignment is unimportant until the data is imported and then rendered, displayed....

The data source seems like a query against MySQL, the formatting of which in the response is ..

You could use perl with printf where you can use %5s as an example where by the column to which this rule applies will always use 5characters, using + or - to deal with left or right justify.........
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

chalie001Author Commented:
hi this is the data file
datapta1--1-.txt
datapta1--2-.txt
chalie001Author Commented:
the data are in wrong column
chalie001Author Commented:
also this data AFB LT-91AM-IV000090  is geting separated by comma as AFB ,LT-91AM-IV000090  it must be one record
nociSoftware EngineerCommented:
Looks like mysql/mariadb... so what is wrong with:

SELECT * 
FROM whatevertable
WHERE foo = 'bar'
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Open in new window

chalie001Author Commented:
Am in oracle db running sqlplus in solaris
arnoldCommented:
What is it you would like to see?
You are querying data
cat data*.txt|grep -v ^UNIT_CDE|awk '{printf "$+10s %-9s$+10s %-9s$+10s %-9s$+10s %-9s$+10s %-9s$+10s %-9s$+10s %-9s$+10s %-9s$+10s %-9s$+10s %-9s\n",$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20}' >>  mergetstneg.txt

Open in new window


use printf instead of print and specify the formating as well as what the data types are %s string %d decimal/integer %5.2f float (printf from C/C++)
nociSoftware EngineerCommented:
For oracle please check this article..., iam less familiar with oracle RDBMS

https://chartio.com/resources/tutorials/how-to-write-to-a-csv-file-using-oracle-sql-plus/
chalie001Author Commented:
hi this is what am geting
output
this is what am geting in excell
sample2i what this
arnoldCommented:
When loading csv data in excel, you have to format the data in excel as you see fit, you seem to imply that when loading a csv data in excel you want the appearance preset which is not possible.

when you want two columns from your db combined into a single column within a csv, your query gas to combine them

....
chalie001Author Commented:
it's not two column it's one column the script make it two
arnoldCommented:
the data in the column includes a space and your awk is not specifying a split which matches on where space.


The other option, is to generate the query to output the data directly into a csv format

Try using -F\t. As an option to awk which will separate the lines on the tab character.....
arnoldCommented:
The other option is account for that by combining the two resulting comment into a single csv marked column.
chalie001Author Commented:
How do I use F/t
chalie001Author Commented:
How do I output query in csv
chalie001Author Commented:
I try this cat  trans*.out | awk 'BEGIN {OFS=","; print "UNIT_CDE",    "LEDG_CLS",  "STK_ACNT_NO",      "TR",            "DUES_IN_NO",    "ISS_UNT",       "IS",             "ISS_VCH_NO",    "SIN",   "COND_CDE",   "UI",          "PRICE",    "QTY_OUT", "FULL_SATS","CONFIRM_IN","QTY_RCV","INSTANCE" }
    {print $1","$2","$3","$4","$5","$6","$7","$8","$9","$10","$11","$12","$13","$14","$15","$16","$17","$18}' | awk 'NR==1 {print}; $1 ~ /^[0-9]/ {print}' | grep -v "rows" > demo.csv
arnoldCommented:
The data in trans.out does not have commas, it is tab separated, white space.
Your awk command us not using an explicit field separator (-F) which defaults to white space (tab, space, multiple tabs, multiple sequential spaces)


Try adding -F\t which will split trans.out based on tabs.
nociSoftware EngineerCommented:
@chalie001 :  / != \ ....   / in unix (awk's home) is a path separator, \ is next character has special meaning.   \t means substitute by tab.

but why get into trouble with spaces when oracle CAN produce CSV files if setup correctly see my previous item.
(mysql does is directly for oracle it is a bit more invovlved.).
chalie001Author Commented:
the data in out need to be cat to get comma separated
nociSoftware EngineerCommented:
cat is a tool to concatenate files, it does only copy verbatim.
If you produce the files in a commaseparated file by SQL queries you are more or less done....
More importantly no confusion about space(s) that are used as filler vs. space(s) used as separator, vs. space(s) that are part of content.

Only this would be needed
cat /where/ever/the/files/are/*oracle-comma-separated-spool-files* > /where/ever/it/needs/to/go/comma-seaparated-file

Open in new window

Gerwin Jansen, EE MVETopic Advisor Commented:
>> the data are in wrong column
Why? Your 2 sample data files look OK to me.

Are you still after this? If not can you close the question?
Gerwin Jansen, EE MVETopic Advisor Commented:
Did you try piping the output through the columns command? That command 'pretty prints' your output into columns.

http://man7.org/linux/man-pages/man1/column.1.html
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.