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).
742
questions
1
vote
2answers
46 views
How can I convert specific sheets in an xlsx file to csv via the linux command line?
I have about 16,000 .xlsx files in a directory. Each file follows the same structure. The first sheet is empty, and called "Sheet 1". The second is where the interesting data is, and it's ...
-1
votes
2answers
48 views
Copy column B if column E or F is empty or comes with value 0
I have a CSV file, and I want to use a bash with command awk or anything else to modify this file.
For example, I want to copy column B if column E or F comes empty or with the value of 0
AAAA, BBBB, ...
1
vote
1answer
63 views
Replace several characters in a text file using specific row and column values of a csv
I have a .txt file with different entries such as region1a, region1b, region2a, region2b etc.
I have an excel file with different column and row values. I want to use a particular row and column value ...
1
vote
1answer
50 views
Merge multiple CSV files from subfolders into a unique file and add file name in a new column
I have a bunch of CSV files in many subfolders (only 1 level of subfolders from the parent directory, nothing is nested further).
Each CSV file has three columns; i, k, v with a mix of string and ...
0
votes
3answers
61 views
Sort multiple columns in csv including date stored as dd/mm/yyyy
I am attempting to sort data stored in a csv.
My data is as below:
name,date,time
DEF,01/02/2021,06:00
HIJ,31/01/2021,07:50
ABC,01/02/2021,05:50
*(date stored as dd/mm/yyyy)
I am trying to Sort by ...
0
votes
0answers
15 views
how to combine CSV files using cat .csv when one of the files has an NA value in cell A1 [duplicate]
I'm using the command line to combine hundreds of CSV files (with identical columns) in the manner described in these questions.
The problem is that some of my CSVs have an NA value in cell A1. In ...
0
votes
0answers
37 views
Redirecting STDIN for rm inside of a while loop
I'm reading in from a csv file containing two fields, a MAC address and a file path. My goal is to run the script on the machine and if a match is found between the MAC address of en0 or en1, use rm ...
1
vote
2answers
69 views
Fill empty cell in pipe-separated-value file with default text [closed]
I have a PSV file with empty cells. I want to fill the empty cells with the valule 00000000000
Example input
0000000001|00346743139|201901|07
0000000002||201901|00
Desired output
0000000001|003467431|...
-3
votes
2answers
43 views
Remove duplicates but keep first instance
Please help me to resolve this issue from a csv file. AWK or Sed.
id1,ipadd1,number1
id1,ipadd2,number2
id1,ipadd3,number3
id2,ipadd1,number1
id2,ipadd2,number2
id2,ipadd2,number3
id3,ipadd2,number2
...
0
votes
0answers
57 views
jq - json to csv - print first string in array only once
I have this result from jq but I only need the string print only once.
jq:
cat file.json | jq -r '[.InstanceId] + (.Entries[] | [.IPV4, .IPV6, .MacAddress, .Name]) | @csv' >> final.csv
id1,...
-1
votes
3answers
36 views
Issues related to grep command looping for multiple csv files
I made a for loop script as shown below,
for file in *.csv
do
grep raxA $file > new_${file}
done &&
mkdir raxA && mv new_* raxA &&
for file in *.csv
do
grep raxB $file > ...
1
vote
1answer
26 views
In Shell Script SSH connection is working only for first line data in CSV file and command failing for remaining servers
My target is to just validate the login is successful for list of servers in CSV file. For SSH Password, Userid and IP address, I am passing these values via CSV.
And want to capture the result of SSH ...
-1
votes
2answers
73 views
AWK set values in variables
I've a csv file with some fields with values and I want to print a combination of this values (in the end it will be a sql script).
I'm trying to use AWK with no results:
Part of my csv file:
ATTR_A,...
0
votes
0answers
48 views
Split command to chunk a large CSV - with new line character
I'm splitting a large CSV file with small chunks using the split command. Each file should contain 1000 rows only.
The command I used is,
cat large.csv | split -a3 -l1000 --filter='gzip > $FILE.gz'...
0
votes
1answer
85 views
I need an efficient method to combine thousands of csv files
I asked the same question on stackoverflow, but did not get a satisfactory answer.
I have a directory tree like this:
bacteria > species(num subdirs = 1300) > kmer(num subsudirs = 9)
The ...
-2
votes
3answers
196 views
Extracting data from CSV file using regular expression [closed]
I have a CSV file like the following:
1004051,abc_eaf@gmail.com,abc,eaf
1004052,edf_eaf@gmail.com,edf,eaf
1004053,abc_eaf@gmail.com,abc,eaf
1004054,ghi_eaf@gmail.com,ghi,eaf
I would like to extract ...
0
votes
1answer
43 views
JQ flat objects to csv
I need to flatten this json array of flat objects
[{
"key1a": "value1",
"key1b": "value2",
...
"key1z": "value50",
},
{
"...
-1
votes
1answer
85 views
Using CSV file with Bash
Looking to use a csv file that uses headers as variables that change at each line but I want to do something simple like run a while read -r or For loop off each line. During the loop I want each ...
0
votes
2answers
50 views
Remove all rows in a CSV file without corresponding/matching timestamp in another CSV file
I have 2 CSV files which look like follows:
file1.csv
col1,col2,col3,col4,date,time,col7
1,2,3,4,2019-07-20,12:40:00,0
1,2,3,4,2019-07-20,12:43:00,0
1,2,3,4,2019-07-20,12:44:00,0
1,2,3,4,2019-07-20,12:...
0
votes
1answer
53 views
Replace strings in a big text file based on fields from a CSV file
I have a big text file (roughly 2GB). I have a csv file that has the following fields:
rowID,pattern,other
1,abc_1z1,90
2,abc_1z2,90
3,abc_1z10,80
4,abc_3p1,77
...
My interest is: replace the content ...
4
votes
4answers
242 views
Grep the data only (-o) and the first column
Sample data
wolf@linux:~$ cat data.csv
A,4.4.4.4,4.4.4.5,4.4.4.6,3.3.3.3,3.3.3.4
B,1.1.1.1,1.1.1.1,1.1.1.2,1.1.1.3,3.3.3.3
C,1.1.1.1,1.1.1.1,1.1.1.1,1.1.1.1,1.1.1.1
D,2.2.2.1,2.2.2.1,2.2.2.2,2.2.2.3,...
0
votes
1answer
21 views
How to split single rows of a CSV file into multiple depending on delimiter in 2nd column
My base.csv file has several entries a sample of which is below.
NAME,IP_ADDRESS,PRI_IP,APP_CODE,APP_NAME,OWNER
test2,10.9.7.12/10.222.101.11,10.9.9.12,545,myapp1,owner1
mytest,10.9.7.12,10.9.9.12,545,...
1
vote
0answers
32 views
Output from Bonnie++ doesn't match up
I just ran bonnie++ ten times to test the performance of a file server. After removing the verbose output this is what remains:
format_version,bonnie_version,name,concurrency,seed,file_size,...
0
votes
2answers
78 views
Unique row values skip the header in csv [closed]
I'm trying to find all the unique values in a column. However, with this command I'll also get the header row. How do I skip that?
awk -vFPAT='([^,]*)|("[^"]+")|","' '{if ($2!~...
-1
votes
1answer
46 views
Pattern matching analysis with CSV file and store the result in an arrays
The codec files to be played to the users are CODEC=welcome1.g711u,Announcementbui4.g711u.
From the log, codec files played to the users are grep and stored in the E41.csv file.
Content of that CVS ...
1
vote
1answer
50 views
add characters at end of each record in a csv file that contains return characters in fields
I have a csv file with 5 columns in it of varying length. The delimiter between columns is "," and the last column ends with ". The row delimiter is a newline. The latest file has ...
0
votes
2answers
18 views
Merging two CSVs based on matching first column (with header)
I have two CSV files. One of which contains a lot of data:
main.csv
Order No.,invoiceNumber,invoiceAmount,invoiceAmountNet
20001,20010,500,5
20002,20011,400,4
20003,20012,300,3
20004,20013,200,2
20005,...
-1
votes
1answer
61 views
grep and re-direct to csv
This is the campaign log that I am parsing. Based on call-id I do grep.
Orginal Progress-log as follows:
FilePlayer::run:: Finished audio file is /voip/VoiceCampaigSetup/wildfly-10.1.0.Final/...
-1
votes
2answers
82 views
grep log file and write it on csv file [closed]
I do grep by codec file played to the user based on their call-id. There are 2 files played to the user/call-id. I am writing those grep output to the .csv file.
I used
grep -oP "(?<=$START).*?...
1
vote
4answers
282 views
Bash script/awk to input and output a CSV file
I'm having some trouble creating a bash/awk/sed script that would take a comma-separated CSV file of three columns (firstname, lastname, date of birth), and outputs another CSV file that has the same ...
5
votes
5answers
475 views
How to sort a first file (csv) based on the second file keys
I am trying to find a solution where I can sort my first file using the first column based on the second file keys
First file example (file1.csv)
COLUMN1 COlUMN2
apple fruit
dog animal
cat animal
cow ...
1
vote
2answers
76 views
How to separate columns when delimiter is included in the fields
I have a CSV file with 24 fields. The record looks something like the sample below. So some fields have ',' in their value. How do I use the delimiter?
I'm trying to find the values greater than 200 ...
1
vote
1answer
61 views
How can i put a variable in an awk command
with my code im trying to sum up the values with the specific name of a column in a csv file, depending on the input of the name.
Here's my code:
#!/bin/bash
updatedata() {
index=0
while IFS=...
0
votes
2answers
53 views
Removing block of text from csv file in Linux
I have a CSV file with lines similar to:
PerformanceData|712899|713|12|5.53|VERIFIED_TRADE N CoverDistribution SecurityTransfer (BondUKTI / //-MO/W////.%)|5708|124.9
In column 6, I have a block ...
1
vote
3answers
55 views
How to use a file of search patterns to search for exact words in the final column of a csv?
This issue is covered as separate topics, but I can't get everything to work together.
I have a tsv file, here is an example line: (file1)
NODE_1_length_45927_cov_22.029055_g0_i0 WP_055195622.1 93.1 ...
2
votes
1answer
17 views
How to introduce a new header to a CSV file based on condition using gawk
I have a csv file from I wish to extract only column number 7 and 11. Depending on the value of column 7 which has either value OK or KO I wish to insert a column next to it called value where the ...
2
votes
0answers
204 views
Libre Office - convert calc/exel schedule to csv from command line
I'm truing to batch convert some exel schedules to csv format from the command-line with the following command:
libreoffice --convert-to csv *.xlsx
I'm getting:
error
xsltParseStylesheetFile : cannot ...
1
vote
1answer
21 views
Get a list of file name, and compare to a csv file while adding 2nd and 3rd column of the same 1st column
I have a csv file that looks like this:
user1,400,300
user2,250,250
user3,400,300
user1,400,300
user4,400,300
user2,250,250
and a directory that has a empty file named after the user
$ ls /dir/*
/dir/...
1
vote
2answers
38 views
Extract information between double quotes on .csv
I'm having some problems with a script parsing a .csv file
Information on .csv:
#1,13/8/2020,somedude@hotmail.com,otherdude@hotmail.com,,Subject,"Dear Dude,
Information have been updated. Please ...
1
vote
2answers
551 views
Using CSV data with awk
I am trying to find a way to take the individual data from a CSV file, and use it as a variable within a grep or awk command. Either seems appropriate but I'm not sure how to tell it to do this ...
0
votes
0answers
209 views
Split csv file on multiple csv files based on several columns headers
I have a large csv file that i tried to split into several csv files based on certain columns in the header. The objective is to generate each file in relation to the columns of the country.
For ...
0
votes
4answers
174 views
Filling in blank spaces in the first column of a csv file using Bash / awk
I am looking into automating some processes / calculations but I may first need to format a slightly awkward CSV file set. (For this I am using bash, as requested).
The csv file set follows (roughly) ...
0
votes
2answers
82 views
Remove duplicated first fields of a CSV file
I try to remove repetitions of the same value of the first column from a CSV file without changing the other cell contents and alignment (in other columns).
My txt:
ACCIDENT EP 4 STEM PERCUS,, ...
5
votes
1answer
372 views
Adding suffix to filename during for loop in bash
This has probably been asked but the existing answers don't make sense to me. I am running a loop over multiple CSV files in a folder. This folder has non-csv files also and hence I must specify the ...
0
votes
5answers
341 views
how to check validation of csv file with two fields
I have simple csv file that should contain only two non-empty fields as the following
This is example of right csv file
$ more file.csv
why_we_need_help,log_low=53687091200
whats_is_going_on,...
-1
votes
1answer
488 views
i need to generate ansible-playbook using jinja2 with csv file in ansible
i need to create jinja templates to generate ansible playbook for that i need to read datas from csv file
csv file is similar to below(file name ansi.csv)
aaa,bbb,ccc,ddd
aa01,ansi,directory,yes
aa02,...
3
votes
4answers
318 views
How to add different string to duplicates in a single column of a tab delimited file in order to make them unique
I have a 4 column tab delimited file and the last column sometimes has duplicates. This is an excerpt from that file:
chr7 116038644 116039744 GeneA
chr7 116030947 116032047 ...
0
votes
1answer
56 views
Transpose few columns from a csv file based on several columns using awk
In my previous tag, i would keeping csv columns using a awk generic code. The answer was given here by @steeldriver.
In the following, I want to transpose some columns from a csv file to get a new ...
1
vote
1answer
36 views
create file name based on CSV column data using gawk
I have a very messed up CSV file where the header looks the following:
maindata.csv
time;area;measurement1;measurement2;measurement3;.....measurement12
I was able to split the single large file based ...
0
votes
1answer
62 views
Keeping columns of csv file based on condition of columns of header line
I wish to keep only certain columns of a CSV file, based on the structure of the header line.
Description of data:
In the header line (fields of country), several fields are empty.
The number of ...
