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.
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!
CTO of Identity Automation, LP
Disclaimer: As with everything else at NetIQ Cool Solutions, this content is definitely not supported by NetIQ, so Customer Support will not be able to help you if it has any adverse effect on your environment. It just worked for at least one person, and perhaps it will be useful for you too. Be sure to test in a non-production environment.