Tool

(View Disclaimer)

SYSTEM REQUIREMENTS:
You'll need to put the attached script file on a machine with PHP installed (tested with PHP 5 but should work with PHP 4). This script is used at the command prompt so you'll need the php executable in your path.

SOURCE DELIMITED TEXT FILE:
Your source file should be a delimited text file. You can change the delimiter character by tweaking the "$delimiter" variable in line 2 of the convert.php script (delimiter is a comma by default). You'll need to add a header row to your delimited text file that labels your columns. An example file would something like this:

first_name,last_name,employeeid
JOHN,DOE,12345
JANE,DOE,23456
ABER,CROMBIE,34567

TEMPLATE TRANSFORMATION FILE:
The template file is something you construct to define how each record in your source delimited file should be transformed. If you want to build an SQL statement with each record, your template file would be an SQL statement. If you want to build and LDIF file, your template would be an LDIF statement.

The trick is that we will use the column labels from our source file as tokens in our template file. In other words, our template will refer to the column names and the script will replace those tokens with the actual values from the source file.

Let's say we want to build an LDIF record to add all of the user records in our source file to our directory. You might build a template file as such:

dn: %employeeid%,ou=users,o=acme
changetype: add
objectclass: user
objectclass: top
givenName: %first_name%
sn: %last_name%
workforceid: %employeeid%

In this example, your output would be:

dn: 12345,ou=users,o=acme
changetype: add
objectclass: user
objectclass: top
givenName: JOHN
sn: DOE
workforceid: 12345

dn: 23456,ou=users,o=acme
changetype: add
objectclass: user
objectclass: top
givenName: JANE
sn: DOE
workforceid: 23456

...and so on...

However, if you want to build an SQL insert statement, you might build a template like this:

INSERT INTO myTable (emp_num,fname,lname) VALUES (%employeeid%,%first_name%,%last_name%)

In this example, your output would look like this:

INSERT INTO myTable (emp_num,fname,lname) VALUES (12345,JOHN,DOE)
INSERT INTO myTable (emp_num,fname,lname) VALUES (23456,JANE,DOE)
...and so on...

Once you build your template, simply save that file in the directory with the convert.php script and your source delimited file.

COMMAND-LINE SYNTAX:
To perform the conversion, simply use the following syntax:

php convert.php [source_file] [target_file]

e.g.
php convert.php mytextfile.txt mytemplate.tmpl

NOTE: Your php executable might be php5.

When you run this command, you will see the results of the conversion on the screen (stdout). This is good for quick tests to validate your template. To create your actual output file, simply pipe the output to a file:

php convert.php mytextfile.txt mytemplate.tmpl > myldifoutput.ldif

or 

php convert.php mytextfile.txt mytemplate.tmpl > mysqloutput.sql

One thing this conversion tool doesn't do is transform any of the data itself. You can basically use the data in the native format only. You can concatenate values but you can't get substrings, convert case, etc. Of course, if you know PHP, you can certainly inject these types of transformations and string manipulations as you so desire.

I'd love to get feedback on this tool. I can tell you it has saved me days of work on certain projects. Any enhancements (or re-writes in other scripting languages) are welcome!

Troy Moreland
CTO of Identity Automation, LP
troy.moreland@idauto.net

Disclaimer: As with everything else at Cool Solutions, this content is definitely not supported by Novell (so don't even think of calling Support if you try something and it blows up).

It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test, test, test before you do anything drastic with it.