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 ...
0
votes
2answers
48 views
Organizing data by a header
I have two csv files.
File 1 contains header.
File 2 contains data
file 1 format : file1.csv
id,abc,xyz,aaa,bbb,ccc
file 2 format : file2.csv
id,source,value
1,abc,100
1,xyz,...
-2
votes
1answer
58 views
How to do awk to do sorting between a txt file and a csv file? [closed]
Here is the file that I am going to use:
file1.txt:
School JUPAS_N Univ ID Working_line_number
TST 11-2 CUHK 90544
file2.csv:
Date,JUPAS,UniType,DSE,PRA ,PE,SIP,BRA,DDI Group,...
3
votes
5answers
567 views
Replace the third comma on each line with newline
I have these long IDs which consist of four sections:
AKJHGFGUIKL,OIUYT,KJHBTYUI,98765434567
RTYUIKHGFGH,TYUJI,TGHYJKJKLJKL,6789876
ETRYTUUI,YTYUIL,UIOKJHGFGH,34567898766
I want to put the numbers ...
0
votes
2answers
42 views
How to sort the data from CSV file by comparing data from text file using awk output it as a TXT file?
There are two files I am going to extract the data:
file1.txt:
Type Serial ID Element Hit_Possibility
Yasuo 19-2 19623 Hasaki
file2.csv:
Date,Name,Order,Hit Possibility
12-Aug,Ken,1,256
12-Aug,...
0
votes
3answers
107 views
Field separator part of a column - incorrect parsing unix
I want to do a check the number of columns in a CSV file before processing it. The problem is that the delimiter (comma) also occurs in the text of some fields and because of that I cannot parse it ...
0
votes
2answers
64 views
How to remove duplicate items within the same line from a csv file?
I have a csv file with ~4000 lines, each one containing between 2 and 30 names separated by commas. The names are including titles (for example mr. X Adams or ms. Y Sanders). Some names exist multiple ...
1
vote
3answers
33 views
CSV file: Group and sum values based on first pattern of each value
I am using bash to create a script to find, group and sum fields in a CSV file. Each row will have comma-separated fields with each field following a similar convention. For each comma-separated field,...
3
votes
4answers
419 views
Bash: join data from two csv files
I have two csv files that contain various user data; they share one common field (username).
file A:
username ; Fullname ; mail
Bob ; Bob Hope ; bob@hope.org
file B:
username ; LastLogonTime ; ...
0
votes
1answer
39 views
Append lines from csv file to another based on column 1 matches
I have 2 .csv files, file1.csv and file2.csv. Say a line from file2.csv has the same 1st field as a line from file1.csv. I would like the full line from file1.csv to replace the full line from file2....
0
votes
3answers
52 views
AWK csv manipulation
I have a csv file containing something like this:
abc;!!!!;22
abc;!!!!;23
23;!!!!!;22
Now I want to delte every line that contains a 23 in the third column.
Since there is a possibility that there ...
0
votes
2answers
45 views
Rename a csv tab from UNIX bash script
I have CSV file that is generated from a bash script: UNIX_REPORT.sh. When you open CSV the file on the windGrr side with excel, the tab (or sheet) in the lower left corner is named UNIX_REPORT.sh.
...
0
votes
1answer
25 views
Interpolate Columnar Data
I am trying to find a way to interpolate between two lines of data in a CSV file, likely using awk. Right now, each line represents a data point at Hour 0 and Hour 6. I am looking to fill in missing ...
0
votes
3answers
42 views
Using AWK to add a new column with values to a csv file without creating empty new lines in between rows
I would like to add a new column to the end of a csv file and populating the column with values. I have used the following codes and it ends up showing new blank lines in between each record. Please ...
0
votes
2answers
48 views
Split datetime column in csv file into MM/YYYY and time column separately and write to the same csv
I am trying to use awk, split and then print but running into error
Input:
id day userId itemId
1 12/1/17 8:32 2232 tv
2 1/12/18 10:18 3232 fdfs
3 2/9/18 10:50 232 fdsf
...
0
votes
3answers
87 views
remove the lines in CSV that contain specific pattern on the second field
How to remove the lines in .csv file that second field contain the word content?
example :
ams-hbase-log4j,content:\n#LicensedtotheApacheSoftwareFoundation(ASF)underone\n#...
1
vote
2answers
34 views
How to merge different columns in the same file?
I have three files, as shown below:
FILE 1:
DATE PGTO_CRED
20180801 50.00
20180802 150.00
20180803 130.00
20180804 110.00
20180805 200.00
FILE 2:
DATE PGTO_TOTAL
20180801 ...
1
vote
1answer
33 views
Save each line of a .csv file as a separate .csv [duplicate]
I have a .csv file with no headers and want to have each line saved as a separate .csv file with the new file name to be the value in column 2.
0
votes
0answers
35 views
file command gives incorrect encoding type
I have a csv file in hand. When I ran 'file -i filename', it shows that it's encoded as us-ascii. But when I ran cat filename | csvcut -t -e us-ascii, I got an error:
"Your file is not "us-ascii" ...
-2
votes
1answer
40 views
Store the content of a txt file into a variable with comma separated [closed]
Hi I have a file txt file with keys
a.txt:
1234
4567
4568
4564
6754
warning message
invoke from function
I need to write the content of this file into a variable parm as
1234,4567,3456,2345
I ...
2
votes
1answer
55 views
Populate a CSV file from data file with nested loops in bash
I have a file, 4.txt that contains full paths to *.cfg files as well as additional data I need to strip for the final report (5.csv).
For example /source/EDDG/env1/dom1/proj/config/test.cfg
<...
1
vote
3answers
50 views
bash: assign variable and print to stdout in same command [closed]
I'm working on a bash script that reads a csv file with fields and field values and creates a json string out of the input.
For simplicity I have a bash script that takes in two arguments simulating ...
-1
votes
3answers
60 views
remove n columns from CSV file [closed]
I have a csv file with data as below :
12,1234,28-07-2018 05:28:12.21,ABC60,3456,28-07-2018 05:28:12.21,AB60,7580,28-07-2018 06:28:12.21,PQ
I need to remove all date columns from the file through ...
1
vote
1answer
34 views
Grep regular expression to show only serial numbers (defined length containing Alpha & Digit) in CSV File
I have a lot of (huge and chaotic) CSV files with a bunch of (Cisco) serial numbers.
My goal is to extract them (BTW: And to call the Cisco API later on for getting service/support coverage feedback)...
3
votes
4answers
61 views
Move files into separate directories based on grouping defined in a CSV file
RELATED: Move all files with matching prefixes to folder based on a csv list
I have a two-column CSV file (comma-separated values), with headers:
"id","group"
"F1256","old"
"E51651","new"
"X56369","...
-2
votes
3answers
72 views
Change all but first ',' to “<COMMA>” for each row in file (bash) [duplicate]
I am using bash and have a csv file (dat.csv) that needs to be only two columns (App, Blurb) of data but due to many ',' on each row it is becoming MANY columns.
EXAMPLE OF PROBLEM csv.dat:
App , ...
4
votes
3answers
249 views
How to print the count of pattern at each line?
I need to print the count of a matching string at the end of each line.
An example for matching foo:
foo,bar,foo,foo
bar,foo,bar,bar
foo,foo,bar,bar
Result :
foo,bar,foo,foo,3
bar,foo,bar,bar,1
...
-3
votes
1answer
40 views
Script for changing CSV into Key Value (KV) format
I have a CSV file with data as below:
row_identifier,DBNAME,tblsps_name,Cur_size,Max_size,Used,Free,Percentage
tablespace,MRETF,RERETOSB15_DATA,51200,45600,14284,31316,31
tablespace,MRETF,...
0
votes
4answers
60 views
How to compare lines in a single file line by line and append the output to the same file
I have a file abc.csv that has the below contents.
a6caa290d03ae75dd5f3bf9214f0b523;CO.jar,a6caa290d03ae75dd5f3bf9214f0b523;CO.jar,
a1a5bf1547f50feea4120290343f89e4;Common.jar,...
0
votes
0answers
51 views
Reading CSV and writing CSV with Base64 encoding using cat
So I been trying to make image encoding script what I got working with 3 columns but not with 4. Example data is just with 4 rows finished data will be 100-1000 rows... Delimiter on csv is set to | ...
1
vote
1answer
73 views
Switch columns in CSV using awk? [duplicate]
I have the exact same question as this one here. Only difference is I have 3 columns only and I am trying to move the 1st column to the end.
The original file looks like this:
col1,col2,col3
2,2015-...
5
votes
3answers
332 views
sed/awk to remove double quotes " that are inside of curly braces {}
I have a csv file with data similar to this:
"1b579a5e-9701-40eb-bd36-2bc65169da99","week14_Friday-019","6907eaad-1aff-4d26-9088-ba20374b67c0","2181-019","f20af5bb-c716-42e0-9b9d-cbolf5bfecea","15-...
-1
votes
3answers
150 views
Bash to read all columns and display blank cells in each row from CSV file
I need the script to go through each column and check for blanks and group it up for me into 1 column... so e.g.
1st row: banana , 2nd row: rachel, 5th row: rachel, monica, chandler, joey, janice, ...
0
votes
1answer
20 views
How to do sum calculations in libreoffice calc, single column
I have a .csv file which has two columns and about 50 rows of data in them. One of the columns has some numbers in it. What should have worked is -
=sum<B1:B50>
the result should be displayed ...
1
vote
2answers
436 views
Using bash to swap first and second columns in CSV
I'm using bash. I have a CSV file with two columns of data that looks roughly like this
num_logins,day
253,2016-07-01
127,2016-07-02
I want to swap the first and second columns (making the date ...
2
votes
1answer
50 views
Merge multiple lines column by column based on column 1 - same file?
I'm trying to organise a CSV file with 30 columns and 1000 rows.
I want to concatenate the rows column by column if the 1st column is the same, using a vertical line (|).
Input:
3,XX,YY,123,1234,MM,-...
4
votes
3answers
235 views
How to merge first two lines of a csv column-by-column?
I have an excel file that I converted to csv. When converted, it looks like the following example (Please note that there are 100+ columns in the csv. This is a minified version):
,Product," ",Citty,...
5
votes
4answers
94 views
Count by first column, count distinct by second column and group output by first column?
Please, I am new Unix command awk. I need a Unix command that will read a CSV file (over 700M rows) with the sample below:
A, 10
B, 11
C, 12
A, 10
B, 12
D, 10
A, 12
C, 12
The command will count the ...
0
votes
3answers
36 views
How to have trailing zero in CSV
The following is the sample csv file:
12354506.0,4
13129229.0,4
815612,5
7624107.0,5
6056548.0,5
The trailing zero in the first column is formatted to hole number on CSV VIEWER.
Is there any option ...
6
votes
7answers
1k views
Replace the comma with vertical bar |, except when inside double quotes, and remove double quotes
File1
12584,"Capital of America, Inc.",,HORIZONCAPITAL,USA,......etc
25841,"Capital of America, Inc.",,HORIZONCAPITAL,USA,......etc
87455,"Capital of America, Inc.",,HORIZONCAPITAL,USA,......etc
...
1
vote
2answers
71 views
Getting a row data from matching column in CSV file in Perl?
I am not able to attach the CSV file, so I have attached the images so that the content is clear. I used awk and perl one liners but that is not giving any output. Please help me know, how can I get ...
1
vote
1answer
57 views
Keep only N characters of specific column
I'm trying to treat a CSV file in away that I'll keep only the first 10 characters of a specific column values.
The field values are inside "" and has a comma in the middle which give me errors and ...
2
votes
3answers
44 views
Record data moving to New Line
I have CSV file in which few records the data is moving to New line:
Example:
ABCD,1234,QWER
ASDF
,2345,VGFT
"ASDF,12",1212,ASDR
1234,ZXCV,ERTT
the output result should be:
ABCD,1234,...
0
votes
2answers
338 views
Convert data from LDIF file to CSV
New to bash, need help to convert selected Attributes from the block of text between blank lines in the LDIF(text) file and convert it to CSV file with comma separated delimiter, similar to below ...
1
vote
1answer
40 views
How to find which lines are different in a CSV file in the Linux command line?
Im trying to find which lines differ in between file1.csv and file2.csv. if file1.csv looks like this:
1,2,3,4
5,6,7,8
9,10,11,12
and file2.csv looks like this:
1,2,3,4
9,9,9,9
5,6,7,8
9,9,11,12
...
0
votes
2answers
81 views
Split into file with first column Name
I have this CSV file:
"Product ID";"Product Name";"Price";"Description";
"11;"Example";"200";"Descripcion here...";
"21;"Example2";"300";"Some here...";
I want to split it line by line and save them ...
2
votes
4answers
285 views
Count repeated occurrences in a CSV?
I have a CSV with columns that look like the following:
Team Other Data More Data Result Time
Knicks A F Loss 2p
Celtics B E Win 2p
Lakers ...
1
vote
2answers
47 views
Remove suffix from data in col 4 of csv file only (bash)
I have a csv file that col 4 of contains a suffux of "_1" that I need to remove without disturbing the other columns in the csv that may or may not contain a suffix of "_1".
CSV FILE OUTPUT:
DOM,...
0
votes
2answers
46 views
How to split single column to multiple column in CSV file
I have below text in csv file and need to place FILE and TIMESTAMP into separate columns to a csv file. Could you please let me know how i can achieve this.
FILE, TIMESTAMP
/u01/app/xxcus/12.0.0/mds/...
0
votes
1answer
669 views
Export JSON to CSV with Headers using JQ
I have a (dictionary?) JSON file, similar to:
{
"Salad": {
"name": "Dressing",
"good": true,
"status": true
},
"Data_XML": {
"name": "XML",
"good": false
},
"...
2
votes
1answer
142 views
Bash- txt to column [closed]
I need to merge three txt files into one csv file, in which the first txt file goes to the first column, the second txt file goes to the second column, and the third txt to the third column.
The ...
