text to MySQL

[Charlotte]

Hi

I am a newbee to both php and MySQL.

I was wondering if I could get some assiatance on a matter:

I have a userdatabase in flatfile format. The user info is divided into many files:

First the main file where all the usernames are listed in one column. Then one separeate file for each user, named [username].dat containing their userinfo with encrypted password.

Is there a way I can import all these files (almost 300 of them) into a single MySQL table? If so, could someone please explain this to me in easy terms? Thanks.

Charlotte

[nicc777]

Hi - This is an interesting problem and I belief Perl can provide a better solution then PHP ( PHP experts - correct me if I'm wrong ). You see, the power of perl lies in it's ability to parse text files. The fact that you have two text files is not a problem either.

Let's first look at a simple sample:

File 1 : Names :

Name,Surname
Name1, Surname1
Name2, Surname2
Name3, Surname3

File 2 : Details :

username,email,password
username1,user1@aaa.com,qwertyuiopoiuytrewqwertyui opoiuy
username2,,qwertyuiopoiuytrewqwertyuiopoiuy
username3,user3@ccc.com,qwertyuiopoiuytrewqwertyui opoiuy

Let's call file 1 names.txt and file 2 details.txt and the first line of each file is the column heading.

There is one problem here. You must know how the password was created. MySQL has several ways of storing passwords. Encrypted passwords are either MD5 or SHA1 ( refer to the MySQL documentation: misc. functions ). From the documentation you will also see that MySQL encrypted passwords and Unix passwords are NOT the same, so if you have Unix encrypted passwords - forget it. The passwords could also be 3rd party generated, in which case we store the passwords as is. I will assume that this is the scenario.

Here goes:

     1	#!/usr/bin/perl
     2	
     3	# set the file path and name
     4	$file1 = "/home/f2821796/tmp/names.txt";
     5	$file2 = "/home/f2821796/tmp/details.txt";
     6	
     7	# initialise our arrays
     8	@lines_names = ();
     9	@lines_details = ();
    10	
    11	# get the data of the names
    12	open( NAMES, "< $file1" ) or die "Can't open $file1: $!\n";
    13	@lines_names = <NAMES>;
    14	
    15	# get the data of the details
    16	open( DETAILS, "< $file2" ) or die "Can't open $file1: $!\n";
    17	@lines_details = <DETAILS>;
    18	
    19	# get rid of the first line
    20	shift( @lines_names );
    21	shift( @lines_details );
    22	
    23	# loop and create the data lines
    24	use DBI;
    25	$dbh = DBI->connect( "dbi:mysql:test" , "username", "password" ) or die("DB ERROR : $!");
    26	
    27	$linecounter = 0;
    28	foreach $line ( @lines_names ) {
    29	
    30		chomp( $line );
    31		( $name, $surname ) = split( /,/, $line );
    32		
    33		$line2 = $lines_details[$linecounter];
    34		chomp( $line2 );
    35		( $username, $email, $password ) = split( /,/, $line2 );
    36		
    37		$linecounter++;
    38		
    39		$sql = "INSERT INTO usertable ( name, surname, username, email, password ) VALUES ( '$name', '$surname', '$username', '$email', '$password' )";
    40		$dbh->do( $sql );
    41	
    42	}
    43	
    44	exit;

The logic is simple:

Step 1 : Lines 3 to 17 : Load the contents of each file in a seperate array;

Step 2 : lines 19 to 21 : Get rid of the first line of garbage.

Step 3 : Lines 24 and 25 : Load the DB module and connect.

Step 4 : Lines 27 to 42 : Loop through each line at a time of each file and assign values to the variables. Combine all the variables into one SQL query, and execute the query.

The nice thing about the basics of this script is that it's easy to add more files if you want.

You could also do something similar with straight forward SQL, BUT you will also need to use each file in a seperate query. Have a look at this page: http://www.mysql.com/doc/en/mysqlimport.html

Hope that helps

[greenman]

Wow nicc777 (btw - check your private messages)! I'm not going to give an answer in as much detail, but for simple text parsing like this PHP has no problem either if you'd prefer to use that. You can read more about this in: the PHP manual and see a basic tutorial on the Free2code site

[nicc777]

I must admit - I'm not into PHP. I really enjoy Perl and I know that PHP can probably do a lot of things "easier" then it would be in Perl.

The important thing is that when you choose a tool, you learn all there is to know about that tool. I have played with Perl for about 6 years now, and sometimes I feel like I will never know everything - AND there's a new version of Perl on the horison !!

Anyway, where possible I will always give Perl examples. People can then make up there own mind as to what tool seems best for them. I will never "condem" anybody for prefering one tool over another.

Cheers