Converting a list to X columns of csv (& wrapping a command around it)
Hi All,
Random question, how would you convert a data file from a list like so:
to comma separated X columns across:
Why would you want to do this? The background to this is a business app which loads certain data into memory based on keys supplied via a cli utility. The cli utility's syntax looks something like:
I need to share a simple way with others to handle this task, but i'm coming up short. Due to a political concern (boo! hiss!) i can't simply drop a script in the relevant production user's home dir for other staff to use (which would seem the best way to go to my mind).
So i need to find a solution to hand this over. I have the standard unix toolset available and also a wiki for storing a How To page explaining the process (preferably with copy / pasteable commands).
Here's the way i do it interactively at the shell when necessary:
Quote:
1. Open the input data file (sample 1 above) in vim
2. Record a macro qq$a,[DEL][ESC]q - which is recording a macro to register q, going to line end, enter append mode, append a ,, delete the newline bringing the next line up onto this, exit to command mode, finish recording macro
3. Undo the change made when recording that macro u
4. Record another macro qa3@qjq - which is recoring a macro to register a, run macro in register q 3 times (for 3 columns), move cursor to line below, stop recording macro
5. Undo the change made when recording that macro u.
6. :set number
7. <shift>g - go to end of file
8. Quick mental sum, divide the number of lines in the file by 3 (if 3 columns) and add 1 to the result. Go back to the start of the file 1<shift>g. Run the macro in register a that many times, i.e. 11@a
9. Then it's simply a case of wrapping this csv data with the command line: :%s/^/.\/app_loadkeys -axxx -bxxx -exxx -d /
Although that flows perfectly logically at the shell and should be easy to memorise after a few runs through, the description of how to do it reads horribly and is totally confusing if the user is not experienced with vim. It's also a bit long winded for a task they can expect to repeat semi-frequently.
I was thinking a copy / pastable command line would be an improvement for usability, perhaps:
However i think that cmd line looks v daunting for an inexperienced user. What happens if the process changes in future? It's not very maintainable, for example sometimes they will want to submit 4 keys at a time instead of 3, or 10 keys at a time. I could list out on the wiki page a copy / pasteable command for each version and they copy the one for X keys at a time as they need. Like i say, i feel i'm coming up short here.
Wish i could just drop a script in place to handle this, would take 20 mins and be done with for life.
How would you solve this? Any inspiration greatly welcomed!
Hello, I've been trying to use find command to find and print out some files. When I execute the command on the command line I get the output as expected howerver when I run it in the script it doesn't wrap.
For example, this is nicely wraped
find /etc -perm -o=w -exec ll '{}' \;... (4 Replies)
By default, we use ksh (88) as our shell. I prefer bash, so I added this line to my .profile:
exec bash -o viI also added this to my .bashrc?:
#***********************************************
#These are important tweaks specific to BASH:
#***********************************************... (1 Reply)
Hi All,
Please give me the solution to the following ASAP.
1) Converting the .xls into .csv
Script i tried,
mv hello.xls hello.csv
The above given script converting the .xls file into .csv successfully. But after i run the below unix command I am no able to open the .csv file, its giving... (4 Replies)
Hi All,
I have two .csv's
input.csv having values as (7 columns)
ABC,A19907103,ABC DEV YUNG,2.17,1000,2157,07/07/2006
XYZ,H00213850,MM TRUP HILL,38.38,580,23308,31/08/2010
output.csv having (25 columns)
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y... (4 Replies)
Hi,
I have a requirement to convert rows into columns. data looks like:
c1,c2,c3,..
r1,r2,r3,..
p1,p2,p3,..
and so on..
output shud be like this:
c1,r1,p1,..
c2,r2,p2,..
c3,r3,p3,..
Thanks in advance, (12 Replies)
Hi Friends,
I have come across some files where some of the columns don not have data.
Key, Data1,Data2,Data3,Data4,Data5
A,5,6,,10,,
A,3,4,,3,,
B,1,,4,5,,
B,2,,3,4,,
If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns.
I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Hi,
I have a file of csv data, which looks like this:
file1:
1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628
2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
I am joining two CSV files based on 'Server_Name' column, 1st column of first file and 2nd column of second file.
If matches, output 1st and 2nd column from first file, 3rd,4th,5th,6th columns from second file.
I am expecting output CSV file as below.
Could you please send me help me with... (6 Replies)