Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unanswered: convert a flat file to an unl

    hello

    i have several 200MegaBytes-files and i need to change their format to unl , my purpose is to load the datas in an informix database.
    i work with informix v10 IDS on AIX 5.3
    the file is a 1-line with no particular delimiters (the space or blank doesnt mean the end of a record), i can give an example

    040624xxx 10000yyyyy 040625zzz 25000aaaaa 040627bbb 12000ddddd ....

    i have a table outtab
    create table outtab (field1 date , field2 char(3) , field3(decimal) , field4 char(5))

    i want first to make a temporary file outtab.tmp
    990624|xxx|10000|yyyyy
    990625|zzz|25050|aaaaa
    990627|bbb|12075|ddddd
    ........

    and after conversion outtab.unl (so i can make a load to outtab)

    06/24/1999|xxx|100.00|yyyyy
    06/25/1999|zzz|250.50|aaaaa
    06/27/1999|bbb|120.75|ddddd

    Thank's for helping me

  2. #2
    Join Date
    Feb 2009
    Location
    Brooklyn, NY
    Posts
    9

    Talking

    Fastlane,

    This is a job for a Perl or AWK script. I conjecture from your example that you want to split the first two fields from a line into their own line, then split each of the two fields into 2 more fields. Then proceed with the same scheme for the rest of the line, grabbing 2 raw fields at a time.

    How you got from 04 to 99, as in 040624 -> 990624, is a stretch. But from the 99 to 1999 makes some sense. Hence, the conversion from 990624 -> 06/24/1999 still makes sense. Similarly the second numeric field.

    With the exception of that mystery step, the algorithm is straightforward:
    Split the line by white-space.
    Grab the first two fields via shift.
    I forget the exact pattern matching for alpha but \d+ will match digits.
    Alternatively, some judicious use of index and substr functions might accomplish this.

    But the exact method is independent of Informix and UNL files.

    HTH a bit.

    -- Rasputin Paskudniak

  3. #3
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

    don't know if it's usefull in this particular case but here's a script I use on Linux to convert fixed recordsize ASCII files to unload files. I guess if you make it a ksh script it will work on AIX to.
    Code:
    #!/bin/bash
    
    if [ ! $# -ge 1 ]; then
    	echo "`basename $0`: missing file argument"
    	echo " Usage: `basename $0` FILE [[COLUMN] ...] [DELIMITER]"
    	exit 1
    fi
    
    tmp="${HOME}/.`basename $0`.tmp"
    [ -f "${tmp}" ] && rm $tmp
    touch $tmp
    
    dlm="|"
    cnt=0
    for j in $@
    do
    	let cnt=${cnt}+1
    	if [ $cnt -eq 1 ]; then
    		if [ "${j}" = "-" ]; then
    			file=$j
    		elif [ -f "${j}" ]; then
    			file=$j
    		else
    			echo "Error: file '`pwd`/${j}' not exists, exit program!"
    			exit 1
    		fi
    	else
    		chk=`echo $j | awk '{print ($0 !~ /[^0-9]/) ? 0 : 1}'`
    		if [ $chk -eq 1 ]; then
    			if [ $cnt -eq $# ]; then
    				dlm=`echo $j | \
    						awk '{print ($0 ~ /^[^0-9a-fA-F]$/) ? $0 : "\\\"}'` 
    				if [ "${dlm}" = "\\" ]; then
    					echo "Error: '${j}' is invalid delimiter, exit program!"
    					exit 1
    				fi
    			else
    				echo "Error: '${j}' is no column number, exit program!"
    				exit 1
    			fi
    		else
    			echo $j >> $tmp
    		fi
    	fi
    done
    
    awk 'BEGIN	{
    				FS = ""
    				split(ARGV[2], inp, "=")
    				while(getline a[++n] < inp[2] > 0) {}
    			}
    			{
    				str = ""
    				for(i = 1; i <= (l = length($0)); i++)
    				{
    					str = str sprintf("%s", substr($0, i, 1))
    					for(j in a)
    						if(a[j] == i)
    						{
    							sub(/ +$/, "", str)
    							str = str d
    						}
    					if(i == a[n])
    						break
    				}
    				for(j in a)
    					if(a[j] > l)
    					{
    						sub(/ +$/, "", str)
    						str = str d
    					}
    				print str
    			}' d=$dlm v=$tmp $file
    rm $tmp
    Use this script like:
    Code:
    $ mkunl sourcefile 6 10 15 21 27 31 36 42 48 52 57 63
    I would use this script to make an unload file first and then use awk (with the -F "|" option to redefine the fieldseperator) to further process the fieldvalues.

    Regards,
    Hans

  4. #4
    Join Date
    Mar 2010
    Posts
    5
    I use the ACE report writer in INFORMIX-SQL to read in ascii files, output report to "filename.unl", print var1,"|",date_var2 using "mm/dd/yyyy","|" ... etc.
    assuming DBDATE=MDY4/

    It sounds awkward to use ACE, but it provides me with powerful formatting capabilities for generating the .unl file.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •