Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

generating a create ddl from a csv file using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting generating a create ddl from a csv file using awk
# 1  
Old 10-21-2009
generating a create ddl from a csv file using awk
Hello,
I would greatly appreciate some help on the this

I have comma delimited file as follows:

csv file
--------
Code:
TEST1,fld1,VARCHAR2,3,,,
TEST1,fld2,DATE,,,,
TEST1,fld2,VARCHAR2,51,,,
TEST1,fld4,VARCHAR2,2,,,
TEST1,fld5,NUMBER,4,0,,
TEST1,fld6,VARCHAR2,1,,,
TEST1,fld7,TIMESTAMP,,,NOT NULL,
TEST1,fld8,VARCHAR2,16,,NOT NULL,1
TEST2,COLUMN1,VARCHAR2,100,,,
TEST2,COLUMN2,VARCHAR2,16,,NOT NULL,1
TEST2,COLUMN3,DATE,,,,
TEST2,COLUMN4,VARCHAR2,30,,,
TEST2,COLUMN5,VARCHAR2,30,,,
TEST2,COLUMN6,NUMBER,17,2,,
TEST2,COLUMN7,VARCHAR2,15,,NOT NULL,2
TEST3,COLUMN1,TIMESTAMP,,,NOT NULL,1

how can I get the following output below using AWK

ddl output using awk
-------------------
Code:
CREATE TABLE TEST1 (
fld1 VARCHAR2 (3),
fld2 DATE ,
fld3 VARCHAR2 (51),
fld4 VARCHAR2 (2),
fld5 NUMBER (4,0),
fld6 VARCHAR2 (1),
fld7 TIMESTAMP ,
fld8 VARCHAR2 (16)
);
CREATE TABLE TEST2(
COLUMN1 VARCHAR2 (100),
COLUMN2 VARCHAR2 (16),
COLUMN3 DATE ,
COLUMN4 VARCHAR2 (30),
COLUMN5 VARCHAR2 (30),
COLUMN6 NUMBER (17,2),
COLUMN7 VARCHAR2 (15)
);
CREATE TABLE TEST3(
COLUMN1 TIMESTAMP
);

Last edited by vgersh99; 10-21-2009 at 02:10 PM.. Reason: code tags, please!
# 2  
Old 10-21-2009
To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags [code] and [/code] by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums
# 3  
Old 10-21-2009
Code:
awk -F, '{if(!f||f!=$1){if(NR!=1)print z;f=$1;printf "CREATE TABLE %s (\n%s %s %s",$1,$2,$3,(($4&&$5)?x$4FS$5y:(($4)?x$4y:w))}else{printf ",\n%s %s %s",$2,$3,(($4&&$5!=w)?x$4FS$5y:(($4)?x$4y:w))}}END{print z}' w= x=\( y=\) z="\n);" file

Last edited by danmero; 10-21-2009 at 04:16 PM.. Reason: Try to stretch
# 4  
Old 10-21-2009
wow ! that works just like butter !
do you mind explaining me ?
# 5  
Old 10-21-2009
That's too much to explain but if you want to know why is working I'll suggest to read Gawk: Effective AWK Programming - GNU Project - Free Software Foundation (FSF)
You have the problem and the working solution and what's left is to find how and why Smilie
# 6  
Old 10-23-2009
Code:
my %tables;
while(<DATA>){
 chomp;
 my @tmp=split(",",$_);
 if ($tmp[2] eq "VARCHAR2"){
    $tables{$tmp[0]}->{$tmp[1]}->{val}=$tmp[2]." (".$tmp[3].")";
    $tables{$tmp[0]}->{$tmp[1]}->{seq}=$.;
 }
 elsif($tmp[2] eq "NUMBER"){
    $tables{$tmp[0]}->{$tmp[1]}->{val}=$tmp[2]." (".$tmp[3].",".$tmp[4].")";
    $tables{$tmp[0]}->{$tmp[1]}->{seq}=$.;
 }
 else{
    $tables{$tmp[0]}->{$tmp[1]}->{val}=$tmp[2];
    $tables{$tmp[0]}->{$tmp[1]}->{seq}=$.;
 }
}
for my $key (keys %tables){
  print "CREATE TABLE ",$key,"(\n";
  for my $k (sort {$tables{$key}->{$a}->{seq} <=> $tables{$key}->{$b}->{seq}} keys %{$tables{$key}}){
    print $k," ",$tables{$key}->{$k}->{val},",\n";
  }
  print ");\n\n";
}
__DATA__
TEST1,fld1,VARCHAR2,3,,,
TEST1,fld2,DATE,,,,
TEST1,fld3,VARCHAR2,51,,,
TEST1,fld4,VARCHAR2,2,,,
TEST1,fld5,NUMBER,4,0,,
TEST1,fld6,VARCHAR2,1,,,
TEST1,fld7,TIMESTAMP,,,NOT NULL,
TEST1,fld8,VARCHAR2,16,,NOT NULL,1
TEST2,COLUMN1,VARCHAR2,100,,,
TEST2,COLUMN2,VARCHAR2,16,,NOT NULL,1
TEST2,COLUMN3,DATE,,,,
TEST2,COLUMN4,VARCHAR2,30,,,
TEST2,COLUMN5,VARCHAR2,30,,,
TEST2,COLUMN6,NUMBER,17,2,,
TEST2,COLUMN7,VARCHAR2,15,,NOT NULL,2
TEST3,COLUMN1,TIMESTAMP,,,NOT NULL,1

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk and sed script to create one output CSV file

Hi All , I would require your help to generate one output file after post processing of one CSV file as stated below This file is just a small cut from a big file . Big file is having 20000 lines PATTERN,pat0,pat1,pat2,pat3,pat4,pat5,pat6,pat7,pat8,pat9... (2 Replies)
Discussion started by: kshitij
2 Replies

2. Shell Programming and Scripting

Is there a way to handle commas inside the data when generating a csv file from shell script?

I am extracting data via sql query and some of the data has commas. Output File must be csv and I cannot update the data in the db (as it is used by other application). Example table FavoriteThings Person VARCHAR2(25), Favorite VARCHAR2(100) Sample Data Greta rain drop on... (12 Replies)
Discussion started by: patk625
12 Replies

3. Shell Programming and Scripting

Create csv from text file

Gents, I am trying to create a csv file using the file attached. I have a problem to get all information required because the rows are not continues. Here is my code till now. awk ' /"ffid"/{if(s){print s;s=$NF}else{s=$NF}} /"LineNumber"/{s=s $NF} /"PointNumber"/{s=s $NF}... (4 Replies)
Discussion started by: jiam912
4 Replies

4. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

5. UNIX for Dummies Questions & Answers

Generating a CSV file from a text file

Hi Guys, I have a simple request. I have a file in w3c format. Each file has 2 header lines. Rest of the lines are 16 columns each. They are separated by Tab. I need to discard the first 2 lines and then write each column of the txt file into a seperate column of CSV. I tried the command below... (1 Reply)
Discussion started by: tinkugadu
1 Replies

6. Shell Programming and Scripting

Generating CSV from Column data

Hi List, I have a chunk of data like so: User Account Control: User Account Control: User Account Control: User Account Control: Disabled User Account Control: User Account Control: User Account Control: Disabled User Account Control: User Account Control: ... (3 Replies)
Discussion started by: landossa
3 Replies

7. Shell Programming and Scripting

Need help. How to create csv file?

Hi I'm a beginner and I have some problem. I have multiple files in the same directory which has one column but rows following the format. File: directory/Disk.txt Content: a b c d e File: directory/Memory.txt a b c d e File: directory/CPU.txt (3 Replies)
Discussion started by: thenuie
3 Replies

8. Shell Programming and Scripting

Script to create a CSV file

I created a script that will go out and so a "/sbin/chkconfig --list | egrep XXX" against a server list that would create an output file like the following example: ---------------------------------------------------------------------------------- SERVER1 RC_Script_1 0:off 1:off 2:off... (4 Replies)
Discussion started by: asnatlas
4 Replies

9. UNIX for Dummies Questions & Answers

How to create a .csv file from 2 different .txt files?

Hi, I need to create a .csv file from information that i have in two different tab delimited .txt file. I just want to select some of the columns of each .txt file and paste them into a .cvs file. My files look like: File 1 transcript_id Seq. Description Seq. Length ... (2 Replies)
Discussion started by: alisrpp
2 Replies

10. Shell Programming and Scripting

Command to create and update csv file

Hi, I need to create a csv file to store oracle query output. This report need to be created on hourly basis. The csv file report format as "Report_22_Sep_09_13IST.csv". I have the oracle query. Now i need to create and move the oracle query output to the report row by row starting from 3rd row.... (6 Replies)
Discussion started by: Sekar1
6 Replies
Login or Register to Ask a Question