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

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

Postby Silvio.Falconi » Sat Sep 17, 2022 6:59 am

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

sample of storico.txt
Code: Select all  Expand view

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 view
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 view
  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" }
 


Image




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 view


 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

Image




Any solution pls ?
Last edited by Silvio.Falconi on Sat Sep 17, 2022 10:46 am, edited 1 time in total.
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7021
Joined: Thu Oct 18, 2012 7:17 pm

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

Postby alerchster » Sat Sep 17, 2022 7:45 am

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

Ing. Anton Lerchster
User avatar
alerchster
 
Posts: 91
Joined: Mon Oct 22, 2012 4:43 pm

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

Postby Silvio.Falconi » Sat Sep 17, 2022 10:19 am

I made It on main, i must repeat It. ?
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7021
Joined: Thu Oct 18, 2012 7:17 pm

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

Postby Jimmy » 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
greeting,
Jimmy
User avatar
Jimmy
 
Posts: 1725
Joined: Thu Sep 05, 2019 5:32 am
Location: Hamburg, Germany

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

Postby nageswaragunupudi » Sat Sep 17, 2022 4:05 pm

Setting date format this way also is valid
Code: Select all  Expand view
  SET DATE FORMAT TO "YYYY/MM/DD"
   ? Date()
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10604
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby nageswaragunupudi » 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 view
       AEVAL( aData, { | arr, nIndex | aData[nIndex][1] := SUBSTR( arr[1], 9, 2 ) + "/" + SUBSTR( arr[1], 6, 2 ) + "/" + LEFT( arr[1], 4 ) } )
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10604
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby nageswaragunupudi » 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
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10604
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby nageswaragunupudi » 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 view

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 view

   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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10604
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby Silvio.Falconi » Sun Sep 18, 2022 10:17 am

I sent you Yesterday storico.txt
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7021
Joined: Thu Oct 18, 2012 7:17 pm

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

Postby nageswaragunupudi » Sun Sep 18, 2022 10:43 am

Please try this.
Code: Select all  Expand view
#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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10604
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby Silvio.Falconi » Mon Sep 19, 2022 7:11 am

nageswaragunupudi wrote:Please try this.
Code: Select all  Expand view
#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
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7021
Joined: Thu Oct 18, 2012 7:17 pm


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: dutch, Google [Bot] and 33 guests