If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Informix > convert a flat file to an unl

Reply
 
LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 01-30-10, 03:55
fastlane3000 fastlane3000 is offline
Registered User
 
Join Date: Jan 2010
Posts: 1
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
Reply With Quote
  #2 (permalink)  
Old 01-31-10, 19:47
rpaskudniak rpaskudniak is offline
Registered User
 
Join Date: Feb 2009
Location: Brooklyn, NY
Posts: 5
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
Reply With Quote
  #3 (permalink)  
Old 02-14-10, 06:03
Tyveleyn Tyveleyn is offline
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old 03-16-10, 18:56
frankcomputer frankcomputer is offline
Registered User
 
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.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On