I currently using AREV 1.15 and we need to import data into our AREV application. The source import data is a saved as CSV (comma delimited file) from an Excel spreadsheet.

I am trying to use an IMPORT process to import variable length ASCII records. I have defined the Process name, DOS file name (location of the CSV file), Revelation file name and the Field/record/EOF delimiter. I'm using comma (ASCII 44) as the field delimiter and

carriage return/line feed (13,10) as the record delimiter and the default ASCII 26 as the EOF delimiter.

I only see a 0 key record with no field data after running the import process using the F9 key.

Does anyone have any ideas?

Is there a programatic way importing data from a CSV file into an AREV table?

I just tested with 1.16 and had to define EOR as just 13 not 13,10

also make sure you define your dictionary fields to import otherwise

you will get null records

also once you get your import working from the menu then you can create a tcl command as follows

TCL

DATA Y

IMPORT ASCII testimport

where testimport is the import procedure you defined

you may need more data stmts depending on more prompts appearing

one last thing. once the template is defined there will be a record

in file IMPORT.EXPORT like IMPORT.ASCII."your template name"

you can review it and then should be able to build your own templates

outside of the import/export menu

You may have one small problem with your import. If you don't recognize that some of your CSV records have data in quotes (containing commas?) you may end up with one or more records in your imported data being a bit out of kilter. You might consider switching your delimiter to a TAB (Char(9)) instead. Excel will handle this quite easily – check the 'save as' file types. BTW, I do data conversion as a large part of my business.

Regards,

Eric Drews

Drews Enterprises

www.drews-ent.com

edrews@drews-ent.com

908 665 2513

Thanks. I managed to get it working. I found just like you did, that you have to setup the dictionary items in order to see the data.

Richard

I put the following statement in a VOC, but I still get the prompt asking whether I want to clear the data in the import file. Any clues on how to suppress this prompt.

TCL

DATA Y

IMPORT ASCII testimport

change

IMPORT ASCII testimport

to

IMPORT ASCII testimport (S

AREV's Import is way too clutzy and slow. The following code will import 200,000 CVS records in about 10 minutes on a Pentium II, 333. Rev's Import Pgm imports a single record at a time, typically less than 150-bytes.

This program sucks in data in 32k chunks. Hope this helps

DECLARE SUBROUTINE MSG, VIDEO.RW, CATALYST

DECLARE FUNCTION POP.UP

* Import CVS ASCII data into LH files

* By Dean Todd & Allen Fitzsimmons

*BEGIN*

* RESPONSE =C:\IMPORT\ '

DUMMY =Processing:|" : SPACE(40) : "||||"

In_Buffer ='

Temp ='

POS =0

RLength =0

FILE.FLAG ='

RLength =32768

FLAG='

Byte =0

Today =Date()

Deano =0

OPEN "MEMBERS" TO data_file ELSE

  MSG("ERROR: Cannot open MEMBERS File.", "", "", "")

  STOP

END

@RECORD='

MSG("If you are using the floppy drive, insert a disk now!", "", "", "")

gosub import_locations

* MSG("What DOS file to process?|", "RC", RESPONSE, "")

  OSOPEN RESPONSE TO FILE.IN THEN NULL

  IF STATUS() THEN

      MSG("ERROR: Cannot open %1%!", "", "", "")

      FLAG=0

  END ELSE

      FLAG=1

      FILE.IN=RESPONSE

      X=DIR(FILE.IN)

      F.BYTES=X

      F.DATE=OCONV(X,"D2-")

      F.TIME=OCONV(X,"MTS")

  END

LOOP UNTIL FILE.FLAG

  OSBREAD temp FROM FILE.IN AT POS LENGTH RLength

  In_Buffer := temp

  swap \0C0D\ with \0D\ in In_Buffer

  FILE.FLAG=STATUS()

  POS += RLength

  IF POS ] F.BYTES THEN FILE.FLAG=1

  swap Char(34) : Char(44) : Char(34) with @Vm IN In_Buffer;* QUOTE COMMA QUOTE

  swap Char(34) with '' IN In_Buffer;* QUOTE only

  swap Char(44) with @vm in In_Buffer; * Get the improper items.

  swap \0D0A\ with @fm IN In_Buffer

  MSG(DUMMY,"UB",IMAGE,"")

  CNT=count(IN_BUFFER, @FM) -1

 for X=1 to CNT

    @ID       =Trim(In_Buffer)

    FIRST_NAME=Trim(In_Buffer)

    LAST_NAME =Trim(In_Buffer)

    SEX       =In_Buffer

    Temp      =In_Buffer; * DOB

    ZIP       =In_Buffer

    PHONE     =In_Buffer

    PRINT @(20,10) : FMT("A] " : @ID, "C#20")

    PRINT @(20,12) : FMT("Count--] " : Reco, "R#20")

• dob is in this format, 19711222

• so break it down for Rev, then IConvert it.

        Year= Temp1,4;          * get Year

        Month=Temp5,2;          * get month

        Day  =Temp7,2;          * get day

        Dob=Month : '-' : Day : '-' : Year

• dob NOW is in this format, 8/7/92 0:00:00 , 8-3-99 DBT

    @RECORD =Trim(FIRST_NAME)

    @RECORD =Trim(LAST_NAME)

    @RECORD =SEX

    @Record ='

    @record=iconv(Dob, "D") ; ** stuff converted Birth date

    @RECORD =ZIP

    @RECORD=PHONE

    @RECORD=Whatever hard coded sting you want'

    @RECORD=Date()

    Reco=Reco + 1

    WRITE @RECORD TO data_file, @ID

    In_Buffer=delete(In_Buffer,1,0,0);

 next X

repeat

ender:

osclose File.In

If DISKNAME=C:\IMPORT' then

osdelete File.In

end

MSG("", "DB", IMAGE, "")

STOP

import_locations:

CATALYST("P", "POPUPS*IMPORT_LOCATIONS")

IF @ANS=' THEN STOP

IF @ANS=END' THEN STOP

DISKNAME=@ANS

DRIVE=DISKNAME

INITDIR DRIVE: '\*.*'

       LIST=DIRLIST()

IF LIST='THEN

    MSG("There are NO files found on ":DRIVE:".","",'','')

    @ANS='

    RETURN

END

LIST=LIST :"End"

COL=25

ROW=1

FILE='

DISPLAY=LIST

FORMAT=1:14:L::File Name"

MODE=R"

SELECTION=0

TITLE= Select A File ESC Aborts '

ATTR='

HELP='

COLUMN=1

TYPE='

@ANS='

@ANS=POP.UP(COL,ROW,FILE,DISPLAY,FORMAT,MODE,SELECTION,TITLE,ATTR,HELP,COLUMN,TYPE)

IF @ANS=' THEN STOP

IF @ANS=End' THEN STOP

RESPONSE=DRIVE: '\' : @ans

return

View this thread on the forum...