FiveTech Software tech support forums

Posted: **Sat Sep 17, 2022 6:59 am**

I have to convert from a txt text file to an archive dbf

sample of storico.txt


1939/01/07  BA  58  22  47  49  69
1939/01/07  FI  27  57  81  43  61
1939/01/07  MI  40  38  57  67  7
1939/01/07  NA  85  44  48  88  55
1939/01/07  PA  73  80  39  38  57
1939/01/07  RM  73  24  4   39  22
1939/01/07  TO  19  43  10  31  27
1939/01/07  VE  9   43  61  14  75

the fields of dbf

Code: Select all | Expand

local aFields :=      { { "DATA" , "D", 8, 0 },;
                        { "RUOTA", "C", 2, 0 },;
                        { "N1"   , "N", 2, 0 },;
                        { "N2"   , "N", 2, 0 },;
                        { "N3"   , "N", 2, 0 },;
                        { "N4"   , "N", 2, 0 },;
                        { "N5"   , "N", 2, 0 }}

the conversion from historical file.txt to aData array does it well in fact I can see it in a browse

Code: Select all | Expand

   Local  cFieldList:="data,ruota,n1,n2,n3,n4,n5"
      Local  csvfile := cDirZip+"storico.txt"
      Local  cSymbol := CHR( 9 )
      Local  cText := StrTran( MemoRead( csvfile ), CHR( 10 ), Chr(1) )
      Local  aData := HB_ATokens( cText, Chr(1), .t., .t. )
      local  nPos     := 0

     AEval( aData, { |c,i| c := StrTran( c, Chr(1), CRLF ), aData[ i ] := HB_ATokens( c, cSymbol, .t., .t. ) } )

      if len( ATail( aData ) ) < 2
           ASize( aData, Len( aData ) - 1 )
        endif

        AEVAL( aData, { | arr, nIndex | aData[nIndex][1] := SUBSTR( arr[1], 9, 2 ) + "/" + SUBSTR( arr[1], 6, 2 ) + "/" + LEFT( arr[1], 4 ) } )

      XBROWSER aData TITLE "TXT AS ARRAY" SETUP oBrw:cHeaders := { "Data", "Ruota", "Num1", "Num2", "Num3", "Num4", "Num5" }

when I then do the conversion, that is,

I copy the records of the aData array into the dbf archive, it makes errors, every now and then it does not insert the date field

Code: Select all | Expand



 local  bprogress  := { || (oProgress:Set( npos++ ),; //oProgress:SetPos( npos++ ),;
                                 oSay[3]:SetText(ltrim(str(npos))),;
                                   oSay[3]:refresh(),;
                                  SysRefresh() ) }


      nMeter := 0
     oMeter:set(nMeter)
      sysrefresh()



          oProgress:ntotal:= len(aData)

         olotto2:= TDatabase():Open( , cPath+"STORICO", "DBFCDX", .T. )
         SET DELETED  ON
         olotto2:setorder(0)
         oLotto2:gotop()
         oLotto2:fw_ArrayToDBF( aData,,bProgress)
     //FW_ArrayToDBF( aData, cFieldList, bProgress, lOverWrite, lRecallDeleted, bTrigger )
        DbCloseAll()
     return nil

result

Any solution pls ?

Posted: **Sat Sep 17, 2022 7:45 am**

It seems that SET DATE to the right format is not set in prg DD.MM.YYYY ?

Posted: **Sat Sep 17, 2022 10:19 am**

I made It on main, i must repeat It. ?

Posted: **Sat Sep 17, 2022 10:54 am**

hi Silvio,

what DATE Format do you use :?:

you got YYYY/MM/DD but there is no DATEFORMAT like that

Syntax Date Format

ANSI yy.mm.dd
BRITISH dd/mm/yy
FRENCH dd/mm/yy
GERMAN dd.mm.yy
ITALIAN dd-mm-yy
JAPAN yy.mm.dd
USA mm-dd-yy
AMERICAN mm/dd/yy

so "extract" YYYY, MM and DD from Element before store into DBF

Posted: **Sat Sep 17, 2022 4:05 pm**

Setting date format this way also is valid

Code: Select all | Expand

   SET DATE FORMAT TO "YYYY/MM/DD"
   ? Date()

Posted: **Sat Sep 17, 2022 4:08 pm**

Actually he converted the date string from the format
YYYY/MM/DD
to
DD/MM/YYYY

with this code

Code: Select all | Expand

        AEVAL( aData, { | arr, nIndex | aData[nIndex][1] := SUBSTR( arr[1], 9, 2 ) + "/" + SUBSTR( arr[1], 6, 2 ) + "/" + LEFT( arr[1], 4 ) } )

Posted: **Sat Sep 17, 2022 4:09 pm**

Glad you are back to work my friend Silvio
Please send the full text file to my email

Posted: **Sat Sep 17, 2022 5:34 pm**

Most of the above comments were made, not knowing the full capabilities of FW_ArrayToDBF.

The function FW_ArrayToDBF( aData ) converts any string values to the destination field type, before saving the data. This function is made keeping in view conversion from CSV.

This type conversion is made using the function

Code: Select all | Expand


uCharToVal( cValue, cFieldType ) //--> Coverted value of the FieldType

Converting strings to DateType is obviously difficult. This works well but may fail in some cases.

You may test this:

Code: Select all | Expand


   SET DATE ITALIAN
   SET CENTURY ON

   ? uCharToVal( "2021/10/23", "D" ), ;
     uCharToVal( "23/10/2021", "D" ), ;
     uCharToVal( "10/23/2121", "D" ), ;
     uCharToVal( "23 Oct 2021", "D" ), ;
     uCharToVal( "OctOber 23, 2021", "D" )

In all the above cases, the result is date value 23-10-2021.
This function works irrespective of the date format set.
In some cases like "10/04/21", the function can not decide whether it is formatted as dd/mm/yy or mm/dd/yy or yy/mm/dd, etc. In such cases, set date format is used.
Still there may be some cases, where this function may fail

I like to examine Mr.Silvio's data.

Posted: **Sun Sep 18, 2022 10:17 am**

I sent you Yesterday storico.txt

Posted: **Sun Sep 18, 2022 10:43 am**

Please try this.

Code: Select all | Expand

#include "fivewin.ch"

REQUEST DBFCDX

function Main()

   local cText, aData
   local aFields :=   { { "DATA" , "D", 8, 0 },;
                        { "RUOTA", "C", 2, 0 },;
                        { "N1"   , "N", 2, 0 },;
                        { "N2"   , "N", 2, 0 },;
                        { "N3"   , "N", 2, 0 },;
                        { "N4"   , "N", 2, 0 },;
                        { "N5"   , "N", 2, 0 }}

   cText    := HB_MEMOREAD( "storico.txt" )
   cText    := FW_ALLTRIM( cText )
   cText    := StrTran( cText, CRLF, Chr( 10 ) )

   aData    := HB_ATokens( cText, Chr( 10 ) )
   AEval( aData, { |c,i| aData[ i ] := HB_ATokens( c, Chr( 9 ) ) } )
   AEval( aData, { |a,i| a[ 1 ] := uCharToVal( a[ 1 ], "D" ) } )

   DBCREATE( "TEST.DBF", aFields, "DBFCDX", .T., "TT" )
   FW_ArrayToDBF( aData )
   GO TOP
   XBROWSER ALIAS() SHOW RECID
   CLOSE DATA

return nil

This is working for me here.

Posted: **Mon Sep 19, 2022 7:11 am**

nageswaragunupudi wrote:Please try this.

Code: Select all | Expand

#include "fivewin.ch"

REQUEST DBFCDX

function Main()

   local cText, aData
   local aFields :=   { { "DATA" , "D", 8, 0 },;
                        { "RUOTA", "C", 2, 0 },;
                        { "N1"   , "N", 2, 0 },;
                        { "N2"   , "N", 2, 0 },;
                        { "N3"   , "N", 2, 0 },;
                        { "N4"   , "N", 2, 0 },;
                        { "N5"   , "N", 2, 0 }}

   cText    := HB_MEMOREAD( "storico.txt" )
   cText    := FW_ALLTRIM( cText )
   cText    := StrTran( cText, CRLF, Chr( 10 ) )

   aData    := HB_ATokens( cText, Chr( 10 ) )
   AEval( aData, { |c,i| aData[ i ] := HB_ATokens( c, Chr( 9 ) ) } )
   AEval( aData, { |a,i| a[ 1 ] := uCharToVal( a[ 1 ], "D" ) } )

   DBCREATE( "TEST.DBF", aFields, "DBFCDX", .T., "TT" )
   FW_ArrayToDBF( aData )
   GO TOP
   XBROWSER ALIAS() SHOW RECID
   CLOSE DATA

return nil

This is working for me here.

THANKS RAO

FiveTech Software tech support forums

convert from a txt text file to a dbf with errors - RESOLVED

convert from a txt text file to a dbf with errors - RESOLVED

Re: convert from a txt text file to a dbf with errors

Re: convert from a txt text file to a dbf with errors

Re: convert from a txt text file to a dbf with errors - RESOLVED

Re: convert from a txt text file to a dbf with errors - RESOLVED

Re: convert from a txt text file to a dbf with errors - RESOLVED

Re: convert from a txt text file to a dbf with errors - RESOLVED

Re: convert from a txt text file to a dbf with errors - RESOLVED

Re: convert from a txt text file to a dbf with errors - RESOLVED

Re: convert from a txt text file to a dbf with errors - RESOLVED

Re: convert from a txt text file to a dbf with errors - RESOLVED