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).
925
questions
1
vote
3
answers
157
views
Convert lower-case to uppercase with the output to a new column using miller
I want to copy column NAME to column NAME-LOWERCASE. NAME-LOWERCASE should only contain lowercase letters. The uppercase should be untouched in all column expect NAME-LOWERCASE
input
NAME,test
PTC,N
...
- 175
1
vote
5
answers
621
views
awk on date conditions
I am trying delete rows from a person.csv (below) for the condition that person who are not born in last 1 year:
Dataset1:
"Index","User Id","First Name","Last Name&...
- 21
0
votes
1
answer
91
views
Need Help with Date expression - AWK in a csv file
Situation: csv file has date column with different date formats, I want to convert them into a unambiguous date format (like +"%m-%d-%Y").
Example File data : File name = Date_Test_new.csv
3/...
4
votes
5
answers
574
views
Replace a block of spaces with a comma
I have a file in txt format that I want to convert to csv. Between each field there are blocks of spaces. The amount of spaces is not the same between every field.
Is there a command with sed or awk ...
- 175
2
votes
2
answers
144
views
Convert json to csv with headers in jq
Is it possible to convert this json:
[
{
"bytes": 276697,
"checked": false
},
{
"bytes": 276697,
"checked": false
...
- 107
0
votes
1
answer
104
views
Process CSV file through command line: Remove only middle rows between consecutive row entries if consecutive entries have the same 2nd column value
I have a CSV file with only 2 columns (but lots of rows) and the occasional irregular row which always starts with an asterisk (*) character and may span more than two columns. Using just the Linux ...
- 161
3
votes
3
answers
92
views
Creating text files with comma separated list of values from one column based on shared value in second column from the commandline
I am hoping to create a series of text files each containing a comma separated list of values from column 1 of an input file based on their shared value in column 2 of the input file.
Input file ...
1
vote
4
answers
134
views
Sed or Awk (or something else) removing several columns from csv file
I have several files from tool suppliers which I need to merge together. However only a few of the columns in the csv file gets used. So I want to remove the irrelevant columns. I have used sed to do ...
- 175
5
votes
6
answers
1k
views
sort the whole .csv based on the value in a certain column
I have a csv file with different revenues. I want to sort the csv file on revenue from high to low value. I can't find how to do this in terminal without the use of python.
I don't want to use python.
...
- 175
6
votes
2
answers
375
views
Deduplicate CSV rows based on a specific column, with a CSV parser
I searched for this task, and found the following older questions:
Removing Duplicates from a CSV based on specified columns
Identify unique records on CSV based on specific columns
But I can't use ...
0
votes
5
answers
434
views
How to remove commas from a certain field in CSV?
There is a field/column in a CSV file Im working with called name, this is always the second field.
The values in this column are like: "Smith, John" "Brady, Tom" "Manning, ...
- 1
8
votes
8
answers
828
views
Convert a fixed-width file to CSV and remove trailing space
My input file is:
$ cat -e myfile.txt
999a bcd efgh555$
8 z 7 $
1 xx xx xx 48 $
And I need a CSV without trailing spaces in the columns:
999,a bcd efgh,555
8,z,7
1,xx xx xx,48
So far, ...
- 133
0
votes
2
answers
203
views
Append string to the 2nd column if a condition is satisfied, using sed
CSV file:
A,12345,hello
B,67890,hi
A,123456,yo
Expected output: Should add two zeros at the end of the 2nd column if the 1st column is A:
A,1234500,hello
B,67890,hi
A,12345600,yo
- 21
-1
votes
1
answer
194
views
Read column1 of csv file2 and match with column2 of file1, if text matches then add column1 value from file1 to file2
file1.csv
9,hello-world.com.,A,192.168.0.9
1,hello1-world.com.,A,192.168.0.1
2,hello2-world.com.,A,192.168.0.2
3,hello3-world.com.,A,192.168.0.3
4,hello4-world.com.,A,192.168.0.4
5,hello5-world.com.,A,...
- 11
0
votes
1
answer
304
views
how to download postgres data in csv format through bash script?
I am able to connect to db also getting output but it's not coming in proper format. So how to get the output in csv like format and also want to save that in csv format.
psql -d <db> -U <...
- 103
2
votes
1
answer
107
views
gawk is treating NF and custom variables differently even though they have the same value
I have the below awk script where I'm using the length of the array passed, as a variable.
BEGIN {
FPAT = "([^,]*)|(\"[^\"]*\")"
len=split(_arr,arr," ")
}
{
...
-1
votes
6
answers
212
views
Minimum value from ";"-delimited list of values in each field in CSV file
I have a big CSV file in which, in some cells, there is more than one value. How can I change these cells so that they instead only contain the minimum value?
For example, given this input:
id,...
- 131
0
votes
1
answer
50
views
Trying to sort output of mailbox sized
Trying to sort an output file of mailbox sizes.
[email protected], 1.35 MB
[email protected], 2.19 GB
[email protected], 0 B
[email protected], 0 B
use[email protected], 1.66 MB
[email protected], 0 ...
- 1
3
votes
3
answers
777
views
Visually aligning columns of a CSV file
Using sed or awk, is it possible to visually align columns in a CSV file?
For example:
e.g. from:
a,b,c,some stuff,"some, other, stuff",d,2023-03-10 18:37:00
y,x,z,t,cool,thing,2022-04-12 21:...
- 429
-3
votes
2
answers
157
views
Bash script to calculate within a CSV [closed]
This is my CSV File:
Number;Reference;EANNumber;Manufacturer;Price_B2B;Price_B2B_Dis;Price_B2B_DisPer;Price_B2B_DisAmount;Price_B2C_exVAT;Price_B2C_inVAT
2330113;BP3141;1,31304E+11;APC;13;13;0;0;0;0;;;...
- 11
5
votes
3
answers
773
views
Rounding many values in a csv to 3 decimals (printf ?)
I have a paste command like this
paste -d , file1.csv file2.csv file3.csv
And file2.csv contains numbers like this
0.2
0.3339
0.111111
I want the values in file2.csv having 3 decimals like this:
0....
- 167
0
votes
3
answers
85
views
Combine .csv-files with different amount of lines
Here I asked already for a similar problem.
Now I have 3 .csv-files but one of them has only 1 line.
file1.csv
dog
cats
mouse
file2.csv
001a
002a
003c
file3.csv
WORD
The output should be
dog,001a,...
- 167
1
vote
1
answer
59
views
Combine .csv-files with text between each line
I have two (or maybe more) files:
file1.csv
dog
cats
mouse
file2.csv
001a
002a
003c
If I use paste file1.csv file2.csv the output is
dog 001a
cats 002a
mouse 003c
Of course I can use paste -d ,...
- 167
0
votes
1
answer
796
views
Converting data in text file to csv tables
My data in a text file have the below format today
HKL1234/2, R1B
HKL1274/2, R4E
HREF457-74, R1A
I need to present this data in excel file in two different columns like below:
Product ...
0
votes
1
answer
125
views
Converting key+n-values text file to a CSV file
Newbie on this site, so apologies if I ask this question badly:
I have a text file in the following format:
file: abc
value: 123
value: 234
value: 567
file: def
value: 999
file: ghi
value: 123
value: ...
- 3
1
vote
1
answer
129
views
Create csv with filename, mtime and filesize in GB
I use the below command to extract the filename, filesize, and date for a big directory with many subdirectories.
find MY_PATH -type f -exec ls -la --block-size=GB --time-style=+%Y-%m-%d_%H:%M:%S {} \;...
- 117
0
votes
2
answers
237
views
Choose columns with sort and cut in a csv with a comma delimiter ',' ignoring data on quotes with comma "text,text"
I am parsing a huge csv file with rows and columns with different parameters. However, some fields contain large descriptions within quotes that contain commas. How can I choose columns with sort and ...
- 3
3
votes
5
answers
349
views
Replace empty lines in one file with lines from another file
I have file1.csv
"word 1"
""
"word 3"
""
"word 5"
"word 6"
and file2.csv
"replacement text 1"
"replacement text 2"
&...
- 167
0
votes
0
answers
28
views
How to remove '^M' when there is '<br>' in next line? [duplicate]
When we are trying to load csv file using datastage into HANA, one of our columns value contains carriage returns in the middle of the text, due to that datastage is not able to read that record.
For ...
- 1
0
votes
2
answers
226
views
How to cut a string CSV variable with unknown values count in bash, and loop over its values?
It might seem very easy, but I'm stuck at this.
I have a string that is CSV and the length of items is unknown.
"item1,item2,item3,..."
I want to cut it, and loop over its items.
I tried:
...
- 545
-1
votes
3
answers
455
views
Searching in a CSV for multiple values and get the associated values from it
I have a file.csv
"ItemNo","Name","Weight"
"a001","Item a","1.1"
"a002","Item x","1.2"
"a003","...
- 167
1
vote
4
answers
241
views
Count unique values and add result values as new column
I have large csv file and want to build simple ranking:
$ cat file.csv
2022-12-01 RED 1
2022-12-01 RED 1
2022-12-01 RED 2
2022-12-01 RED 2
2022-12-...
0
votes
3
answers
59
views
Read column value in a CSV file starting from another column value?
I am working on extracting certain data from OECD statistics. These are available in a CSV file and look as follows (excerpt):
"COUNTRY","Country","DAGEGR","Age ...
- 103
0
votes
1
answer
2k
views
jq: error (at input.json:45): Cannot index array with string "value"
I try to convert my .json files to .csv with jq. I'm getting cannot index array with string value on all my values. .json
{
"organic_data": [
{
"description": "...
- 175
0
votes
4
answers
217
views
Awk: Concatenate columns if they are quoted, and remove commas
I have this data:
COL1, COL2, COL3
a, b, c
d, "e,f,g", h
I want Awk to treat it as:
COL1, COL2, COL3
a, b, c
d, efg, h
Each comma-separated string could be any number of characters. I just ...
- 111
-1
votes
2
answers
591
views
Commands runs in shell, but not in script: no directory found error
I have a few commands that don't run as a script.
It's supposed to create a bulleted list of the file names, then sort the columns of each file alphabetically, grab the second and its matching third ...
- 25
0
votes
1
answer
121
views
Quoting a string in output from awk
I am fetching a string from a file using awk as shown below. Now I would like to double quote it. Any support would be highly appreciated.
awk -F',' '{print $(NF)}' sample.csv| tail +2
output:
...
0
votes
1
answer
70
views
How do I split a single column in a .csv/.xlsx file to multiple columns using a bash command/ linux command?
In column A, you can see some text which contains "/".
I want to split column A into 4 different columns using the delimiter "/".
So Year should be in column A, ...
0
votes
4
answers
97
views
extract every 1st & 5th line from a file and put them on one line, comma separated with awk
I have a file, it is a simple music playlist, but the data is as follows
Song
Artist
Album
Date Song was added to playlist (mm/dd/yyyy)
Song Duration (mm:ss)
What I would like to do is just to get ...
-3
votes
2
answers
514
views
How to get difference of two csv files by columns and write diff into 3rd file?
I have two csv files like this;
422174,XN,20.99,2020-09-01,2022-01-20 20:20:28.613+00
421348,SB,21.99,2021-01-26,2022-01-20 20:20:28.613+00
885176,XN,41.80,2021-11-17,2022-01-20 20:20:28.613+00
881751,...
- 27
-1
votes
1
answer
443
views
How to get difference of two csv files and write diff into 3rd file?
I have two csv files like this;
422174,XN,20.99,2020-09-01,2022-01-20 20:20:28.613+00
421348,SB,21.99,2021-01-26,2022-01-20 20:20:28.613+00
885176,XN,41.80,2021-11-17,2022-01-20 20:20:28.613+00
881751,...
- 27
0
votes
4
answers
90
views
Filter CSV file records where no floating point value appears in a given field, with header output
I want to filter out records from a CSV file where the postal_code field has floating point values and i also want the header in the output.
A sample CSV file would be:
> ca test.csv
employee_id|...
- 1
0
votes
2
answers
763
views
compare 2 csv files on multiple columns and replace one column with column from another file
I have to compare 2 files file a and file b with the same columns but are randomly placed in 2 files like compare column values
account, code, date, type, pc, vol and bs and if a match is found for ...
- 1
1
vote
1
answer
70
views
Interpolate timestamps and replace empty fields in CSV files
I have CSV files with the following elements:
timestamp, data1, data2, data3, data4, data5
2015-03-16 00:00:00, 3, 3, 4, 2, 5
2015-03-16 00:10:00, 4, 7, 8, 9, 3
2015-03-16 00:20:...
- 79
2
votes
6
answers
950
views
How to swap column order in csv file using awk?
I want to change order of this CSV
92000,X,19.30,2022-03-01 05:31:58.990562+00,19.29,
00293,X,40.64,2021-09-30 13:10:32.084467+00,40.49,
00293,X,80.95,2021-09-30 13:10:32.084467+00,80.49,
00293,X,41....
- 27
0
votes
1
answer
78
views
comparing 2 files in csv based on few columns and replace one column in first file with the value of matched from another col in 2nd file using shell
i have a bit of an ask in comparing 2 files based on respective columns which are not in order.
file 1
AAAA,BBBB,CCCC,DDDD,EEEE,FFFF,GGGG,HHHH (header)
a,b,c,d,e,f,g,h
aa,bb,cc,dd,ee,ff,gg,hh
aaa,bbb,...
- 1
0
votes
1
answer
584
views
Display & edit tabular data in the terminal
I'm looking for an app to display & edit CSV files as a tabular data in cells, similar to GUI spreadsheets like LibreOffice Calc.
I just need simple viewing, cell editing & easy navigation ...
- 11
-1
votes
3
answers
96
views
Extracting SMS data from a XML
I have a large xml file of my SMS messages. I want to put them into an easily accessible csv format. I've been trying to extract the 'address', 'messageBody' and 'messageTime' sections specifically ...
- 1
0
votes
1
answer
125
views
Convert terminal tables into csv or json
Does a tool exist to convert tables on the command line like snap list or flatpak list into csv or json ouput.
I imagine it like this: <name> "snap list" --csv
- 13
0
votes
3
answers
334
views
How to remove lines from TSV file where columns are empty or all whitespace?
I've a tab-delimited file, e.g. myfile.tsv:
abc\tfoo
xyz\tbar
but sometimes, it has some blank columns, e.g.
abc\tfoo
xyz\tbar
what\t
\tthe
bleep\tsleep
i.e.
$ printf "abc\tfoo\n" > ...
- 123