Create a file layout from a CSV file

code
2

Very useful Geoff! Hopefully SV will increase the 100 row limit to read all the records, that would be a simple change.

The DCT Basic Import does analyze Numeric columns and sizes them well as USHORT ULONG or DECIMAL. If there are negative values it will select SHORT or LONG.

You can see that in my post in the How to import CSV into Clarion 11 TopSpeed file without writing code questions topic. Here’s a DCT Imported CSV and the source showing numbers

image
image865×251 23.1 KB

I’ll paste some code I thought of for figuring out the column type. Each Data row needs to store the max seen for Digits Left and Right of the Decimal, plus if there’s a Deciaml, plus if there’s a negative.

Lengths Queue,PRE(LQ)  !Your Column Lengths Q plus Number
Row1         long
Max          long      

NotNumeric   BYTE  !1=Column w/o a Number found so String
DigitsLeft   BYTE  !Max Digits Left of Decimal Point
DigitsRight  BYTE  !Max Digits Right of Decimal Point i.e. pennies
IsNegative   BYTE  !Less than Zero seen
IsDecimal    BYTE  !'.' seen, can be Zero Digits Right
        end 
NumData     STRING(40)        
Dot         SHORT        !Decimal Point
NumLeft     SHORT        !Digits Left of Decimal Point
NumRight    SHORT        !Digits Right of Decimal Point

–

!--- For Each Column -----------------------
NumData = LEFT(ColData)     !Remove leading spaces for numbers?
LenNum = LEN(CLIP(NumData))
IF LenNum=0 THEN                            !Is Column blank
    !Basic driver ignores blanks in numeric 
    !or ??? LQ:NotNumeric = True ??? 
ELSIF ~Numeric(NumData) OR LenNum > 31 THEN !Is Column NOT Numeric or too long to be a number 
    LQ:NotNumeric = True                    !Any Non numeric then must be STRING
ELSE
    IF NumData+0 < 0 THEN 
       LQ:IsNegative=True            !allow negative
       IF NumData[1]='-' THEN     !Can there be a trailing -
          NumData=SUB(NumData,2,99)
          LenNum -=  1
       END 
    END 
    Dot = INSTRING('.',NumData)     !Find '.' in 12345.67
    IF Dot THEN                     !Decimal Yes
       LQ:IsDecimal=True 
       NumLeft  = Dot - 1
       NumRight = LenNum - Dot
    ELSE                            !Integer
       NumLeft = LenNum
       NumRight = 0
    END 

    IF LQ:DigitsLeft < NumLeft THEN
       LQ:DigitsLeft = NumLeft
    END 
    IF LQ:DigitsRight < NumRight THEN
       LQ:DigitsRight = NumRight
    END 
END

–

!----- Figure out the type 
IF LQ:NotNumeric THEN 
   Type='STRING(' & LQ:Max &')'
ELSIF LQ:IsDecimal OR LQ:DigitsLeft > 9 THEN    !LONG 2,147,483,647
   ! DECIMAL(Length,places) 
   Type='DECIMAL('& LQ:DigitsLeft + LQ:DigitsRight &','&  LQ:DigitsRight &')'
ELSE
  ! IF LQ:DigitsLeft <= 2 AND ~LQ:IsNegative THEN  ! BASIC import does not do BYTE
  !    Type ='BYTE'  
    IF LQ:DigitsLeft <= 4 THEN      !USHORT 65,535
       Type=CHOOSE(~LQ:IsNegative,'USHORT','SHORT') 
    ELSE
       Type=CHOOSE(~LQ:IsNegative,'ULONG','LONG')  !Carl would always do LONG 
    END 
END
1 Like