Unix Scripting to read CSV file then change format

Question

I'm new to unix scripting and would appreciate some direction or help. I want to make a script that reads through a csv file containing Information similar to below.

Business Group  Used Disk Space (GB)    Disk Quota (GB)  Grace Period
EandT-Mech  35403.4                   37434.2              none
Rotatives-AD    6.40E-05                      524.288              none
Nacelles-Mech   0.056832                      1677.72              none

then I need to in that same script once it' either read or reading change it into this format:

INSERT INTO Storage (b_group, used_space, disk_quota, grace_period)
VALUES (‘TSandD-Aero’,6.40E-05,734.003, ‘none’);
INSERT INTO Storage (b_group, used_space, disk_quota, grace_period)
VALUES (‘EDS-Admin’,192.076,3568.01, ‘none’);

so just change the values with the 4 information below to whatever has been read in on the csv file.

so to sum up, read csv file, put into sql formit and commit that into my database, any help or direction to get me going would be appreicated as I can't find anything similar online.

This doesn't look like a csv, it looks like it's either tab delimited or constant column width. Either way, have you tried cut or awk? — cha0site
– cha0site, Commented Feb 28, 2012 at 11:38
Awk??? Perl at least. Also your DBMS probably has a bulk-import tool which can do this out of the box. — Ben
– Ben, Commented Feb 28, 2012 at 11:39
@cha0site CSV has "comma" in its name, but usually describes a "delimiter seperated file". In fact, excel (for example) uses tabs as delimiter when you select "CSV" as target format. — Kaii
– Kaii, Commented Feb 28, 2012 at 11:41
its data I've copied from a csv file that was in excel so its tabbed delimeter then, I will give them a go and let you know how I get on and im using psql database so I've got no import tools on it I believe — Zenaphor
– Zenaphor, Commented Feb 28, 2012 at 11:43

kev · Accepted Answer · 2012-02-28 11:44:23Z

1

$ cat input.txt | awk 'NR>1{printf "INSERT INTO Storage(b_group, used_space, disk_quota, grace_period)\nVALUES(\"%s\", %s, %s, \"%s\");\n",$1,$2,$3,$4}' | tr '"' "'"

INSERT INTO Storage(b_group, used_space, disk_quota, grace_period)
VALUES('EandT-Mech', 35403.4, 37434.2, 'none');
INSERT INTO Storage(b_group, used_space, disk_quota, grace_period)
VALUES('Rotatives-AD', 6.40E-05, 524.288, 'none');
INSERT INTO Storage(b_group, used_space, disk_quota, grace_period)
VALUES('Nacelles-Mech', 0.056832, 1677.72, 'none');

answered Feb 28, 2012 at 11:44

kev

163k49 gold badges286 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zenaphor Over a year ago

Sorry kev could you explain how thats done? I was looking at wiki.postgresql.org/wiki/COPY and thought I could use the /i to read in a script file that copies in to table storage how I would need it to if you have any ideas?

kev Over a year ago

It's just basic awk command: $2 is field#2. I'm not familiar with postgresql. Sorry, I couldn't give you any further advice.

Zenaphor Over a year ago

as I'm a new user and can't answer my own question yet I will post it here: from within postGreSQL all I need to simply do is \i h/location/runtestscript in that script all I do is say delete from storage; COPY storage from '/h/u544835/dehpc14_Disk_Quota_Report.csv' DELIMITERS ',' CSV; so i delete all ones in there, then re-add the file as that gets updated every 6 hours when I need to, so my data can stay fresh.

Zenaphor · Accepted Answer · 2012-03-07 09:14:22Z

0

COPY storage from '/h/u544835/dehpc14_Disk_Quota_Report.csv' DELIMITERS ',' CSV;

answered Mar 7, 2012 at 9:14

Zenaphor

8015 gold badges14 silver badges21 bronze badges

Collectives™ on Stack Overflow

Unix Scripting to read CSV file then change format

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related