Generating Dynamic Scripts

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Hi,

Please give me an idea on how to achieve the below using a unix script.

From our source team we are getting files with in-proper delimiters because of which our data load is failing to avoid this we want to generate dynamic scripts as below.

Read the no of delimiters(which is dynamic in each file) and generate a file in /tmp/ with the below structure, so that we will call the generated file and create the object in database.

if the file has 3 delimiters than the structure should be as below.

create table REC_PRD.File_Name
(column_1 char(30), 
column_2 char(30),
column_3 char(30) )

Please help me to provide a logic on how can I acheive the above.

Thanks you, Mora

2

First off, you say that you are getting files with some kind of 'wrong delimiters'. Can you post an example along with the exact corrections that should be made to that specific file? Also, about these 'wrong delimiters'... are they always the same? (is it always a blank space, or a hyphen, etc., or are there more than one kind of 'wrong delimiters' in the same file?

So depending on the number of delimiters you want to create a file with a query to your database, correct?

Please provide these details in order for us to help you. Thanks.

3

Sorry for the confusion, we are receving flat files in the below format

data1|xyxz|123
data1|xyxz|123|abc
data1|xyxz|123|abc|xyz

we need to count the maximum number of delimiters in the above case 4 delimiters and generate the dynamic table structure with 5 columns.

when I pass the filename to the script it will create a table structure in tmp directory from here I will call the created script and pass the db details to create the table in database and load the source file

Thanks you, Mora

4

You could have that all in one if you could provide some details. The maximum number of delimiters (in your example "|") would be:

sed 's/[^|]//g' /path/to/input.file | sort | tail -n 1

This line filters out the line with the highest number of delimiters in it. Count them, add one and you have the number of fields in the line with the highest number of fields. Hence (as you didn't say which shell you are using i assume Kornshell93):

#! /bin/ksh93
typeset chBuffer="$( sed 's/[^|]//g' /path/to/input.file |\
                     sort |\
                     tail -n 1 \
                    )"
typeset iNumFields=(( ${#chBuffer}+1 ))
typeset iCounter=1
exec 3>/path/to/output.file                         # designate output file

print -u3  - "create table REC_PRD.File_Name"
print -nu3 - "( "

while [ $iCounter -lt $iNumFields ] ; do
     print -u3 - "column_${iCounter} char(30),"
     (( iCounter += 1 ))
done
print -u3 - "column_${iCounter} char(30) )"

exec 3>&-                                           # close output file

exit 0

Still, as you said you want to load the table after this from the file this would be possible in one step by extending above script somehow so that it not only generates the structure but also the load-statement and executes this afterwards. You will have to provide some details, like "how should the table be named", etc.. for this.

I hope this helps.

bakunin

5

Hi Bakunin,

Thanks for the help. I am new to unix so I was not sure if all can be done in one script. what I thought was if a table structure is generated then using some other script I can call the create table script and then using one more script yo load the data in the table, if all can be done in one script that will be great.

I have tried to execute the script provided by you the scripting is failing at the below line

typeset iNumFields=(( ${#chBuffer}+1 ))

with the error word unexpected (expecting ")")

I am connecting to Teradata database using BTEQ ,how can I make the script to generate the create table structure and then loading to the table in the same script. As we have more files I am planning to have the tablename as first 20 charcters from the filename.

Please help me with the logic.

Thanks for your time.

Thank you , Mora

6

It looks like one character was dropped. Try:

typeset iNumFields=$(( ${#chBuffer}+1 ))
7

As DonCragun rightfully stated this was a typo on my part, sorry for that.

I have to admit i do not know Teradata RDBMS at all, i am just a system administrator with a (at best rudimentary) knowledge of SQL. Provide some sample and explain how you would get from the sample to the result you want to have executed and i can write a script providing this very logic.

For instance: you say you want the tablename be the first 20 characters from the filename. This is some logic i can write a script of, in fact by modifying the one i already gave you. Notice the difference between the first and second version. Call this script with "./script.sh <filename>" where "<filename>" is the file you want to use:

#! /bin/ksh93
typeset    pInFile="$1"
typeset    chBuffer=""
typeset -i iNumFields=0
typeset -i iCounter=1
typeset    chTableName=""

if [ -r "$pInFile" ] ; then
     chTableName="${pInFile:1:20}"                  # create table name from filename
     chBuffer="$( sed 's/[^|]//g' "$pInFile" |\
                  sort |\
                  tail -n 1 \
                 )"
     iNumFields=$(( ${#chBuffer}+1 ))
else
     print -u2 "ERROR: file $pInFile not found or not readable"
     exit 1
fi

exec 3>/path/to/output.file                         # designate output file

print -u3  - "create table $chTableName"            # use tablename here
#print -u3  - "create table REC_PRD.${chTableName}" # alternatively like this, not sure
print -nu3 - "( "

while [ $iCounter -lt $iNumFields ] ; do
     print -u3 - "column_${iCounter} char(30),"
     (( iCounter += 1 ))
done
print -u3 - "column_${iCounter} char(30) )"

exec 3>&-                                           # close output file

exit 0

As soon as you explain exactly how to process the file content further (for instance by explaining which SQL-statements you use to load the file) i could show you how to incorporate too.

Programming (and scripting is like programming in any other language) is exactly stating the problem first and foremost and the rest is usually quite easy. Coding is simple once you found out what to code.

I hope this helps.

bakunin