CSV to individual text files

[DanW]

I have a CSV file with the following headings

 

EmailAddress Password Name

 

and 500 records .. containing all the students users email addresses and passwords.

 

 

What i would like to do is export each line to a text file that is called 'Email Username & Password'

 

and inside the text file do the following

 

==

You new Student email address is %EmailAddress% and your password is %Password% you will be required to change you password the first time you login.

 

Student email is now located at http://studentemail.domain.com

 

 

==

 

I'd also like to put the new textfile in a folder called %Name%

 

 

 

Any help will be greatly appreciated

 

 

Dan

[mattx]

Shove it in Excel, stick in a few markers, use find and replace.

If you need to export it one line at a time, I would either use a macro copying and pasting a row or AutoIT to do something similar.

[dhicks]

What i would like to do is export each line to a text file that is called 'Email Username & Password'

 

Learn to program, then you could fix problems like this easily. In your favourite scripting language (if you don't already have one, it's now Python) read each line of text, split it on the "," character, open a new file and write the details to it. Something like:

 

import os

infile = open("data.csv", "r")
lines = infile.readlines()
close(infile)
for line in lines:
   (email, password, name) = line.split(",")
   os.mkdir(name)
   outfile = open("name" + os.sep + "email username and password.txt", "w")
   outfile.write("Your new Student email address is " + email + " and your password is: " + password + "\n")
   outfile.write("You will be required to change you password the first time you log in.\n")
   outfile.write("Student email is now located at http://studentemail.domain.com")
   close(outfile)

 

--

David Hicks

[andy_b]

The dirty way to do it is with a spreadsheet and a batch file.

Import into Excel,assuming:

 

A1 is Forename

B1 is Surname

C1 is email address

D1 is password

 

Insert the following formula into E1 and copy down as appropriate.

=""".\"&A1&" "&B1&"\Email Address & Password.txt"""

 

Insert the following formula into F1 and copy down as appropriate.

="md """&A1&" "&B1&"""& echo You new Student email address is "&C1&" and your password is "&D1&". You will be required to change you password the first time you login.>"&E1&"&"&"echo.>>"&E1& "echo.>>"&E1&" & echo Student email is now located at http://studentemail.domain.com >>"&E1

 

copy and paste column F into a text file and save as a batch file in the appropriate folder and run it.

 

Test first with a small sample rather than accidentally making 100's of files in the wrong place

[DanW]

i get this message when i try and run the py script

 

cd '/Users/Dan/Desktop/Student Email/' && '/usr/bin/pythonw'  '/Users/Dan/Desktop/Student Email/import.py'  && echo Exit status: $? && exit 1
daniel-wrights-macbook:~ Dan$ cd '/Users/Dan/Desktop/Student Email/' && '/usr/bin/pythonw'  '/Users/Dan/Desktop/Student Email/import.py'  && echo Exit status: $? && exit 1
Traceback (most recent call last):
 File "/Users/Dan/Desktop/Student Email/import.py", line 5, in 
   close(infile)
NameError: name 'close' is not defined
daniel-wrights-macbook:Student Email Dan$ 

[DanW]

The dirty way to do it is with a spreadsheet and a batch file.

Import into Excel,assuming:

 

A1 is Forename

B1 is Surname

C1 is email address

D1 is password

 

Insert the following formula into E1 and copy down as appropriate.

=""".\"&A1&" "&B1&"\Email Address & Password.txt"""

 

Insert the following formula into F1 and copy down as appropriate.

="md """&A1&" "&B1&"""& echo You new Student email address is "&C1&" and your password is "&D1&". You will be required to change you password the first time you login.>"&E1&"&"&"echo.>>"&E1& "echo.>>"&E1&" & echo Student email is now located at http://studentemail.domain.com >>"&E1

 

copy and paste column F into a text file and save as a batch file in the appropriate folder and run it.

 

Test first with a small sample rather than accidentally making 100's of files in the wrong place

 

Thanks for that but getting input line too long!

[Chillibear]

Learn to program, then you could fix problems like this easily. In your favourite scripting language (if you don't already have one, it's now Python) read each line of text, split it on the "," character, open a new file and write the details to it. Something like:

 

Couldn't agree more. For this kind of job knowing a scripting language is a must. My own choice for this kind of job is Ruby, not that I have anything against Python I hasten to add! If you're still having problems with getting these files created this would be my take on it using Ruby:

 

#!/usr/bin/env ruby

# user settings
source_file           = '/Users/Dan/Desktop/Student Email/emails.csv'
output_base_directory = '/Users/Dan/Desktop/Student Email/'
output_file           = 'Email Username & Password.txt'

# set the following variable to 'true' if you want the script to remove any
# non alphanumeric characters from theuser's name before creating the output
# directory for that user
clean_up_name = false

# the block of text we will later save to
# a file, replacing some items, eg %Password%
# with actual values
text = '
Your new Student email address is %EmailAddress% and your password is %Password% you will be required to change you password the first time you login.

Student email is now located at http://studentemail.domain.com
'

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
# ensure the output directory has a trailing '/' on it
output_base_directory = "#{output_directory}/" unless output_base_directory[-1,1] == '/'

# ensure the output directory exists
unless File.exists?(output_base_directory) && File.directory?(output_base_directory)
puts "Output directory: '#{output_base_directory}' does not appear to exist"
exit
end

# ensure the source file  exists
unless File.exists?(source_file)
puts "Source file: '#{source_file}' does not appear to exist"
exit
end

# open the source file
File.open(source_file) do |f|
 
 # process each line
 f.each_with_index do |line, index| 
   
   # split the line by commas
   email, password, name = line.split(',')

   # trim any whitespace or carriage
   # returns from the variables
   email.strip!
   password.strip!
   name.strip!

   # set the name for the file we will create.  For safety
   # we will replace any odd characters in the user name
   # with underscores.  We will also need to create the
   # directory for it.
   output = clean_up_name ? output_base_directory + name.gsub(/[^a-zA-Z0-9]/, '_') + '/' : output_base_directory + name + '/'  
   Dir.mkdir(output) unless File.exists?(output)
   output = output + output_file 
  
   # take a copy of our text and replace the placeholders 
   # in the block of text with the real name, email, etc
   mytext = String.new(text) 
   mytext.gsub!('%EmailAddress%', email)
   mytext.gsub!('%Password%',     password)
   mytext.gsub!('%Name%',         name)  
   
   # write the new file
   File.open(output, 'w') { |o| o.write(mytext) }    
   
   # just some output to the console so we can see what
   # is happening
   if File.exists? output
     puts "Processed line: #{index} (#{name}) and saved file as: #{output}"
   else
     puts "Failed to process line: #{index} (#{name})."    
   end
   
 end
end
puts "All done"

 

Save that lot as a text file (if you're feeling neat and tidy with a '.rb' extension), eg email.rb. Edit the variables at the top of the file to point at your actual file and a suitable output directory. Then open up a terminal prompt and run the ruby script. Either make the '.rb' file executable (chmod 700 email.rb) or run it explicitly with the ruby interpreter like this: ruby email.rb.

 

Ruby comes bundled (as does Python) with the Mac (noticing you appear to be using one from error messages posted) so the script should run without a hitch.

 

I've tried to comment it so you can see what's happening.

Edited September 14, 2009 by Chillibear

[Chillibear]

I've added the above script as an attachment to this post (currently as a .txt file).

process-emails.txt

[DanW]

that works ... although it only seems to do the first line ..

[dhicks]

NameError: name 'close' is not defined

 

Last line is wrong, should be:

 

infile.close()

 

--

David Hicks

[DanW]

Last line is wrong, should be:

 

infile.close()

 

--

David Hicks

 

import os

infile = open("data.csv", "r")
lines = infile.readlines()
infile.close()
for line in lines:
   (email, password, name) = line.split(",")
   os.mkdir(name)
   outfile = open("name" + os.sep + "email username and password.txt", "w")
   outfile.write("Your new Student email address is " + email + " and your pas$
   outfile.write("You will be required to change you password the first time y$
   outfile.write("Student email is now located at http://studentmail.
   close(outfile)

 

like this?

Edited September 14, 2009 by DanW

[dhicks]

like this?

 

Yes - sorry, that last line, too:

 

import os

infile = open("data.csv", "r")
lines = infile.readlines()
infile.close()
for line in lines:
   (email, password, name) = line.split(",")
   os.mkdir(name)
   outfile = open("name" + os.sep + "email username and password.txt", "w")
   outfile.write("Your new Student email address is " + email + " and your pas$
   outfile.write("You will be required to change you password the first time y$
   outfile.write("Student email is now located at http://studentmail.
   outfile.close() 

 

--

David Hicks

[mac_shinobi]

instead of using a script do a mail merge using the csv as the data source and jobs a good un

 

simples tsk

[Chillibear]

that works ... although it only seems to do the first line ..

 

Arghhh, I expect each line has odd line endings. causing the 'each' that does each line to fail. Could ensure it's saved with UNIX line endings, otherwise you'd need to do a bit more effort on processing each 'line' of the file.

 

Sorry.

 

EDIT: Just checked and it works with either UNIX (linefeed) or Windows (carriage return and line feed) but NOT with classic Mac formatting, which was just a carriage return, so that's probably it.

Edited September 14, 2009 by Chillibear

[DanW]

Arghhh, I expect each line has odd line endings. causing the 'each' that does each line to fail. Could ensure it's saved with UNIX line endings, otherwise you'd need to do a bit more effort on processing each 'line' of the file.

 

Sorry.

 

EDIT: Just checked and it works with either UNIX (linefeed) or Windows (carriage return and line feed) but NOT with classic Mac formatting, which was just a carriage return, so that's probably it.

 

 

Big thanks ... all done now ....

 

 

And thanks to everyone else that helped me as well