[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]] ==== Importing (AREV Specific) ==== === At 16 JUL 1999 05:37:06AM Kenny wrote: === {{tag>"AREV Specific"}} 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? ---- === At 16 JUL 1999 06:16AM Richard Wilson wrote: === 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 ---- === At 16 JUL 1999 06:24AM Richard Wilson wrote: === 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 ---- === At 16 JUL 1999 06:29AM Richard Wilson wrote: === 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 ---- === At 16 JUL 1999 10:19PM Eric Drews, Drews Enterprises wrote: === 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 ---- === At 28 JUL 1999 10:42PM Kenny wrote: === 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. ---- === At 05 AUG 1999 09:48PM kenny wrote: === 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 ---- === At 06 AUG 1999 06:44AM Richard Wilson wrote: === change IMPORT ASCII testimport to IMPORT ASCII testimport (S ---- === At 14 OCT 1999 02:03AM Dean Todd, Computer Resource Team - Orlando wrote: === 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 [[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=8F4BA24C99FCA528852567B00034D5D8|View this thread on the forum...]]