Importing from csv with changing order of columns

A

abhishek.shah

New Member
[TABLE="width: 500"]
[TR]
[TD]tmpinv_InvNbr
[/TD]
[TD]tag
[/TD]
[TD]month
[/TD]
[TD]tmpinv_SugDate(dd/mm/yyyy)
[/TD]
[TD]xyz
[/TD]
[TD]lmn
[/TD]
[TD]pqr
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]inv-11
[/TD]
[TD]march
[/TD]
[TD]12/03/2012
[/TD]
[TD]--
[/TD]
[TD]---
[/TD]
[TD]---
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]125
[/TD]
[TD]inv-11
[/TD]
[TD]may
[/TD]
[TD]15/05/2012
[/TD]
[TD]--
[/TD]
[TD]---
[/TD]
[TD]---
[/TD]
[/TR]
[TR]
[TD]156
[/TD]
[TD]inv-12
[/TD]
[TD]june
[/TD]
[TD]20/06/2012
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

I am importing data from a csv but order of the column (invoice suggested) is variable. So I need to handle as to which column the data is imported from.
Here offset-tmpinv_SugDate stores the count of offset of the needed column from the first column.
CREATE tmpinv.
IMPORT DELIMITER ","
tmpinv_InvNbr .

REPEAT WHILE i < offset-tmpinv_SugDate :
PUT "^".
i = i + 1.
END.
IMPORT DELIMITER ","
tmpinv_SugDate /* AKS08082012 */
NO-ERROR.

I am trying this but apart from tmpinv_InvNbr nothing is getting loaded accurately.
Please suggest a work around.
 
You have already posted a thread on this topic and seem to have completely ignored my comments. I'm feeling nice so will help a little.

Code: 
DEFINE VARIABLE lv-Detail-Line AS CHARACTER NO-UNDO EXTENT 40. /*The file has a max width of 40 columns*/
DEFINE VARIABLE lv-CurrentRow  AS INTEGER   NO-UNDO.    
DEFINE VARIABLE lv-i           AS INTEGER   NO-UNDO.
DEFINE VARIABLE lv-Label       AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-Format      AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-DataType    AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-Errors      AS CHARACTER NO-UNDO.


INPUT STREAM s-import FROM VALUE(lv-ImportFileName).

ImportLoop:
REPEAT:
  lv-Detail-Line = "".
  IMPORT STREAM s-import DELIMITER "," lv-Detail-Line.
  lv-CurrentRow = lv-CurrentRow + 1.
  IF lv-CurrentRow EQ 1 AND NOT VALID-HANDLE(lv-TTHandle) THEN /*This is the top column so read the labels*/
  DO:
    CREATE TEMP-TABLE lv-TTHandle.
    ColLoop:
    DO lv-i = 1 TO 40:
      IF lv-Detail-Line[lv-i] EQ "" THEN 
        NEXT ColLoop.
      lv-Label = REPLACE(lv-Detail-Line[lv-i]," ","").
      lv-Label = REPLACE(lv-Label,",","").
      ASSIGN 
        lv-Format   = "X(20)"
        lv-DataType = "CHARACTER". /*You can set your format conditionally based on the label here*/
      lv-TTHandle:ADD-NEW-FIELD(lv-Label,lv-DataType,0,lv-Format,?,?,lv-Detail-Line[lv-i]).
      CREATE tt-Columns. /*Keep track of the columns we have in the file*/
      ASSIGN
        tt-Columns.ColumnName       = lv-Label
        tt-Columns.ColumnNumber     = lv-i
        tt-Columns.DataType         = lv-DataType.
    END.
    lv-TTHandle:TEMP-TABLE-PREPARE("tt-Import").
    lv-DefaultBufferHandle = lv-TTHandle:DEFAULT-BUFFER-HANDLE.
    NEXT ImportLoop.
  END.
  DataLoop:
  DO lv-i = 1 TO 40:
    FIND FIRST tt-Columns
      WHERE tt-Columns.ColumnNumber EQ lv-i NO-ERROR. /*Just check we've got one!*/
    IF NOT AVAILABLE tt-Columns THEN 
      NEXT DataLoop.
    CASE tt-Columns.DataType:
      WHEN "INTEGER" OR WHEN "DECIMAL" THEN 
        lv-DefaultBufferHandle:BUFFER-FIELD(tt-Columns.ColumnName):BUFFER-VALUE = fn-EDI-Trim-Format(lv-Detail-Line[lv-i]).
      WHEN "DATE" THEN 
        lv-DefaultBufferHandle:BUFFER-FIELD(tt-Columns.ColumnName):BUFFER-VALUE = fn-EDI-DateConv(lv-Detail-Line[lv-i]).
      OTHERWISE
        lv-DefaultBufferHandle:BUFFER-FIELD(tt-Columns.ColumnName):BUFFER-VALUE = TRIM(lv-Detail-Line[lv-i],CHR(160)).
    END CASE. 
  END.
END.
  

INPUT STREAM s-import CLOSE.


This will not run out of the box - it won't even compile - but it should give an idea of what you need to do.
 
My apologies since I could not find the e-mail in my inbox for my previous thread and so I lost track of it.
I tried importing converting into Excel but the problem I face here is since my data is not sanitized my repeat loop exits when it comes across blank row.

retrieve_data:
repeat:
/* if it hits a blank item then it exits */
if chExcel:ActiveCell:Offset( 0, v-offset-tmpinv_InvNbr ):Value = ? then leave retrieve_data.

create tmpinv.
ASSIGN tmpinv.tmpinv_InvNbr = chExcel:ActiveCell:Offset( 0, v-offset-tmpinv_InvNbr ):VALUE
tmpinv.tmpinv_SugDate = chExcel:ActiveCell:Offset( 0, v-offset-tmpinv_SugDate ):VALUE NO-ERROR.

END.
/* move to the next row */
chExcel:ActiveCell:Offset( 1, 0 ):Activate.
end.


If I don't put limitation of blank row the repeat goes into infinite loop. Please guide.
 
Count the number of consecutive blank rows then and if it goes above a certain threshold, stop.
 
Hey thanks,
I took the footer sentence of the report xls as the end line. Works fine. Also I dont need to create CSV. Now my job is efficiently handled right in excel.
Thanks again.
 
You must log in or register to reply here.
Back
Top