Questions tagged [csv-simple]
Text files containing simple comma separated values. Use this for data that is a subset of real CSV without newlines in (quoted) field content, that can be handled with line oriented tools without the need for a real parser.
105
questions
7
votes
5
answers
877
views
Split a CSV file based on second column value
I am using Ubuntu and I want to split my csv file into two csv files based on the value in the second column (age). The first file for patients under 60 (<60) and the second for patients over 60(&...
- 123
-3
votes
2
answers
165
views
Extracting columns with similar name from two different csv files
I have two different csv files ,one file contain extended columns names while the other contain a shortcut of the same columns names.
For example :
csv file 1 is :
gender,aciclovir drug,aclidinium ...
- 123
2
votes
7
answers
525
views
How to extract columns in which their name containing the word "chronic" from CSV file
I have a big csv file ( around 1000 columns ) and I want to extract to a new file only columns that contain the word "chronic" in their header name. How can I do that ?
For example if I have:...
- 123
-1
votes
1
answer
70
views
Compare different column values together between two files and if match print different column value
File1 has data as below , is the field separator
1,T_EXIT,9053.0,10325.0,, ,
2,V_TURN,120.0,11334.0,,GOAL,RECK
3,Q_ENTRY,122.0,190.0,, ,
4,Q_ENTRY_RUN,130.0,569.0,, ,
File2 has data
SYNC CLK
T_EXIT ...
- 129
-1
votes
1
answer
109
views
Adding an empty column to a CSV file with Miller
I have a CSV file that looks like this:
0
1
2
3
I'd like to use Miller to append an empty column x to every row so that the output file looks like this:
0,x
1,
2,
3,
How do I do that?
- 4,573
-1
votes
2
answers
107
views
Merge duplicate values in columns [closed]
Given a file like this
value,value,value,value
value1,value1,value,value1
value2,value2,value,value2
How can I transform it to look like this:
value,value,value,value
value1,value1, ,value1
value2,...
- 1
-2
votes
5
answers
80
views
Selecting all the records based on filter criteria on 2 fields
ABC,1234.5333,5733.9374,5673.352,352,2.346374,-0.6686874
XYZ,5463.674,93773.683,5734.874,432,-5.683423,-10.38393
AES,7436874.5743,937.6843,8464.5634,564,6.35739,10.6834
PQR,784945.464,57484.8647,57484....
- 75
2
votes
2
answers
1k
views
read line by line and execute cat with grep
Im building a script to load from a file content and then use to cat and grep in another file.
The problem is that is not showing any values while executing the grep. If i do the command manually it ...
- 121
2
votes
2
answers
110
views
Merge muliple files having N common columns by awk and want to replace column value as 0 if any file does not have common key
I want to merge mulpiple files based on common column and want to add 0 when any file does not have that common column. For example see below:
a1.txt
111,222,444,5.5
121,321,555,1.2
a2.txt
111,222,...
2
votes
3
answers
117
views
Generate the MIN and MAX values for keys derived from multiple fields
I have a large flat file which is tab separated and look like this:
APP SRC HITS
App1 SRC1 202
App1 SRC1 101
App1 SRC13 90
App1 SRC2 3169
App1 SRC4 1
App1 SRC2 ...
- 61
1
vote
1
answer
113
views
How to exchange matching patterns in a csv file using another two-column csv table
I want find patterns from csv1 (column1) in csv2 (anywhere) and exchange them with patterns from csv1 (column2).
Like following:
-csv1-
column1
column2
AA
edc
BB
ysc
CC
cds
DD
erg
...etc
-csv2-
...
- 11
0
votes
5
answers
150
views
Rolling up Multiple Rows into a Single Row
How can I rollup multiple rows from a csv file into 1 row ? I have tried working out the query in SQL and it works but I am not sure how can I achieve the same in Linux.
This is how my current file ...
- 93
0
votes
1
answer
95
views
How to get lines which are unique for 1 column but are same for another column?
I am looking for an awk solution which would fetch lines which are unique based on 2nd column when the 1st & 3rd and 4th columns are the same.
constraints :
column 3 and column 4 has to be same ...
- 27
0
votes
7
answers
251
views
How can aggregate lines in CSV based on value of one column
My input is the following sorted CSV file (using : as the field delimiter instead of the usual comma):
version:device
1.0.0:dev1
1.0.0:dev2
1.2.3:dev3
1.3.4:dev4
1.3.4:dev5
I want to aggregate it so ...
- 125
2
votes
3
answers
1k
views
AWK split input CSV into multiple output files based on contents of 1 column
A file data.csv has the following data
1,avocado,mexican green fruit
1,kiwi,green fruit
1,banana,yellow fruit
1,mango,yellow fruit
To organize data into fruit catagories, I've done
awk -F ',' '{print ...
- 3,900
0
votes
2
answers
68
views
Reformatting a list of elements using bash/sed
I have a CSV file with a list like this:
URL,Domain,anchor
https://example1.com,Example1,Category1
I need to reformat this into HTML, like this:
<li><a href="https://example1.com" ...
-1
votes
2
answers
290
views
awk/sed filter example for column manipulation based on data in other columns
I am trying to find an example of how I may effeciently use sed or awk to manipulate csv data in a specific column (col 1 for instance) if the data in col 2 AND col 5 are not equal to "null"....
- 11
0
votes
2
answers
190
views
extract file to generate and rename differenf files
I have a csv (in which firt and second column are separeted by ",") file like this:
Column1,Column2
4e,info1
4t,info2
45t,info3
3,info4
I would like to obtained 4 different file, one for ...
- 161
0
votes
5
answers
341
views
Bash: title-case a csv field
I have this input file on a CentOS system:
1,,,,ivan petrov,,67,
2,2,,,Vasia pupkin,director,8,
3,,,,john Lenon,,,
The task is to change it to:
1,,,,Ivan Petrov,,67,
2,2,,,Vasia Pupkin,director,8,
3,,...
- 11
0
votes
4
answers
82
views
format a CSV file properly so as to fetch data from the CSV correctly
I have a CSV file as below :
I want to remove the "INITIAL OFFER" block from this file and remain with only "FINAL OFFER" block
I also want to remove comma(,) from the first field ...
2
votes
3
answers
704
views
Adding and subtracting multiple columns into new column
I have copied a partial csv file.
publish_date,headline_text,likes_count,comments_count,shares_count,love_count,wow_count,haha_count,sad_count,thankful_count,angry_count
20030219,aba decides against ...
- 35
0
votes
3
answers
176
views
print the number of column and "ERROR" if it has an empty field else "CORRECT" if it has no empty fields
I have a csv that looks as follows:
column1, column2, column3, column4, column5,
1,,,,5,
1,2,3,,,
1,2,3,4,5
1,2,3,4,5,
1,2,,,5,
Using awk I want to print the number of all the rows and a comment &...
- 11
0
votes
1
answer
268
views
awk: add two columns to csv that are the multiplication of a constant by the last column
I am trying to add two new columns to a csv file, that contain the multiplication of the last column to a constant. This last column is formed by the pound symbol and followed by a number.
Input:
id,...
- 145
4
votes
3
answers
659
views
How to do calculation for each row
I had a csv file of data such like that when read into shell:
name,income,reward,payment
Jackson,10000,2000,1000
Paul,2500,700,200
Louis,5000,100,1800
and I want to find the net earning for each ...
- 43
0
votes
1
answer
661
views
How to convert a CSV with multiple sub-row items (from a column) to new columns in Awk?
I have a CSV file with the general format shown in the picture below.
In that CSV there are multiple rows that belong to a certain column (desc) and I would like to to extract those items and add them ...
- 1,568
0
votes
3
answers
4k
views
Compare two columns of a csv file and show unmatched records only
I have a csv file in below format
1311,0008,a131,a131,7039
1311,0008,a131,a132,7039
1711,2046,a626565,a626566,7099
1711,2048,a626565,a626565,7035
What I want is to compare column 3 and 4 only and if ...
- 93
4
votes
3
answers
307
views
How to add column when the number of columns in file is 2
I am trying to write awk for a file I have. Example of the dataset is
S,CV0110,1235
S,1234
D,CQ120,3245
P,7894
Desired outcome is as follows (added empty field when the number of fields in a row is 2)...
- 69
1
vote
2
answers
470
views
Using loop in csv file create separate file for each line
I have a csv file containing
a,b,c
d,e,f
g,h,i
now using this I want to create 3 separate files a.txt, d.txt, g.txt (named after the first field) for each record of that csv.
Each file will ...
- 13
1
vote
4
answers
469
views
XML Data to CSV
Need some quick help as I am not able to get the desired output:
2020-05-19 19:03:07,135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND><TYPE>...
- 17
0
votes
1
answer
26
views
How to use a like function to get the data having comma
We have a data like below
Column1,|Column2,|Col|Abc|Column3,&TableName --- ( Here my delimiter is "&" )
Is there any way i can make an output as below
Column1,Column2,Column3,&...
- 294
1
vote
2
answers
108
views
I want to only remove grouped commas from this csv to change the number of variables to two
I have a csv where the first few rows look like this
c("4288", "57534"),MIB1
c("2272", "2385"),FHIT
c("5550", "10531", "56239"),PREP
c("25809", "23669"),TTLL1
I want to manipulate the number of ...
- 23
0
votes
1
answer
1k
views
SED not removing last double quote from every line
I have a CSV that was exported with double quotes around every header and value and I need them gone but without removing double quotes that might actually exist as values. For example:
"HEADER1","...
- 153
0
votes
3
answers
2k
views
How to extract lines when two columns strings are not equal
I have a CSV file that looks like this format:
text1,text2,string1,string2
text3,text3,string3,string2
text4,text5,string1,string2
text6,text6,string6,string7
I want to extract rows when column1 and ...
- 801
2
votes
5
answers
4k
views
awk: split column, print left side of delimiter and paste in the middle of csv
Input file:
AAA, BBB:XXX, CCC, DDD, EEE, FFF, GGG, HHH
Output should be:
AAA, BBB, BBB:XXX, CCC, DDD, EEE, FFF, GGG, HHH
I thought about something like:
awk -F, '{n=split($2,a,":"); a[n]} {$2=$NF"...
- 157
0
votes
2
answers
2k
views
Append to second column
There is a csv file
ip,time,name
1.1.1.1,2018-08-31 10:01:03,postgres
2.1.1.1,2018-08-31 12:51:13,oracle
3.1.1.1,2018-08-31 14:32:53,sql
I need output as below
ip,time,name
1.1.1.1,2018-08-31 10:...
- 43
0
votes
3
answers
958
views
Replace multiple variable values in a file with given input values (from a file)
If i have two input file with contents like below: How i can replace network_name (eg:P1MSVmgmtvM replace with /rest/ethernet-networks/2bf8bc44-5b31-4e6c-99ed-d68b1e9acf9e).
File1: (input file)
...
- 57
0
votes
1
answer
50
views
print each Network set in separate same group
Currently my csv input looks like below:
network_name,network_set
P1MSVmgmtvM,Data_NetworkSet_A
P1MSVvMotion,Data_NetworkSet_A
P2MSVmgmtvM,Data_NetworkSet_B
P2MSVvMotion,Data_NetworkSet_B
...
- 57
1
vote
3
answers
670
views
remove duplicated pattern/entries within each field in CSV file
How do I remove duplicated entries within each separate fields with below sample as data.
0x,9.4,,,#0,#UNIX#unix,#cli#L#فا#0#فا#0#L#SE#Cli#SE,#فارسی#فارسی#۱#1#١#1,bsh,#V & v
expected output(...
- 40.7k
0
votes
3
answers
2k
views
Using AWK To Extract Numbers From .CSV File
I have a .CSV file that I need to extract numbers from. Example of file:
#File of numbers
1,2,3,4,5
6,7,8,9,10
I want to use awk (or another utility) to extract the first number and second number ...
- 1
9
votes
4
answers
9k
views
How to insert CSV data into an SQLite table via a shell pipe?
I have written a program that outputs the result to the standard output in strict pure CSV form (every line represents a single record and contain the same set of comma-separated fields, fields only ...
- 17.2k
3
votes
1
answer
148
views
Why is column adding a newline in the middle of my row where one is not present in the original data?
I'm analyzing some packetfilter logs and wanted to make a nice table of some output, which normally works fine when I use column -t. I can't use a tab as my output field separator (OFS) in this case ...
- 396
0
votes
2
answers
359
views
Checking if a text file has all entries and is formatted correctly
I have a file that is like this
0001;4;5;30;33;41;52
0002;9;37;39;41;43;49
0003;10;11;29;30;36;47
0004;1;5;6;27;42;59
0005;1;2;6;16;19;46
0006;7;13;19;22;40;47
0007;3;5;20;21;38;56
... etc
The first ...
- 4,404
0
votes
1
answer
2k
views
How can I replace Nth delimiter in a csv file?
I have a comma seperated csv file as a sample like:
1,92345,92345,Dear user, this is your amount , 2016-10-10
2,92345,92345,Dear user, this is your amount , 2016-10-09
I need to replace 4th comma , ...
- 21
6
votes
7
answers
1k
views
How to remove duplicate value in a tab-delimited text file
I have a tab delimited column text like below
A B1 B1 C1
B B2 D2
C C12 C13 C13
D D3 D5 D9
G F2 F2
how could I convert the above table ...
- 119
0
votes
2
answers
604
views
Pivoting Dynamic row data to columns wrt unique values in another column
I'm stuck with a particular requirement which requires the conversion of dynamic row data to column fields.
Suppose I am having a file "output.csv" with the contents like:
TimeStamp,Block,...
- 3
8
votes
1
answer
11k
views
Print each field of CSV on newline without knowing number of fields
I was playing with IFS today and created a quick text file with a list of numbers separated by commas on 1 line.
1,2,3,4,5
I then tried to write a script to print each number on a newline. I was ...
-1
votes
3
answers
2k
views
Add a header field, and the file's name at the end of the data lines, for all the csv files in a folder
I want to add filename (without extension) at the end for all the lines for all the csv files in a folder. All the files are having the same header.
Let's say I have two files a.csv and b.csv in a ...
- 39
2
votes
5
answers
4k
views
Use AWK to split substring by last n characters in to a new column
I have a large .csv file where I need to split a specific column by string length. I'm trying to take the last 6 characters of column 2 and move them into a new column.
Current:
3102017,90131112,0,...
- 147
3
votes
3
answers
6k
views
Count the number of occurrences of a column value in a TSV file with AWK
I have a TSV tab-separated file with 3 cols:
ID\tTEXT\tTYPE
To print the TYPE column I do
cat /dataset.csv | awk -F $'\t' '{print $3}'
Those values are an enumeration of values like {CLASS_A,...
- 287
1
vote
2
answers
298
views
text processing : extract parts of a file and write them into a single line
I have several files formatted like this:
...
<title> Field1 : DATA_FIELD_1</title>
...
<i class="blablabla"></i> <b>Field2 : </b> <span>DATA_FIELD_2</...
- 23