Questions tagged [csv]
Files containing data arrange in a table, often with commas (hence Comma Separated Values), to separate columns. Rows are separated by newlines (but not all newlines are row separators as fields can be quoted to contain the separator newlines. Use this tag for full-fledged CSV data not the simpler case of one record per line or completely unquoted (use csv-simple for that kind of data).
834
questions
1
vote
1
answer
14
views
Filter rows of a huge csv file based on values in 2 columns
I have a .csv files as shown below:
RepDate;FOO1;ProcessId;ClusterName;ProcessType
19700101;-1;Job_1000969943637142044;PROD;auswertung.mpeTestPlayer.xml
20181231;root;Job_107953405843118835;PROD;...
- 125
0
votes
2
answers
65
views
Copy specific Column from multiple csv file and write it to new csv file. Shell Scripting
I have multiple CSV files from these I want to extract 3rd column and write it new file, in the same manner, the file is sequenced in a folder. Append should be in such a manner that all the columns ...
- 19
1
vote
3
answers
48
views
Encapsulate only comma-separated strings in quotes
I have some data that is currently in TSV format, but needs to be converted into CSV format. The only problem is that occasionally in the TSV, there are some values that include commas that are not ...
- 13
0
votes
2
answers
56
views
Remove Records with more than 30 of the same value
I have a large CSV, and want to remove the records that have the same FirstName field ($8), MiddleName field ($9) and LastName field ($10) if there is more than 30 instances of it.
TYPE|10007|44|Not ...
- 1
0
votes
3
answers
41
views
Sum certain values in row, but don't print the sum if one of these fields is empty
I have a file containing 3 columns (comma as delimiter). The first column contains ID, while the second and third contain values that I would like to sum. The sum should be appended as 4th column to ...
- 161
0
votes
3
answers
120
views
How can I remove the second comma in each row of a csv file using sed?
I have a bunch of csv files in a directory, and I want to remove from each row the second comma. Example:
Input:
53,900,1001,-45.4247,9.87187,541.65
75,900,0102,37.0896,4.24087,558.35
75,900,1101,37....
0
votes
4
answers
46
views
Combining columns from two files and seperating them into indivdual output files
I have two large csv files that share the same first column and first row (header) as follow: etc means more values with same pattern.
file1.csv
names,text1,text2,text3,etc
A,1,4,3 ...
B,5,2,8 ...
C,3,...
- 13
1
vote
3
answers
73
views
Compute Differences of Two Columns
I have a CSV file filename.csv with the following contents.
filename.csv:
"Afghanistan","94.0","81.1"
"Bahamas","42.9","43.2"
"Bolivia (...
- 11
0
votes
2
answers
107
views
Need to add second enclosure string not present in a csv file column
Second double quotes is missing in csv column. Looking for any ideas/ shell script for the below
Find a specific column which is missing a ending double quotes.
Add the missing double quotes at the ...
- 11
0
votes
1
answer
36
views
Using jq, etract fields and subfields from list of objects, grouping paired subfields for saving to csv
With this data:
[
{
"c": "A",
"e": "B",
"score": 0.99,
"v": [
{
"context": "asdf",
...
- 1
2
votes
1
answer
132
views
posix bash, how to read a csv file and ignore some columns?
on a POSIX shell, no Python and no awk available (so don't bother telling me I should use a "real" programming language) I have to loop through a csv file.
https://datacadamia.com/lang/bash/...
- 123
-1
votes
3
answers
53
views
Trim pathname in CSV file
I am struggling over a filter where I am trying to trim data in a specific column of a CSV after the 3rd or nth occurrence of the character \.
My data looks something like this:
data,data,c:\path1\...
- 11
1
vote
1
answer
146
views
reformat json using jq
I have the below JSON file:
{ data : [
{
"name" : "name1"
"date" : [
{
"date1" : "aaa",
"date2" : "bbb"
...
- 13
1
vote
1
answer
47
views
AWK: Add missing lines in csv file and set specific column to "0"
(Note: this question is heavily shortened because the detailed question was blocked with "This looks like spam.")
I'm having a hard time adding missing lines to a series of data in a ...
- 13
2
votes
4
answers
75
views
How to truncate characters in between of a large column in tsv/csv?
I have a csv file:
1,abcde aa aaaa aaa aaaa abcde,4
2,efghi ooo oooo ooo oooo efghi,5
I need it to truncate the second column with initial and last five characters and fill with three dots. How to ...
- 215
0
votes
2
answers
82
views
Manipulating CSVs in Unix - best native Unix tools to learn [closed]
I am new in the Unix environment. I am looking to learn manipulating CSV files (for example: adding new columns, getting values from one CSV by index and appending the value in a new CSV file etc.) in ...
- 23
1
vote
5
answers
69
views
include a cent-sensitive character that does not interfere with the delimiter of a csv file
I have a CSV file that I need to include a separator to highlight the cents in that column that doesn't interfere with the ',' delimiter
Below is the model of how my current file is:
99999999,2021-01-...
1
vote
2
answers
289
views
How can I append new lines in a CSV file and modify them in Unix
I am new to Unix in general and starting to learn shell scripting. I am working with a CSV file with the below sample rows (it's a large CSV file with 4 entries for each item):
Table 1
Item ID Time ...
- 23
-7
votes
1
answer
79
views
how to add "p" in file [closed]
I have test.csv file and below is the content
"A","b","c","d"
I wanted to add "p" between b & c using awk command, could you please assist?
- 1
-1
votes
3
answers
80
views
separating fields not aligned from a pipe delimited file
I have a file which has entries separated by | and tab.
#Name Length Bases Coverage Reads RPKM Frags FPKM
ENST00000423372.3|ENSG00000237683.5|-|-|AL627309.1-201|AL627309.1|2661|UTR5:1-...
- 59
2
votes
3
answers
370
views
Removing Duplicates from a CSV based on specified columns
I am working with a CSV data set which looks like the below:
year,manufacturer,brand,series,variation,card_number,card_title,sport,team
2015,Leaf,Trinity,Printing Plates,Magenta,TS-JH2,John Amoth,...
- 123
0
votes
2
answers
261
views
Replace content of a specific column in a CSV with sed
I have several CSV log files and want to replace the status codes stored in the 8th column of these log files with a description
The log files looks like this:
ip,date,time,zone,cik,accession,...
0
votes
1
answer
68
views
if col1 matches with col4/col5 print all values from col4/col5 respectively in empty rows in col1
I have a comma delimited text file on a Linux machine. First I want to match col1 with col4 and col5, if col1 matches with col4 then print all values from col4 in all rows where col1 is empty and if ...
- 91
2
votes
3
answers
103
views
commands to remove row from file if reverse row is in file
I lets say I have a csv that looks like:
1,20
1,13
20,1
I need to remove 20,1 because 1,20 is already in the csv. My idea to do this is to go row by row and check if the reverse is already in the csv,...
- 29
1
vote
2
answers
52
views
Iterate lines on csv to save columns as variables
I have a csv composed as follows:
Column1,Column2,Column3
A Existing text in Column1, A Date in Column2, A Integer in Column3
B Existing text in Column1, B Date in Column2, B Integer in Column3
C ...
- 31
1
vote
1
answer
188
views
Filter one very large CSV based on values from another CSV
I am processing some CSV files that do not fit in RAM.
The 2 CSV files have the following structure:
first.csv
id
name
timestamp
serial
str
yyyy-mm-dd hh:mm:ss
second.csv
id
name
date
serial
str
...
2
votes
2
answers
104
views
Find lines in one "key - value-list" file that contain data fields not mapped to that key in a second "key-value" file
I have 2 files. One file contains a minimum of two fields (a username, an age, and a variable number of "fruits" each in their own field) per line, the other file always contains two fields ...
- 31
0
votes
1
answer
49
views
Calculate mean and stdev between similar formatted csv files and output to another file following format
I have a group of output csv files from replicates of a simulation. Each file line in the file follow the same format: generation, number, value1, value2, .... valueX. (the file also include a header, ...
- 1
0
votes
1
answer
88
views
Parse comma separated string ignoring commas between quotes
I have a comma separated string that could contain quoted elements with commas. For example:
issuer=C = US, O = "DigiCert, Inc.", CN = DigiCert High Assurance TLS Hybrid ECC SHA256 2020 CA1
...
- 9,316
1
vote
4
answers
79
views
Need to format a CSV
I have a CSV file with the following values sample shown below
"Basic","""21,21""","[""21"",""21""]","&...
- 11
2
votes
3
answers
537
views
Replace a character only between the double-quotes with sed or awk [duplicate]
I have such a scenario inside a CSV file:
ID,PRICE,QUANTITY,ARRIVAL
01299,"41,5",1,0
26528,"412,03",0,0
38080,"2,35",0,0
38081,"2,35",0,0
..
..
The question I ...
1
vote
2
answers
78
views
Deduplication with awk on command line and script
I have a file that has the following format:
487422,Potenza
487386,Forlì-Cesena
487399,Grosseto
487425,Catanzaro
487409,Napoli
487446,Prato
495498,Fermo
487425,Catanzaro
487389,Macerata
487442,Biella
...
- 35
2
votes
2
answers
104
views
Comparing with csv file value
I have a csv file and I need to compare the value in 4th column of 2nd row with a string.
Sample csv file:
Col1,Col2,Col3,Col4
Val1,Val2,Val3,ValNeeded
I'm using the below code to perform this,
...
- 23
-1
votes
3
answers
298
views
Replace a column value in csv file from another file
I have a csv file which has 85 fields. I want to replace column number 52nd values with the data from another file. This second file contains only 1 column and has same number of records as it is in ...
- 324
0
votes
2
answers
71
views
Convert to csv file with same name using AWK command
I'm using this command to process text files and my goal is to save my output in CSV format.
awk -f test.awk /FILES/detail-1019:01 > detail-1019_01.csv
But problem is, I have many files and I don'...
- 15
0
votes
0
answers
71
views
Copying windows csv file data to Unix server file
I have this Unix server at work where resides a script which picks up a file on that same server and process each row. I believe we receive those files from data providers, which SFTP the files to us. ...
2
votes
3
answers
934
views
Looping through columns
I have a csv file that looks something like this:
first_name,surname,email,
first_name2,surname2,email2,
....3,..
....4,...
The number of rows in each column are the same.
I want to run a command ...
- 21
0
votes
5
answers
147
views
Shift of dates in a CSV file via bash script
Currently I have a file with records like this :
D20211011,S0519,306668,1
D20211004,S1600,306668,1
D20211009,S1604,306668,1
D20211010,S1605,306668,1
D20211006,S1610,306668,1
D20211011,S1611,306668,1
...
- 91
0
votes
2
answers
418
views
How do I loop through all csv files in a directory, select a range of columns and combine to single csv?
I have a large number of CSV files in a specific directory. All have at least 41 columns with matching headers, but can be as wide as 200 columns. I need to grab just the first 40 columns and append ...
1
vote
3
answers
588
views
Generate templates in yaml from a CSV file
I am trying to create yaml files from a template using my variables.
My yaml template look like this
number: {{NUMBER}}
name: {{NAME}}
region: {{REGION}}
storenum: {{STORENUM}}
clients: {{...
- 9
-3
votes
1
answer
157
views
Remove CR+LF from a cell in CSV file [closed]
The data in column b has a CR+LF that shouldn't be there. It looks likes this:
a,b,c
test1,test2
test2 bis,
test3
But it should look like this:
a,b,c
test1,test2 test2 bis,test3
How can I remove ...
- 75
1
vote
2
answers
47
views
Assign groups based on a list of paired values between all individual group entries
I have a tab-separated file pairs.tsv with two columns that display paired entries. Based on the two values in the two columns, I want to create a third column that assigns groups to the entries.
In ...
- 11
1
vote
5
answers
2k
views
Removing new line character from a column in a CSV file
We are getting new line character in one the column of a CSV file. The data for the column is coming in consecutive rows.
Eg:
ID,CODE,MESSAGE,DATE,TYPE,OPER,CO_ID
12202,INT_SYS_OCS_EX_INT-0000,"&...
- 19
2
votes
3
answers
494
views
csv to tsv with nested quoting
I need to convert CSV to TSV in bash. I found this solution and it works well, but not for all datasets as I will show below.
For example, for a.txt:
a,"test, part2 ""the start"&...
- 1,135
0
votes
2
answers
201
views
Organizing a CSV file with missing columns
Problem
I have a .csv file that was not filled in properly, which has missing column information.
The file looks something like this (whitespace is for clarity):
C1, C2, C3, C4,
R1C1,R1C2,R1C3,...
- 19
-1
votes
2
answers
59
views
Extract information from csv to multiple files
I need some help to extract and to separate strings of information from a CSV file, and subsequently printing it multiple new files.
I have huge files with a ton of information I need separated. The ...
- 3
1
vote
1
answer
160
views
How may I convert this JSON into CSV?
I have below JSON document to convert into CSV. I need to iterate over the first column with the 2nd and 3rd (they can be either string or array) at the same time as one iteration.
Example document:
[
...
- 99
8
votes
2
answers
3k
views
how to jq nested array without key (only value) and convert to csv
I hope someone can guide me how do I convert below json to csv that I'm expecting for.
Much appreciated in advance.
Update: thanks for the solutions provided, but I found that sometimes array does not ...
- 99
-3
votes
1
answer
53
views
Splitting a line in CSV to two columns
I have a CSV where each line is like below
**./ABC/dce/app/config/fgh/pqr/app.properties** **app.pass**
I want to use space as the seperator and get two seperate columns in the CSV like below
...
- 11
0
votes
2
answers
274
views
How to extract columns by names from CSV file
I have over 150 CSV files with inconsistent columns on MAC (Unix) e.g:
Sheet A
ID
Col
mobile
email
secondary mobile
Name
v
v
v
v
v
v
v
v
v
v
v
v
Sheet B
ID
Col
Col
Col
Col
Col
v
v
v
v
v
v
v
v
...
- 1