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:
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:
In this example, your output would be:
...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]
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
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 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.