How to format .csv file with new data

rhbegin · 11-18-2011, 09:43 AM

I need some help, I need to add data to a user.csv file to upload it with username and password in this format below.

Presently the user.csv file is in this format:

login,password,email,quota
thiggins,d32817ae3e4d816710a795863fed3a5f,,50
jharris,57e80157d0777e51a0ede3015a2e6485,,50
badams,d97f26e3aadb5fe515ace91443a6079b,,50
cbingle,94a9584f8f49404ae70f87b96e140976,,50

My file I need to get in this order is in this format:

aagin@domain.com
u_password: welcome

willr@domain.com
u_password: welcome1

esmith@domain.com
u_password: wecome2

edam@domain.com
u_password: welcome3

gdan@domain.com
u_password: welcome4

I need to the new users in the same format like this:

aagin,welcome,,50
willr,welcome1,,50
jharris,welcome2,,50
badams,welcome3,,50
gdam,,welcome4,,50

I am struggling trying to figure this out, any advice/help would be great.

thanks

rhbegin · 11-18-2011, 11:43 AM

I figured out hot to get rid of the @domain.com and the u_password:

but my list is left with this

username
^password
it has a blank space in front of password???

how can I take this format of and make it

username,password,,50

rhbegin · 11-18-2011, 11:49 AM

Basically I got the file down to this:

the ^ is a space
username1
^password1
extra line not needed
username2
^password2

Just need to get it like this:

username1,password,,50

firstfire · 11-18-2011, 12:32 PM

Hi.
If your records are separated by blank lines then following awk program may work

Code:

$ awk 'BEGIN{RS=""; FS="@[^:]*: *"} {print $1","$2",,50"}' test.csv 
aagin,welcome,,50
willr,welcome1,,50
esmith,wecome2,,50
edam,welcome3,,50
gdan,welcome4,,50

where test.csv is

Code:

aagin@domain.com
u_password: welcome

willr@domain.com
u_password: welcome1

esmith@domain.com
u_password: wecome2

edam@domain.com
u_password: welcome3

gdan@domain.com
u_password: welcome4

From `man awk':

Quote:

.....
Normally, records are separated by newline characters. You can control how records are separated by assigning values to the built-in variable RS. ...
If RS is set to the null string, then records are separated by blank lines.
.....
As each input record is read, gawk splits the record into fields, using the value of the FS variable as the field separator. If FS is a single character, felds are separated by that character. If FS is the null string, then each individual character becomes a separate field. Otherwise, FS is expected to be a full regular expression.

Setting FS to "@[^:]*: *" we assume that records are separated by strings beginning with @, not containing semicolons in the middle and ending with semicolon, possibly followed by spaces.

Hope this helps.

rhbegin · 11-18-2011, 01:10 PM

Thank you a million!

I will give this try, I was getting stressed trying to figure this out.

thank you again I will report my results.

rhbegin · 11-18-2011, 01:13 PM

I tried the command and these are the results:

username1
^password1,,,50
username2
^username2,,,50
username3
^password3,,,50
username4
^password4,,,50

How can I get the password on the same line as the username?

It is still putting the username on one line and then the next line it has a (space) and 3 commas?

rhbegin · 11-18-2011, 01:43 PM

I am sorry it did worked perfectly I was using the wrong file.

Thank you again!

This was a HUGE time saver and benefit.