Using UD8/CSV

This guide is divided up into the following sections:

What is a Field Level Driver?

Uniface accesses databases using intelligent drivers. These are divided roughly into 2 types:

Record level drivers consist entirely of fixed length fields. A record may look like:

ARTHUR..................BARRETT..................MARCH.HARE...............

Field level drivers, by understanding each field as a separate object can represent the same information:

ARTHUR,BARRETT,MARCH HARE

In this sense the UD8 driver is a field level driver. It relies on Uniface’s ability to match records and maintain referential integrity. Internally it uses Physical Record Addresses (PRA's) to locate occurrences/records from the Uniface hitlist. A PRA of 20 corresponds to line number 20.

Use of the CSV/UD8 driver

The UD8 driver is designed to work with Microsoft Excel, and native loaders for SQL databases (like bcp for Sybase, and SQL*Loader for Oracle). There are many options that can configure it to a particular task. These are set using the USYS$UD8_PARAMS assignment file setting.

where clause

The where clause is not supported.

order by qualifier

This is supported by Uniface for both ascending and descending ordering.

u_where clause

Uniface handles the u_where clause in all circumstances.

selectdb instruction

Uniface handles the selectdb instruction in all circumstances.

sql instruction

The SQL proc instruction can be used for:

Please see the help page on SQL for more information.

[ENTITIES] Section of Assignment File

Entity Mapping

This section explains how you can map entity names (filenames) using the [ENTITIES] section in a suitable assignment file.

To rename the entity (or filename if using a driver like CSV/UD8): 

[ENTITIES]

colour.bridalwcolour.*

[DRIVER_SETTINGS] Section of Assignment File

Common UD8 Driver Settings

To use UD8 effectively you must set the appropriate driver settings for your CSV file format.

Eg: to read in a mixed CSV file, which contains some quoted fields, and dates in the format "ddmmyyyy": 

[DRIVER_SETTINGS]

USYS$UD8_PARAMS=heading enable,dateforce 3,quotestrings enable, translatechars binary,doublequote enable

If you had a model defined in Uniface named CSV, containing an entity EMPL:

DIV_CODE        N   C4

STATE_CODE      N   C4

LOCN_CODE       N   C4

EMPL_CODE       N   C10

ADDR_ID         N   C4

FIRST_NAME      S   C40

LAST_NAME       S   C40

MID_NAME        S   C40

ADDRESS1        S   C100

ADDRESS2        S   C100

ADDRESS3        S   C100

PLACE           S   C50

PIN             S   C10

PHONE           S   C24

PHONE1          S   C24

DESIGNATN       S   C100

CATEGORY        S   C6

LAST_UPDT       D   D

RESIGN_DT       D   D

Primary Key is: STATE_CODE,LOCN_CODE,EMPL_CODE,ADDR_ID,FIRST_NAME,LAST_NAME.

With the DRIVER_SETTINGS described above, and this entity definition, the following CSV file can be read:

DIV_CODE,STATE_CODE,LOCN_CODE,EMPL_CODE,ADDR_ID,FIRST_NAME,LAST_NAME,MID_NAME,ADDRESS1,ADDRESS2,ADDRESS3,PLACE,PIN,PHONE,PHONE1,DESIGNATN,CATEGORY,LAST_UPDT,RESIGN_DT

1,12,7,774,1,RANJAN,NANDA,,"MIG -130,BARHUT NAGAR",REWA ROAD,SATNA (MADHYA PRADESH),SATNA,,,,MEDICAL DETAILMAN,MR,22111993,7121992

1,25,7,325,1,ALOK,NANDI,KUMAR,C/O. ASHUTOSH NANDI,"P.O. RABINDRA SARANI,",RABINDRA NAGAR(SILIGURI),DARJEELING,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,14121992,

1,25,7,14,1,HEMANTA,NANDI,KUMAR,"50 A/2, RAJKRISHNA STREET",,DIST HOOGHLY,UTTARPARA,111111,,,DISTRICT MANAGER-INSTITUTIONAL BUSINESS,DM,8071999,

1,12,7,1342,1,HIMADRI,NANDI,SEKHAR,C/O BHAGWAN DAS SHARMA,OPP. SINDHU BHAWAN,NAI BASTI,KATNI (M.P.),111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,11031997,10031997

1,25,7,1974,1,HIMADRI,NANDI,SEKHAR,7/2 CHANDRANATH VIDYA-,RATNA LANE. BHATPARA,,"24, PARGANAS (NORTH)",111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,12022001,

1,12,7,1901,1,VIJAY,NANDWAL,KUMAR,"4/9, VIJAY NAGAR",A B ROAD,,INDORE,111111,011 1212121,011 1212121,PROFESSIONAL SALES REPRESENTATIVE,MR,28101998,26101998

1,25,7,117,1,SUNIL,NANDY,KUMAR,BEHIND DESHBANDHU BALIKA,VIDYAPITH.DESHBANDHU PARA,"SAROJANI BHAVANVILLA""SIB-",RAM SARANI SILIGURI,111111,,,REGIONAL SALES MANAGER,RSM,15052001,

1,30,7,1982,1,AJIT,NARANG,SINGH,"C-331, VIKAS PURI,",,,NEW DELHI,111111,011 1212121,011 1212121,EXECUTIVE-SCIENTIFIC SERVICES,EXECUTIVE,18031998,17031998

1,7,7,1466,1,SUSHIL,NARANG,KUMAR,C/O SH.RADHAY SHAM MEHTA,"BOARDING STREET,H.NO.1286","RORI BAZAR,NR.GOVT.GIRLS",SCHOOL SIRSA,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,16121997,

1,1,7,1285,1,K.V.S.,NARAYANA,,80-20-12  J.N. ROAD,KOKA BHASKARAMMA NAGAR,"EAST GODAVARI DISTRICT,",RAJAHMUNDRY,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,15052001,

1,1,7,577,1,K.V.S.,NARAYANA,,80-20-12 J.N. ROAD,KOKA BHASKARAMMA NAGAR,RAJAHMUNDRY,EAST GODAVARI DIST.,111111,,,MEDICAL DETAILMAN,MR,5121994,2031993

1,22,7,1895,1,R,NARAYANAN,SURYA,"ROOM NO. D5, SRI SAKHTI","MANSION. 8A, KRISHNAPPA",MUDALI STREET. CHEPAUK,CHENNAI,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,2111999,2111999

1,22,7,160,1,RAMAMOORTHY,NARAYANAN,,"USV LIMITED.""D"" BLOCK,","1ST FLOOR.9,SOUTH BAUG RD",ADWAVE TOWER.T.NAGAR,CHENNAI,111111,,,REGIONAL SALES MANAGER,RSM,25031999,16031999

1,22,7,508,1,S,NARAYANAN,,"41, UPSTAIRS",THATHAMUTHIAPPAN STREET,,CHENNAI,111111,011 1212121,,PROFESSIONAL SALES REPRESENTATIVE,MR,8071999,

1,3,7,1021,1,AMALENDRA,NATH,,INDIAN TRADING CO.,SHYMA PRASAD ROAD,"SILCHAR,ASSAM",SILCHAR,111111,011 1212121,,DISTRICT MANAGER,DM,16121999,

1,25,7,1938,1,SANJAY,NATH,,"60, K N SEN ROAD",P. O. KASBA,,CALCUTTA,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,27011998,1021998

1,18,7,1694,1,SAROJ,NAYAK,KUMAR,,,,CUTTACK,,,,PROFESSIONAL SALES REPRESENTATIVE,MR,12061997,15051997

1,4,7,892,1,U,NAYAK,K,"C/O M.K.S.ENTERPRISES,","1ST FLR,101-102 SAI APTS.","ORIENTAL BNK LANE,COURT-","ROAD, RANCHI.",111111,,,DISTRICT MANAGER,RSM,21072001,

1,4,7,1150,1,A.,NAZRE,M.,C/O.VIDYA MEDICAL AGENCY,MARWARI TOLA LANE,,BHAGALPUR,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,23121996,

1,12,7,2124,1,ANIL,NERKAR,,"B/301,3RD FLR.AASHIYANA",APTS.NAPIER.TOWN.NR.NAVIN,VIDYA BHAVAN SCHOOL,JABALPUR,111111,,,REGIONAL SALES MANAGER,RSM,14032000,13032000

1,12,7,2719,1,SUDEEP,NIGAM,KUMAR,ANKUR APT.FLAT NO. 306,SOUTH CIVIL LINE,NEAR IIND BRIDGE,JABALPUR,111111,011 1212121,,DISTRICT MANAGER,DM,14112000,

1,13,7,564,1,V.,NIMBALKAR,V,"PANCHAGANGA,BLDG.NO.17,","FLAT NO.405,RAM SOCIETY","NEAR PHULE NAGAR RTO,","YERAWADA, PUNE",111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,1091994,1091994

1,25,7,1776,1,SUSMITA,NONDY,,"H-3, CHITRA",NEELACHAL ABASAN,"98, RAJDANGA GOLD PARK",CALCUTTA,111111,011 1212121,011 1212121,DISTRICT MANAGER,DM,30071999,10071999

1,25,7,870,1,S,OKHADE,S,REGIONAL SALES MANAGER,C/O.P.D.DISTRIBUTORS,S.C.GOSWAMI ROAD PANBAZAR,GUWAHTI,111111,,,REGIONAL SALES MANAGER,RSM,19041997,18041997

1,11,7,2151,1,V,PADMAKUMAR,R,PADMA VILASAM.T.C.41/901,THIRUNARAYANAPURAM (NR.,PUTTENLOTTAH SIVA TEMPLE,MANACAUD TRIVANDRUM,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,17021999,

1,22,7,2029,1,C,PADMANABHAN,,,,,DHARMAPURI,,,011 1212121,PROFESSIONAL SALES REPRESENTATIVE,MR,8101998,30091998

1,10,7,30,1,DAYANAND,PAI,NAGESH,"14/21-15A,2ND.FLR.GIRIJA",SHANKAR COMP.80FT RD.,RADHAKRISHNA LAYOUT.KADI,RENAHALLY.BANGALORE,111111,,,SR.REGIONAL SALES MANAGER,RSM,5042001,

1,5,7,1064,1,SAMEER,PAI KANE,SHEKHAR,'OMKAR',BEHIND KTC BUS DEPOT,FATORDA,MADGAON-GOA,,,,PROFESSIONAL SALES REPRESENTATIVE,MR,18101995,

1,5,7,822,1,SRINIVAS,PAI RAIKAR,G,"FLAT NO.21,BLDG.1,","GALAXY APARTMENTS,",OPP.CINE VISHANT,MARGOA,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,13011994,10111993

1,6,7,522,1,P,PANCHAL,S,"11, NIYANT APARTMENTS",SATELLITE ROAD,NEAR PREMCHAND NAGAR,AHMEDABAD,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,17041997,17041997

1,18,7,382,1,P,PANDA,K,'SHRADHANJALI',"228, U.P.SCHOOL LANE",DEULA SAHI,CUTTACK,111111,,,REGIONAL SALES MANAGER,RSM,20071999,

1,6,7,1866,1,CHANDAN,PANDEY,SUBHASH,C/O FARASHRAM BHAVASAR,"BEGAMPURA, CHEVALI SHERI",HOUSE NO.H/4362.NR. MOTI,TALKIES. SURAT,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,11052000,3052000

1,6,7,2166,1,KANISHKA,PANDEY,MADHAVPRASAD,"5,AMBENAGAR SO.,NR.LAXMI-","NARAYAN BUS-STOP,SHRI RAM",#NAME?,AHMEDABAD,111111,011 1212121,011 1212121,PROFESSIONAL SALES REPRESENTATIVE,MR,19072001,

1,22,7,901,1,P.,PANDIKUMAR,,"RM.NO.A-25,SEETHALAKSHMI","MANSIONS, 166,167,NETHAJI","ROAD, MANJAKUPPAM",CUDDALORE,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,14041997,9041997

1,10,7,2093,1,SHAILESH,PANDIT,A,C/O P B KARNAD,SADGURU NIVAS. BEHIND,OLD SERVICE BUS STAND,UDIPI,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,8061998,

1,8,7,2129,1,ANIL,PANDITA,KUMAR,C/O RAMESH KUMAR DHIMAN,NARWANA BAZAR,VPO - YOL CAMP,DHARAMSHALA,111111,,011 1212121,PROFESSIONAL SALES REPRESENTATIVE,MR,23121998,22121998

1,9,7,2013,1,NAVIN,PANDITA,KUMAR,QUARTER NO. 593.BLOCK-G,MIGRANTS CAMP MISHRIWALA,P.O. DUMANA,JAMMU,,,,PROFESSIONAL SALES REPRESENTATIVE,MR,8022000,25012000

1,25,7,1753,1,JAYANTA,PANJA,KUMAR,C/O A.W.B.S.R.U. REST,HOUSE. RABINDRA AVENUE,BIBIGRAM. HATKHOLA,MALDA,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,8071997,

1,6,7,1045,1,MANISH,PAREKH,NAMDEV,"KHANDAKUVA,",MORABHAGAL,,SURAT,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,2061994,31051994

1,10,7,1792,1,VIJAY,PARGE,PUNDALIK,"H. NO.166, ADAKI CHAWL",GOKUL ROAD,HOSUR,HUBLI,111111,,,DISTRICT MANAGER,DM,19022001,31012001

1,6,7,1761,1,HEMAL,PARIKH,R,"38, AMRAPALI SOCIETY",KARELIBAUG,,BARODA,111111,,,DISTRICT MANAGER,DM,10081999,5081999

1,20,7,383,1,M,PARMAR,K,6K/18 HOUSING BOARD,BEHIND NAGAVIJI TEMPLE,,BIKANER,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,18101995,

1,6,7,1648,1,MAYANK,PARMAR,KANTILAL,SHETH POLE,NEAR MODI SANTH,,NADIAD,111111,011 1212121,,PROFESSIONAL SALES REPRESENTATIVE,MR,6051997,

1,10,7,777,1,S.,PARVATHIKAR,S.,C/O.S.S.KARKUN,B/H MUNCIPAL HOSPITAL,BADE KAMAN ROAD,BIJAPUR,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,13011998,

1,10,7,779,1,RAGHVENDRA,PARVATI,K.,"KRISHNA KUNJ,UPSTAIRS,","OPP. SHANDILYA ASHRAM,","NAGSHETTY KOPPA,",HUBLI,111111,,,DISTRICT MANAGER,DM,16072001,

1,1,7,1561,1,J,PASHA,A,"C/O.D SEKARAIAH.9/713,",BEH.RAFI BATTERY WORKS,N S C BOSE RD.SUBEDARPET,NELLORE,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,24082000,

1,6,7,1476,1,GAURANG,PATEL,M.,"14/B, ADHYAPAK NAGAR SOC.",WATER TANK ROAD,,BARODA,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,19041997,19041997

1,6,7,2167,1,HITESH,PATEL,MANUBHAI,"24/D, SUNIL SOCIETY",CANAL ROAD,NEAR BHADVATNAGAR,AHMEDABAD,111111,011 1212121,011 1212121,PROFESSIONAL SALES REPRESENTATIVE,MR,13012000,24121999

1,4,7,968,1,SANJEEV,PATEL,KUMAR,C/ORAINBOW COMMERCIAL CO.,EXIBITION ROAD,,PATNA,111111,,,DISTRICT MANAGER,DM,12071999,

1,6,7,1646,1,SETUL,PATEL,ATULKUMAR,"1/A, SOMNATH SOCIETY",NR.PUNJAB NATIONAL BANK,MANINAGAR (EAST),AHMEDABAD,111111,011 1212121,,PROFESSIONAL SALES REPRESENTATIVE,MR,30032001,23032001

1,6,7,1734,1,SUNIL,PATEL,RAMESHCHANDRA,D/88 BHAVNA PARK.B/H ANIL,PARK NO.3.B/H BRIGHT,"SCHOOL, VIP ROAD",BARODA,111111,,,DISTRICT MANAGER,DM,11021998,12061997

1,12,7,509,1,V,PATEL,S,HOUSE NO.7,GANGA NAGAR COLONY,"GARHA, MP",JABALPUR,111111,,,DISTRICT MANAGER,DM,15041997,14041997

1,6,7,74,1,VINOD,PATEL,KANTILAL,DISTRICT MANAGER,"7B, GOPAL BAUG,",BHAIRAVNATH RD.MANINAGAR,AHMEDABAD,111111,011 1212121,011 1212121,DISTRICT MANAGER.                           ,DM,15041997,14041997

1,13,7,847,1,UDAY,PATHADE,A,C/O.T.S. AGENCIES,"68, GURUNANAK MARKET",,NANDED,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,31121996,31121996

1,6,7,1057,1,ANURAG,PATHAK,PRAMATHESH,"""PRASAD"",SAYAJIGANJ",BHIMNATH MANDIR ROAD,,BARODA,111111,,,PROFESSIONAL SALES REPRESENTATIVE,MR,4091998,2091998

1,6,7,328,1,MANISH,PATHAK,ANANTNARAYAN,"12.""PANKAJ"",BHAVIN PARK","SOCIETY, NEAR MONA PARK,","JIVRAJ PARK,VEJALPUR ROAD",AHMEDABAD,111111,,,DISTRICT MANAGER (PROBATION),MR,17071995,12071995

1,27,7,2695,1,RANBIR,PATHANIA,SINGH,H. NO. 628,SECTOR 20-A,,CHANDIGARH,111111,011 1212121,,REGIONAL SALES MANAGER,RSM,28072000,

1,4,7,1954,1,SUKUMAR,PATI,,INTERNATIONAL SURGICAL &,MEDICAL AGENCY. SHOVA,COMPLEX. ROUZA ROAD,SASARAM,,,,PROFESSIONAL SALES REPRESENTATIVE,MR,6071999,3051999

1,13,7,2105,1,MADHUKANT,PATIL,S,"109, SACHIN A-5,",SAIKUNJ SOCIETY,"KRISHNANAGAR, CHINCHWAD",PUNE,111119,011 1212121,011 1212121,PROFESSIONAL SALES REPRESENTATIVE,MR,2021999,2021999

1,13,7,1143,1,SUDHIR,PATKI,YESHWANT,USV LIMITED,76 VIVEKANAND NAGAR,,NAGPUR,111111,,,ZONAL SALES MANAGER,RSM,25031997,18031997

NOTE: There must be an End Of Line character at the end of the last line (ie: CR)

Explanation of settings (a detailed list of all UD8 settings can be found here):

Setting used in exampleExplanation of setting used in example
heading enableignore the first line of the CSV file
dateforce 3interpret the date field as ddmmyyyy
quotestrings enablea field begins and ends with a double quote if it contains a comma
translatechars binaryneeded to enable doublequote
doublequote enabletranslate a double quote in a field that has been quoted to two consecutive double quotes

Examples

Copying data from one driver to another using the command line

This section explains how you can copy data using the UD8 driver and IDF command line options which you can set in the IDF command dialog box.

To copy from the default database to CSV/UD8:

/int=1000 /cpy def:colour.bridal ud8:

Using the driver to dynamically assign a filename

You can use the UD8 driver to read in a file using any filenameby using the following technique:

In this example EXISTING should be replaced with the name of your entity.

Using the driver to copy a file

You can use the UD8 driver to read in a file using one name, and write it out using another by using the following technique:

SeeWriting Report files with UD8/CSV for more examples.

In this example EXISTING should be replaced with the name of your entity.

Using the UD8 driver to wrap an ORACLE driver

You can use the UD8 driver to read in an oracle table that has a different name to the name of the entity painted on the form using the following technique:

SeeDynamically assigning physical tablenames in Oracle for more examples.

In this example the entity UFM reads in the table UFORM.

This same technique can be used to copy data from a CSV file to an ORACLE table (it is not safe to copy from ORACLE to CSV). For instance, if we wanted to copy the contents of PERSON.CSV to an ORACLE table CLIENT:

NOTE: To switch the driver into and out of CSV /ORA modes use the sql proc:

NOTE: unless all of the fields in the table are character strings, the driver may write unintelligible records, or crash when switched to CSV mode. This is because the 'become' driver settings (in this case ORACLE) override the data type mappings for the UD8/CSV driver.

$Revision: 1.12 $ $Date: 2011/02/28 02:56:33 $[go to top]