Creating a LDIF file from CSV or Excel sheet

Question

My Requirement is like, we will ask requester to fill an excel sheet/csv where they will put the values in column uiddn(A1 in excel sheet) and nIds(B1 in excel sheet).

For Example: enter image description here

uiddn nIds
uid=uid12345,cn=abc,cn=def,dc=xyx 7_54321
uid=uid6789,cn=abc,cn=def,dc=xyx 5_9876

Once those details are filled, a bash script should take the values and create a ldif in the name 'removenid.ldif' and the ldif should content as shown below:

dn: uid=uid12345,cn=abc,cn=def,dc=xyx
changetype: modify
delete: nIds
nIds: 7_54321
-

dn: uid=uid6789,cn=abc,cn=def,dc=xyx
changetype: modify
delete: nIds
nIds: 5_9876
-

Along with it should create another ldif file in the name 'groupremoval.ldif' where the ldif file content should be:

dn: cn=ghk,cn=klm,cn=opu,dc=xyx
changetype: modify
delete: member
member: uid=uid12345,cn=abc,cn=def,dc=xyx---only this value it should take from the excel sheet

dn: cn=ghk,cn=klm,cn=opu,dc=xyx
changetype: modify
delete: member
member: uid=uid6789,cn=abc,cn=def,dc=xyx---only this value it should take from the excel sheet

Welcome to SO. You are expected to show what you have tried, and how it fails. Research is on you. Please take the [tour] and read [ask]. That being said, you need to read your CSV, line per line, extract each item into variables, and printf the new lines you want. `awk` is your friend here. — Nic3500, Feb 08 '23 at 13:39
@Satyajit – It looks like you don't have comma separated values – what is the separator? — Armali, Feb 08 '23 at 14:47
Please provide enough code so others can better understand or reproduce the problem. — Community, Feb 08 '23 at 15:28

Eric Marceau · Answer 1 · 2023-02-08T21:50:52.653

Step one is to save your table from Excel to a CSV.

I strongly recommend specifying a delimiter character (in this case vertical bar "|") because you never know when your input will deliberately have spaces in any of the fields. So, a space character is the worst choice.

You would need to create a macro to automatically save the *.CSV file with a constantly reused file name (i.e. always iddnx.csv).

Then, again using the macro, tell the OS to run the script with the required parameters.

This talks about how to create a macro.
This talks about how to run the macro.
This talks about how you can get the OS to run the command.

The following script will generate the two files indicated:

#!/bin/bash

DBG=0
input=""

while [ $# -gt 0 ]
do
    case $1 in
        --debug ) DBG=1 ; shift ;;
        --input ) input="${2}" ; shift ; shift ;;
        --strAlt ) strAlt="${2}" ; shift ; shift ;;
        * ) echo "\n Invalid option use on command line.  Only valid options: [ --debug | --input {inputfile} ]\n Bye!\n" ; exit 1 ;;
    esac
done

if [ -z "${input}" ]
then
    echo -e "\n DEMO RUN ..."
    input=iddnx.csv
    cat >"${input}" <<"EnDoFiNpUt"
uiddn|nIds
uid=uid12345,cn=abc,cn=def,dc=xyx|7_54321
uid=uid6789,cn=abc,cn=def,dc=xyx|5_9876
EnDoFiNpUt
    strAlt="cn=ghk,cn=klm,cn=opu"
fi

if [ -z "${strAlt}" ]
then
    echo -e "\n ERROR - missing option '--strAlt' requires string value to be specified!\n Bye!\n" ; exit 1
fi

output1="removenid.ldif"
output2="groupremoval.ldif"

awk -v dbg=${DBG} -v altStr="${strAlt}" -v f1="${output1}" -v f2="${output2}" 'BEGIN{
    ## Initial loading of the report reference index file into an array for tabulation
    split( "", dataload );
    indexR=0 ;
    if( dbg == 1 ){ printf("\n\t --- %s\n", "PHASE 1" ) | "cat >&2" ; } ;
}
{
    if( $0 != "" ){
        indexR++ ;
        split( $0, datline, "|" );
        if( dbg == 1 ){ printf("\n\t --- %s\n", $0 ) | "cat >&2" ; } ;

        dataload[indexR,1]=datline[1] ;
        if( dbg == 1 ){ printf("\t --- datline[1] = %s\n", datline[1] ) | "cat >&2" ; } ;

        dataload[indexR,2]=datline[2] ;
        if( dbg == 1 ){ printf("\t --- datline[2] = %s\n", datline[2] ) | "cat >&2" ; } ;

        if( indexR == 1 ){
            typeHdr=datline[2] ;
        } ;
    } ;
}
END{
    if( dbg == 1 ){ printf("\n\t --- %s\n", "PHASE 2" ) | "cat >&2" ; } ;
    for( i=2 ; i<=indexR ; i++ ){
        #dn: uid=uid12345,cn=abc,cn=def,dc=xyx
        #changetype: modify
        #delete: nIds
        #nIds: 7_54321
        #-
        printf("dn: %s\nchangetype: modify\ndelete: %s\n%s: %s\n-\n\n", dataload[i,1], typeHdr, typeHdr, dataload[i,2] ) >f1 ;
    } ;

    for( i=2 ; i<=indexR ; i++ ){
        split( dataload[i,1], tmp, "," ) ;
        #dn: cn=ghk,cn=klm,cn=opu,dc=xyx
        #changetype: modify
        #delete: member
        #member: uid=uid12345,cn=abc,cn=def,dc=xyx
        #
        printf("dn: %s,%s\nchangetype: modify\ndelete: member\nmember: %s\n\n", altStr, tmp[4], dataload[i,1] ) >f2 ;
    } ;
}' "${input}" 

echo -e "\nLDIF files generated:"
ls -l "${output1}" "${output2}"

echo -e "\nContents of '${output1}':\n"
cat "${output1}"

echo -e "\nContents of '${output2}':\n"
cat "${output2}"

Using the command line:

script.sh --input iddnx.csv --strAlt "cn=ggg,cn=kkk,cn=ppp"

You will get the following output:

LDIF files generated:
-rw-rw-r-- 1 ericthered ericthered 217 Feb  8 16:35 groupremoval.ldif
-rw-rw-r-- 1 ericthered ericthered 172 Feb  8 16:35 removenid.ldif

Contents of 'removenid.ldif':

dn: uid=uid12345,cn=abc,cn=def,dc=xyx
changetype: modify
delete: nIds
nIds: 7_54321
-

dn: uid=uid6789,cn=abc,cn=def,dc=xyx
changetype: modify
delete: nIds
nIds: 5_9876
-


Contents of 'groupremoval.ldif':

dn: cn=ggg,cn=kkk,cn=ppp,dc=xyx
changetype: modify
delete: member
member: uid=uid12345,cn=abc,cn=def,dc=xyx

dn: cn=ggg,cn=kkk,cn=ppp,dc=xyx
changetype: modify
delete: member
member: uid=uid6789,cn=abc,cn=def,dc=xyx

Creating a LDIF file from CSV or Excel sheet

1 Answers1