csv

How to remove double quotes within the double-quoted field values in .dat file

How to remove double quotes within the double-quoted field values in .dat file I have a text file that has around 15 columns.  The fields are separated by comma.  One column that is description is double-quoted and also has some words which are double-quoted.  I need to retain the beginning and ending double quotes and remove only the inner …

Total answers: 5

How to remove embedded newlines from CSV fields

How to remove embedded newlines from CSV fields I am struggling with processing a file. Normally I should have a line for each host definition. But sometimes, someone split some fields on different lines. Here is an example: "host1","host1","linux server","" "host2","host2","linux server","" Now I would like to find a way (better in bash) to fix …

Total answers: 7

How to create a new column and add a random identifier to it with miller

How to create a new column and add a random identifier to it with miller I want to add a column with a randomly created "case number" to my csv file. The first 2 letters of the casenumber must be any letter from A-Z in capitals. followed by 5 random numbers. input: COMPANY,NAME,STREET,ZIP,CITY,IBAN Test Ltd,John,Big …

Total answers: 5

Create new CSV files based on latest timestamp data

Create new CSV files based on latest timestamp data I have a script that accesses multiple dat files and produce csv files based on previous day’s data. these DAT files are updated on per minute bases with data from various instruments. Script snippet: gawk -F, ‘ { gsub(/"/,"") } FNR==2{ delete timestamp; #This code was …

Total answers: 1

How to convert a CSV with paragraphs to JSON on macOS's BSD Terminal?

How to convert a CSV with paragraphs to JSON on macOS's BSD Terminal? I have a huge text file that contains this structure: title;description;date The problem is that description main contain paragraphs, like: title;description;data MyText;Lorem ipsum dolor sit amet, consectetur adipiscing elit. Aliquam est dolor, molestie a rhoncus vel, congue sed diam. Vestibulum pellentesque libero …

Total answers: 1

awk on date conditions

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","Date of birth","Job Title" "1","9E39Bfc4fdcc44e","new, Diamond","Dudley","06 Dec 1945","Photographer" "3","32C079F2Bad7e6F","Ethan","Hanson","08 Mar 2014","Actuary" "2","aaaaaaa, bbbbbb","Grace","Huerta","21 Jan 2023","Visual merchandiser" So, the expected output would look like(last row got …

Total answers: 5

Replace a block of spaces with a comma

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 to replace the blocks with 3 …

Total answers: 5

Convert json to csv with headers in jq

Convert json to csv with headers in jq Is it possible to convert this json: [ { "bytes": 276697, "checked": false }, { "bytes": 276697, "checked": false } ] to a table WITH headers in jq? I’ve tried: cat file.json | jq ‘.[] | join(",")’ but it omits headers: "276697,false" "276697,false" it should be: "bytes,checked" …

Total answers: 2

sort the whole .csv based on the value in a certain column

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. I …

Total answers: 6

Deduplicate CSV rows based on a specific column, with a CSV parser

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 awk because my data is a complex CSV file with …

Total answers: 2

Convert a fixed-width file to CSV and remove trailing space

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, I succeed to add …

Total answers: 8

how to download postgres data in csv format through bash script?

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 <user> -W -h <host> …

Total answers: 1

Visually aligning columns of a CSV file

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:44:00 to: a, b, c, some stuff,"some, other, stuff", d, 2023-03-10 18:37:00<EOL> x, y, z, t, cool, thing, 2022-04-12 21:44:00<EOL> There are some …

Total answers: 3

Bash script to calculate within a CSV

Bash script to calculate within a CSV 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;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; 1402141;A51U;8,84333E+11;HP;2;2;0;0;0;0;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; 1450121;LC125XLC;4,34444E+12;Brother;11,4;11,4;0;0;0;0;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; 5431414;YEG-00431;343434315;Msoft;11,1;11,1;0;0;31,45;41,31;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; 5533314;QQC-08323;8,85341E+11;Microsoft;522,23;522,23;0;0;0;0;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; 3140025;30MB0SY0-M0EAY0;1,4123E+12;Asus;11,33;11,33;0;0;0;0;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; 1452531;R18-05435;3,33334E+12;Microsoft;24;24;0;0;1,8;1,33;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; 4480158;M28-00002;;Meyss;54,22;54,22;0;0;11,13;31,13;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; 2310152;AC2T0E;;HP;52;52;0;0;0;0;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; I want to execute the following formula via bash script: =WHEN(I2=0;E2*1,2;I2) price should be displayed in column x can you do that with awk or gawk? I’ve tried but can’t get any further. …

Total answers: 2

Rounding many values in a csv to 3 decimals (printf ?)

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.200 0.334 0.111 For one value this is working: printf "%.3f" …

Total answers: 3

Combine .csv-files with different amount of lines

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,WORD cats,002a,WORD mouse,003c,WORD One solution (from my previous question) is: paste -d, file1.csv …

Total answers: 3

Combine .csv-files with text between each line

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 , file1.csv file2.csv dog,001a cats,002a mouse,003c But I want this output …

Total answers: 1

Converting key+n-values text file to a CSV file

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: 999 My goal is to write a bash script …

Total answers: 1