Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Generating Dynamic Scripts

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Generating Dynamic Scripts
# 1  
Old 07-09-2013
Generating Dynamic Scripts
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.
Code:
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

Moderator's Comments:
Mod Comment Dont forget to use code tags next time...
Last edited by vbe; 07-10-2013 at 05:13 AM..
# 2  
Old 07-09-2013
Quote:
Originally Posted by mora
Hi,

...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)...
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?
Quote:
...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) )
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  
Old 07-10-2013
Sorry for the confusion, we are receving flat files in the below format
Code:
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
Moderator's Comments:
Mod Comment Please use code tags for your code and data
Last edited by vbe; 07-10-2013 at 05:13 AM..
# 4  
Old 07-10-2013
Quote:
Originally Posted by mora
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
You could have that all in one if you could provide some details. The maximum number of delimiters (in your example "|") would be:

Code:
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):

Code:
#! /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
This User Gave Thanks to bakunin For This Post:
mora
# 5  
Old 07-10-2013
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
Code:
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  
Old 07-11-2013
Quote:
Originally Posted by mora
Hi Bakunin,

... ... ...

I have tried to execute the script provided by you the scripting is failing at the below line
Code:
typeset iNumFields=(( ${#chBuffer}+1 ))

with the error word unexpected (expecting ")")

... ... ...

Thank you , Mora
It looks like one character was dropped. Try:
Code:
typeset iNumFields=$(( ${#chBuffer}+1 ))

This User Gave Thanks to Don Cragun For This Post:
mora
# 7  
Old 07-11-2013
Quote:
Originally Posted by mora
Code:
typeset iNumFields=(( ${#chBuffer}+1 ))

with the error word unexpected (expecting ")")
As DonCragun rightfully stated this was a typo on my part, sorry for that.

Quote:
Originally Posted by mora
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.
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:

Code:
#! /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
Last edited by bakunin; 07-14-2013 at 05:13 AM..
This User Gave Thanks to bakunin For This Post:
mora
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Generating dynamic variables

Hi , i am unable to generate dynamic variables can any one please help me on the below issue j=1 {record_count_"$j"}=`db2 -xselect substr\(job_name,24\) rec_count from $libname.audit_table_nrt where job_name like \'DATAMART_DEL_RUN%\' and STS_FLAG=\'E\' and seq_no=$i` echo " record... (3 Replies)
Discussion started by: bhaskar v
3 Replies

2. Shell Programming and Scripting

Help with generating a script

I am a biologist who is new to linux and am having difficulty generating a script to do what I want it to do! I have tried basic grep commands, but even that does not give me back the data I want. I have many files that are all currently in .xslx and I'm not sure if they need to be .csv or .txt... (16 Replies)
Discussion started by: kellywilliams
16 Replies

3. Shell Programming and Scripting

KSH - How to call different scripts from master scripts based on a column in an Oracle table

Dear Members, I have a table REQUESTS in Oracle which has an attribute REQUEST_ACTION. The entries in REQUEST_ACTION are like, ME, MD, ND, NE etc. I would like to create a script which will will call other scripts based on the request action. Can we directly read from the REQUEST_ACTION... (2 Replies)
Discussion started by: Yoodit
2 Replies

4. Shell Programming and Scripting

Scripts - Dynamic text

Hi, I have a file in which I have to replace the text with system date. The text needs to be changed on daily basis automatically. Assume, in "test.txt", I have below lines: LOAD FILE MYFILE 'c:/test/test_2010_09_24.txt ----- ----- In the above, MYFILE value changes every day, the... (1 Reply)
Discussion started by: psnmak
1 Replies

5. UNIX for Advanced & Expert Users

Sql dynamic table / dynamic inserts

I have a file that reads File (X.txt) Contents of record 1: rdrDESTINATION_ADDRESS (String) "91 971502573813" rdrDESTINATION_IMSI (String) "000000000000000" rdrORIGINATING_ADDRESS (String) "d0 movies" rdrORIGINATING_IMSI (String) "000000000000000" rdrTRAFFIC_EVENT_TIME... (0 Replies)
Discussion started by: magedfawzy
0 Replies

6. Shell Programming and Scripting

Changing the Bash Scripts to Bourne Scripts:URGENT

Hi, I have to write a program to compute the checksums of files ./script.sh I wrote the program using bash and it took me forever since I am a beginner but it works very well. I'm getting so close to the deadline and I realised today that actually I have to use normal Bourne shell... (3 Replies)
Discussion started by: pgarg1989
3 Replies

7. Shell Programming and Scripting

Generating Combinations

Hi, I need to generate all combinations upto n-1 level, if the input file looks like say, A B C D . . .... I need to generate all combinations such that first value remains constant and the remaning are combined with all possible ways. Output A AB AC AD ABC (1 Reply)
Discussion started by: zorg4u
1 Replies

8. Shell Programming and Scripting

Bash Scripts - File generating

Forgive the daft requests - I'm still a learner :D I need a script so that I can test another script (I'm confused already) The script I am looking for should generate a new file in the same directory (called newfile1 or what ever) and also generate text within the new file (Hello world? What... (1 Reply)
Discussion started by: JayC89
1 Replies

9. UNIX for Dummies Questions & Answers

Generating a CDR file using PHP scripts.

Hi, I need to do croning in PHP to generate a CDR (Call Details Record) file daily from the mysql database. The CDR file has to be named in a certain sequence such as xx00000xxx200604080850.cdr. A new file is written every day. The generated CDR file is then ftp over to a server. I am... (0 Replies)
Discussion started by: kurl
0 Replies

10. Programming

generating timer

I'm trying generate an interrupt every 1 seconds using itimer and My clock is not running. This is what i did : printf("about to sleep for 1 second \n"); signal(SIGALRM, wakeup); //myTimer.it_interval.tv_sec=0; //myTimer.it_interval.tv_usec =0; ... (5 Replies)
Discussion started by: Confuse
5 Replies
Login or Register to Ask a Question