cat file in unix

chalie001
chalie001 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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 Engineer
Distinguished Expert 2018

Commented:
What exactly is not aligned, or the actual problem.
Distinguished Expert 2017

Commented:
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.........
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
hi this is the data file
datapta1--1-.txt
datapta1--2-.txt

Author

Commented:
the data are in wrong column

Author

Commented:
also this data AFB LT-91AM-IV000090  is geting separated by comma as AFB ,LT-91AM-IV000090  it must be one record
nociSoftware Engineer
Distinguished Expert 2018

Commented:
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

Author

Commented:
Am in oracle db running sqlplus in solaris
Distinguished Expert 2017

Commented:
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 Engineer
Distinguished Expert 2018

Commented:
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/

Author

Commented:
hi this is what am geting
output
this is what am geting in excell
sample2i what this
Distinguished Expert 2017

Commented:
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

....

Author

Commented:
it's not two column it's one column the script make it two
Distinguished Expert 2017

Commented:
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.....
Distinguished Expert 2017

Commented:
The other option is account for that by combining the two resulting comment into a single csv marked column.

Author

Commented:
How do I use F/t

Author

Commented:
How do I output query in csv

Author

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
Distinguished Expert 2017

Commented:
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 Engineer
Distinguished Expert 2018

Commented:
@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.).

Author

Commented:
the data in out need to be cat to get comma separated
nociSoftware Engineer
Distinguished Expert 2018

Commented:
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
Most Valuable Expert 2016

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
Most Valuable Expert 2016

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial