+ Post New Thread
Results 1 to 15 of 15
Scripts Thread, CSV to individual text files in Coding and Web Development; I have a CSV file with the following headings EmailAddress Password Name and 500 records .. containing all the students ...
  1. #1
    DanW's Avatar
    Join Date
    Oct 2007
    Posts
    246
    Thank Post
    35
    Thanked 12 Times in 12 Posts
    Rep Power
    18

    CSV to individual text files

    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

  2. #2

    mattx's Avatar
    Join Date
    Jan 2007
    Posts
    9,240
    Thank Post
    1,058
    Thanked 1,068 Times in 625 Posts
    Rep Power
    740
    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.

  3. #3

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,624
    Thank Post
    1,240
    Thanked 778 Times in 675 Posts
    Rep Power
    235
    Quote Originally Posted by DanW View Post
    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:

    Code:
    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

  4. Thanks to dhicks from:

    DanW (12th September 2009)

  5. #4

    Join Date
    Apr 2007
    Location
    Birmingham
    Posts
    142
    Thank Post
    6
    Thanked 45 Times in 39 Posts
    Rep Power
    22
    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.
    Code:
    =""".\"&A1&" "&B1&"\Email Address & Password.txt"""
    Insert the following formula into F1 and copy down as appropriate.
    Code:
    ="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

  6. Thanks to andy_b from:

    DanW (13th September 2009)

  7. #5
    DanW's Avatar
    Join Date
    Oct 2007
    Posts
    246
    Thank Post
    35
    Thanked 12 Times in 12 Posts
    Rep Power
    18
    i get this message when i try and run the py script

    Code:
    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 <module>
        close(infile)
    NameError: name 'close' is not defined
    daniel-wrights-macbook:Student Email Dan$

  8. #6
    DanW's Avatar
    Join Date
    Oct 2007
    Posts
    246
    Thank Post
    35
    Thanked 12 Times in 12 Posts
    Rep Power
    18
    Quote Originally Posted by andy_b View Post
    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.
    Code:
    =""".\"&A1&" "&B1&"\Email Address & Password.txt"""
    Insert the following formula into F1 and copy down as appropriate.
    Code:
    ="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!

  9. #7

    Join Date
    Feb 2009
    Posts
    95
    Thank Post
    3
    Thanked 33 Times in 32 Posts
    Rep Power
    17
    Quote Originally Posted by dhicks View Post
    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:

    Code:
    #!/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.
    Last edited by Chillibear; 14th September 2009 at 01:41 PM.

  10. Thanks to Chillibear from:

    DanW (14th September 2009)

  11. #8

    Join Date
    Feb 2009
    Posts
    95
    Thank Post
    3
    Thanked 33 Times in 32 Posts
    Rep Power
    17

    above script as attachement

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

  12. Thanks to Chillibear from:

    DanW (14th September 2009)

  13. #9
    DanW's Avatar
    Join Date
    Oct 2007
    Posts
    246
    Thank Post
    35
    Thanked 12 Times in 12 Posts
    Rep Power
    18
    that works ... although it only seems to do the first line ..

  14. #10

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,624
    Thank Post
    1,240
    Thanked 778 Times in 675 Posts
    Rep Power
    235
    Quote Originally Posted by DanW View Post
    NameError: name 'close' is not defined
    Last line is wrong, should be:

    infile.close()

    --
    David Hicks

  15. Thanks to dhicks from:

    DanW (15th September 2009)

  16. #11
    DanW's Avatar
    Join Date
    Oct 2007
    Posts
    246
    Thank Post
    35
    Thanked 12 Times in 12 Posts
    Rep Power
    18
    Quote Originally Posted by dhicks View Post
    Last line is wrong, should be:

    infile.close()

    --
    David Hicks
    Code:
    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?
    Last edited by DanW; 14th September 2009 at 03:15 PM.

  17. #12

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,624
    Thank Post
    1,240
    Thanked 778 Times in 675 Posts
    Rep Power
    235
    Quote Originally Posted by DanW View Post
    like this?
    Yes - sorry, that last line, too:

    Code:
    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

  18. Thanks to dhicks from:

    DanW (15th September 2009)

  19. #13

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,763
    Thank Post
    3,273
    Thanked 1,053 Times in 974 Posts
    Rep Power
    365
    instead of using a script do a mail merge using the csv as the data source and jobs a good un

    simples tsk

  20. Thanks to mac_shinobi from:

    DanW (15th September 2009)

  21. #14

    Join Date
    Feb 2009
    Posts
    95
    Thank Post
    3
    Thanked 33 Times in 32 Posts
    Rep Power
    17
    Quote Originally Posted by DanW View Post
    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.
    Last edited by Chillibear; 14th September 2009 at 07:04 PM.

  22. Thanks to Chillibear from:

    DanW (15th September 2009)

  23. #15
    DanW's Avatar
    Join Date
    Oct 2007
    Posts
    246
    Thank Post
    35
    Thanked 12 Times in 12 Posts
    Rep Power
    18
    Quote Originally Posted by Chillibear View Post
    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

SHARE:
+ Post New Thread

Similar Threads

  1. create import csv
    By KWestos in forum Office Software
    Replies: 1
    Last Post: 2nd September 2009, 08:27 PM
  2. Enthusiastic individual looking for first IT role
    By Cooper_Trooper in forum Educational IT Jobs
    Replies: 5
    Last Post: 1st June 2009, 08:20 PM
  3. [MS Office - 2003] Split Word doc into individual page files
    By sdc in forum Office Software
    Replies: 0
    Last Post: 13th January 2009, 01:57 PM
  4. merging 2 text files
    By RabbieBurns in forum How do you do....it?
    Replies: 8
    Last Post: 29th June 2008, 04:45 PM
  5. Locate and edit text files
    By _Bob_ in forum Scripts
    Replies: 14
    Last Post: 7th June 2006, 02:11 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •