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).
786
questions
3
votes
1answer
20 views
Reading empty string from CSV file in BASH
I'm using the following gawk script to read values from the first column of the csv file file.csv. I use gawk since I don't want any embedded commas to be ignored.
col=`gawk '
BEGIN {
FPAT="([^,]...
1
vote
3answers
53 views
Performing CSV Split effectively keeping limit into consideration
I have to split a large CSV file into multiple small files with a limit of 1000 records in each small file. However, I don't know beforehand how many records will be there in that large CSV file. I ...
2
votes
1answer
46 views
Create Symlink Glob of directory read from a file
I am extracting names of directory at 5th column of tsv file ExtensionList.tsv and I want to create symlinks to all those folders with names beginning with that located at ${HOME} inside Extension ...
0
votes
1answer
49 views
How to write awk script to show all the lines/records between the specified range entered by user?
Suppose this the data.lst
Anil Sharma, Vaishali Nagar, science, 45, 67
Manoj Gupta, Sri Nagar Road, commerce, 66, 89
Kamal Sharma, Shastri Nagar, commerce, 81, 32
Rama Sharma, Vaishali Nagar, commerce,...
1
vote
2answers
71 views
How to store .txt file data in different columns of a CSV file using BASH?
I have a .txt file with the following data structure:
Scan Times:
33.3 seconds
77.4 seconds
33.3 seconds
77.4 seconds
Check Times:
110.30 seconds
72.99 seconds
72.16 seconds
110.30 seconds
...
0
votes
4answers
164 views
How can I extract the entire columns that its column name match a certain pattern of a CSV file?
I am not too familiar with Unix and am working on a very large CSV file right now.
Here is an example:
ABC1,ABC2,ABC3,DDD,EEE,FFF
1,2,3,4,5,6
1,2,3,4,5,6
How can I extract all columns that start with ...
1
vote
2answers
45 views
How to convert xlsx file to csv using shell script?
I have tried with libreoffice, ssconvert, xlsx2csv to convert the file but I am getting "command not found". Could you please help on that?
xlsx2csv
ssconvert file.xlsx file.csv
libreoffice -...
0
votes
1answer
30 views
Write values with embedded commas from arrays into CSV file using BASH
I am trying to write different arrays into a csv file ($file) columns.
Each array has the same number of values.
For example, arr1 and arr2 have 3 values each
arr1=( 23 45 34 )
arr2=( "abc,d"...
2
votes
3answers
57 views
How to create CSV with headers from JSON using jq
I am trying to create a csv file with headers from a json using jq.
I have the following snippet https://jqplay.org/s/H_U5mxbTFW
Question:
How can I print the values of peerings > accept_vpc_info &...
0
votes
3answers
37 views
Extracting domains from a CSV URL column using Miller
Having CSV content similar to this:
Family,URL,IP,FirstSeen
Pony,http://officeman.tk/images/admin.php,207.180.230.128,01-06-2019
Pony,http://learn.cloudience.com/ojekwaeng/yugo/admin.php,192.145.234....
-1
votes
4answers
123 views
Combining different terms in third column into one row according to sample code
I have some genomic data with three columns. Column 1 is samples, Column 2 is cancer type, and Column 3 is mechanism. Each sample has one or more gene mechanisms assigned to it (e.g. 6 ecDNA) in the ...
0
votes
1answer
48 views
Reading in a CSV file using while IFS= but not treating the comma as the separator if the field is in quotations
I have a large CSV file where columns are separated by commas in each case. However, about 5% of the time the first column contains a comma within itself and is denoted this way by the fact that these ...
-2
votes
1answer
45 views
Unix - Sed Command Related
#filename as $1
#business name as $2
#updated in the year $3
#It can match to any commas inside quote values as long as it is before the matching V for postal code. They all start with V.
./script6_1....
1
vote
2answers
22 views
Converting files in multiple directories from tab separated to comma separated
I am running Mac OS.
I have a directory /Users/sethparker/Documents containing several subdirectories /Users/sethparker/Documents/dir1,/Users/sethparker/Documents/dir2,/Users/sethparker/Documents/dir3....
2
votes
4answers
96 views
Regex match shell variable as a pattern (case insensitively)
A snippet of a typical tsv file I have used
10 Interstellar Main Theme Extended UDVtMYqUAyw
11 Journey XvG78AmBLc4
12 Jurassic Park Music & Ambience Amazing Soundscapes and Music ...
4
votes
4answers
381 views
Read file and store as array without skipping empty strings
File.tsv is a tab delimited file with 7 columns:
cat File.tsv
1 A J 1
2 B K N 1
3 C L O P Q 1
The following reads File.tsv which is tab delimited file ...
1
vote
3answers
54 views
Concatenate N number of csv files to one single csv file
I have a requirement to build a shell script which would concatenate all available CSV files into one. All these CSV files have a similar structure (i.e same headers and number of columns) and these ...
0
votes
4answers
746 views
Grep and Cut command in linux
I have a csv file with loads of data. I wish to cut the 9th column for values >=1 and then use grep to display full rows that match.
Sample format:
ABC,XYZ,RTY,CREAM,FRANCE,170019,ST REMY CREME,...
5
votes
7answers
143 views
How to remove \n in a string
I have a tab delimited text file where the 23rd column with string has \n in it which causes it to break to the next line.
I opened the text file in vi and enabled white space characters and I can see ...
0
votes
0answers
33 views
How to write .csv from another .csv in Bash or Ksh
I have a lot of output's from my matlab in .csv. I would like to put them together in one output.csv file.
So my idea was use my .csv created by Matlab as variables for my output.csv global.
#!/bin/...
3
votes
7answers
294 views
Find and add quotes in between particular string
when I'm working with csv, unwanted commas(',') is misleading my csv file, in result it gives the inconsistency.
please find in details below.
My sample csv file:
1|a,b|4
1|c,d|4
1|e,f|4
1|g,h|4
1|i,j|...
0
votes
1answer
22 views
split a large csv file
I have a game_data.csv file with over a million rows (70MB in size). I am writing an app that is going to do some processing on the CSV and write the CSV data into a database.
Whilst I'm developing ...
0
votes
2answers
68 views
awk: calculate the occurrence of the elements in a given column of CSV
I am dealing with the analysis of multi-column data file (here is an example of 10 lines, but the real log will contain 150 lines!) in the following format:
ID(Prot), ID(lig), ID(cluster), dG(...
4
votes
1answer
92 views
Bash Script : Problem with variables from csv file
I'm having a little problem getting variables from a csv file on my Linux machine and using them in an if. I have the following csv:
Name;Age
Marc;18
Joseph;10
I'm trying to get this information from ...
1
vote
2answers
111 views
Use awk to select only select lines with one field smaller than threshold from CSV file
Dealing with the post-processing of multi-column csv file contained many (10000+) lines:
ID(Prot), ID(lig), ID(cluster), dG(rescored), dG(before), POP(before)
9000, lig662, 1, 0.421573, -7.8400, 153
...
2
votes
1answer
90 views
awk to return percentage by day of the week [closed]
So I have a dataset of flights in a CSV file and I want to get a percentage of flights delayed (column 6) ordered by day. 0 is for not delayed and 1 is for delayed 15 or more minutes.
Input:
...
-1
votes
3answers
71 views
Store terminal output in HTML file
I have a CSV file that I'm counting the occurrences of column 4:
Input:
2,Serveis,14,"Restaurants, bars i hotels (Inclos hostals, pensions i fondes)",Restaurants,1
2,Serveis,11,"...
1
vote
4answers
114 views
Split CSV file with timestamp along breaks for defined period
I wonder if there's an easy way, maybe one liner, with unix cli tools to split a CSV file with ISO-8601 UTC timestamps in millisecond precision (+%FT%T.%3NZ, e. g. 2021-05-27T13:59:33.641Z) along a ...
0
votes
4answers
62 views
add column, populate entire column with name of file (without the “.csv”) -for multiple files in BaSH
Caveat: Absolute beginner. I need to add a column to a .csv file where the column header can be "Name", but the entire column should be exactly the same -- the name of the file itself, the ...
0
votes
0answers
35 views
Filter csv file with AWK passing a variable in the regex [duplicate]
I need to filter a very large csv file in the most efficient way. I already tried csvgrep but is not efficient timewise, so I'm now trying to use AWK for this task.
The csv file has a ; separator, and ...
2
votes
2answers
97 views
awk extract columns that meet condition
I'm writing a script to prepare a csv file that takes columns number 5, 6, 7, 8, 10 and 13; takes the rows that on column 44 are equal to 7 and also meet that the rows that on column 3 are equal to 1, ...
6
votes
2answers
639 views
awk script to prepare csv file
I'm stuck creating an awk script that prepares a csv file before analysis. I need to create an output file with columns 1-2, 10, 13-15, 19-21. Also I need to replace the numbers on column 2 to the ...
1
vote
3answers
54 views
Bash to send variable value to csv?
I've the following data. I've only given 2 servers here for the sake of simplicity. This servers list might be change from time to time, the same goes with the variable value.
hostname = 'server1'
...
0
votes
2answers
54 views
Reshaping tab separated file
I have a file that looks like this:
Time Flag
0.65 5.885581e-01
0.56 5.847484e-01
0.58 5.278409e-01
0.57 1.140746e+00
1.00 0
0.00 0
1.00 1
0.00 0
But this is because I ...
-2
votes
1answer
62 views
How can I easily move all the text in this csv file to the beginning of the line?
I am new to Linux and also manipulating csv files. I am trying to figure out how to easily move this large column of text to the beginning of the line to where there are no spaces before the digits. ...
-1
votes
2answers
44 views
comparison to get only lines with changed values on a column
Given the following two csv files, previously sorted:
file1
a,1,val1
b,2,val2
c,3,val3
d,4,val4
e,5,val5
f,6,val6
g,7,val7
file2
a,1,val1
b,2,val2
c,3,val3x
e,5,val5x
g,7,val7
h,8,val8
How can I ...
1
vote
2answers
57 views
Search for special characters and character space
Hello I am currently working with a csv file.
I want to find a bash command that will help me find special characters
?, !, #, *, %
and also and character spaces such as
' '
any advice would be ...
0
votes
6answers
122 views
sed: truncate decimals in csv file column
I am trying to remove the entire decimal part of the numbers of a csv file, with sed. All I can do is truncate all the decimals (that are .0) from the whole columns of the file and I don't know how to ...
2
votes
3answers
122 views
awk: split datetime column into three separate columns in a csv
I am trying to use substr to split a datetime column, the fifth one (previous_test) into three different ones at the end.
Input:
id,tester,company,chief,previous_test,test,date,result,cost
6582983b-...
-1
votes
2answers
62 views
Compare two csv files containing floating-point data and print their difference statistics
I have two csv files: a.csv and b.csv. Both these files contain same set of rows. For example:
a.csv:
Yield
5.68
1.34
1.47
9.01
7.59
b.csv:
Yield
5.68
2.01
0.78
7.98
8.17
I need to compare these two ...
0
votes
1answer
44 views
Generate a CSV from select files' directory information
I'm on a mac, and I'm afraid this command line problem has me stumped. At least I'm assuming the easiest way to do this would be through the CL.
I'd like to recursively search a directory tree for ...
1
vote
0answers
15 views
Histogram of timestamps in LO Calc?
I have a CSV of values, where the first column contains dates (M/DD/YYYY), and the second times (HH:MM:SS). Is there an efficient way to get a histogram of the frequencies of each datetime?
I've tried ...
0
votes
1answer
53 views
Using sed to remove LF but keep CRLF in file
I have unquoted newline LF in CSV file that I want to load to database, causing error. Can I use sed or other tools readily available in Linux to remove this newline?
Actual line ending for the CSV ...
3
votes
1answer
171 views
How can SQLite command “.import” read directly from standard input?
How would you modify function csv_to_sqlite so that sqlite3 command .import reads directly from standard input instead of from a temporary named pipe?
#!/bin/bash
function csv_to_sqlite() {
local ...
0
votes
1answer
119 views
Combine multiple CSV files with Same Header into multiple group files
Hi I am looking for a fastest solution to process loads of csv files.
Situation:
I have multiple csv files in a single folder with different headers
I have already done pre-processing on them to ...
0
votes
1answer
52 views
Filter a CSV file to remove if Field 1 matches and the date/time in field 3 is less than 5 mins from the first field-1 match
Using a bash shell script on Ubuntu on a Raspberry Pi, I am trying to remove lines from a (comma separated) CSV list where { Field 1 matches AND Field 3 is less than 5 mins (300 secs) from the first ...
1
vote
2answers
182 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
55 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
66 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
110 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 ...
