Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

CSV to DBF
Posted: Fri Aug 23, 2013 07:00 AM

Someone have a function to convert a file csv ( with ; separator) into dbf ?
thanks

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 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

cnavarro

Posts: 6755

Joined: Wed Feb 15, 2012 08:25 PM

Re: CSV to DBF
Posted: Fri Aug 23, 2013 08:09 AM

Hola, a ver si te sirve
http://forums.fivetechsupport.com/viewtopic.php?f=3&t=18285&start=0&hilit=append+from+delimiter

Cristobal Navarro

Hay dos tipos de personas: las que te hacen perder el tiempo y las que te hacen perder la noción del tiempo

El secreto de la felicidad no está en hacer lo que te gusta, sino en que te guste lo que haces

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: CSV to DBF
Posted: Fri Aug 23, 2013 08:47 AM

Hi

I use this function for inserting data from .csv or .unl files in to sql table
You can change INSERT INTO statement with Dbeplace or change separator or what else you need

Code (fw): Select all Collapse

//----------------------------------------------------------------------------//

FUNCTION LoadFrom(cFile,cTable,cSeparator,oSayRecno,oSayLastrec,lLoading)

LOCAL oDbf
LOCAL cSql     := ""
LOCAL cZnak    := "" // Character value
LOCAL cZbor    := "" // Field value
LOCAL aRed     := {} // Row values
LOCAL nRedovi  := 0  // Inserted rows

LOCAL nBrojac  := 0
LOCAL nBrojac1 := 0
LOCAL nBrojac2 := 0

LOCAL nSize    := 0  // Size of source file

LOCAL fOpen

      lBreak := .f.  // STATIC variable if we want to break inserting

IF !File( cFile )
   MsgAlert("NO Source file !")
   RETURN NIL
ENDIF

nSize := fSize( cFile )

fOpen := fOpen( cFile , 0 ) // 0 Read 1 Write 2 Read/Write
IF fError() <> 0
   MsgAlert("File Open Error !")
   RETURN NIL
ENDIF

oSayLastRec:SetColor( CLR_BLACK, CLR_GRAY )
oSayLastRec:SetText( "Inserting ..." )

CursorWait()

lLoading := .t.

FOR nBrojac := 0 to nSize

    fSeek( fOpen, nBrojac )
    cZnak := fReadStr( fOpen, 1 )

    IF cZnak <> CHR(13) .AND. cZnak <> CHR(10) // End of row .CSV have CHR(13) + CHR(10) but .UNL (unloaded from sql table) only CHR(10)
       IF cZnak <> cSeparator // ";"
          cZbor := cZbor + cZnak
        ELSE
          AADD( aRed, cZbor )
          cZbor := ""
       ENDIF
     ELSE
       IF LEN( cZbor ) > 0    // If there is no separator on the end of row lets finish last field
          AADD( aRed, cZbor )
          cZbor := ""
       ENDIF

       IF LEN( aRed ) > 0 // Inserting
          cSql := cSql + "INSERT INTO " + ALLTRIM(cTable) + " VALUES("
          FOR nBrojac1 = 1 TO LEN( aRed ) // Adding values
              IF AT( "'", aRed[ nBrojac1 ] ) >0  // If there are ' in data let use "
                 cSql := cSql + '"' + aRed[ nBrojac1 ] + '"'
               ELSE
                 cSql := cSql + "'" + aRed[ nBrojac1 ] + "'"
              ENDIF
              IF nBrojac1 < LEN( aRed )
                 cSql := cSql + ","       // Add , if not last field
              ENDIF
          NEXT
          cSql := cSql + ");  " // End of SQL for every row
          aRed := {}            // For new row

          oDbf := TDbOdbcDirect():New( cSql, oOdbc )

          IF oOdbc:IsError()    // If error
             oOdbc:aErrors := {}
             oDbf:End()
             CursorArrow()

             fClose( fOpen )
             lLoading := .f.

             oSayRecno:SetText( nRedovi )
             IF nRedovi > 0
                oSayLastRec:SetColor( CLR_BLACK, CLR_HRED )
                oSayLastRec:SetText( "Error ... at Row: " + ALLTRIM(STR(nRedovi+1)) )
              ELSE
                oSayLastRec:SetColor( CLR_BLACK, CLR_HRED )
                oSayLastRec:SetText( "Error ..." )
             ENDIF

             RETURN NIL
          ENDIF

          SysRefresh() //
          CursorWait() //

          nRedovi := nRedovi +1
          IF nRedovi / 100 = INT( nRedovi / 100 )
             oSayRecno:SetText( nRedovi )
          ENDIF

          oDbf:End()
          cSql := ""
       ENDIF
    ENDIF

    IF lBreak
       EXIT
    ENDIF

NEXT

fClose( fOpen )
lLoading := .f.

IF !lBreak
   oSayRecno:SetText( nRedovi ) // Last row
   oSayLastRec:SetColor( CLR_BLACK, CLR_GREEN )
   oSayLastRec:SetText( "Finished" )
 ELSE
   oSayRecno:SetText( nRedovi ) // Last row
   oSayLastRec:SetColor( CLR_BLACK, CLR_HRED )
   oSayLastRec:SetText( "Break by User after: " +ALLTRIM(STR(nRedovi)) + " Rows Inserted !" )
ENDIF

CursorArrow()

RETURN NIL

Regards,

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: CSV to DBF
Posted: Fri Aug 23, 2013 03:31 PM

Assuming that the CSV file's structure and datatypes correctly match the DBF file, I could think of small 5 lines function to do the job.

Code (fw): Select all Collapse

cText := StrTran( MemoRead( csvfile ), CRLF, Chr(1) )
aData := HB_ATokens( cText, Chr(1), .t., .t. )
AEval( aData, { |c,i| c := StrTran( c, Chr(1), CRLF ), aData[ i ] := HB_ATokens( c, ",", .t., .t. ) } )
USE DESTINATION.DBF NEW ALIAS DST
DST->( FW_ArrayToDBF( aData ) )

function headers, variable declarations can be added.

This logic preserves CRLFs and commas with in double quoted strings.

Regards

G. N. Rao.

Hyderabad, India

Silvio.Falconi

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

Re: CSV to DBF
Posted: Mon Aug 26, 2013 02:48 PM

Nages,
I have this error ...perhaps I make an mistake ...

I insert here the test.prg to converte csv to dbf

and some lines of the file lotto.csv

on this csv file On first line there are the fields declared

the error

Code (fw): Select all Collapse

Application
===========
   Path and name: C:\Work\Errori\cvs\test.Exe (32 bits)
   Size: 2,928,640 bytes
   Compiler version: Harbour 3.2.0dev (Rev. 17516)
   FiveWin  Version: FWH 13.05
   Windows version: 6.1, Build 7600 

   Time from start: 0 hours 0 mins 1 secs 
   Error occurred at: 08/26/13, 16:59:30
   Error description: Error BASE/1132  Bound error: array access
   Args:
     [   1] = A   { ... }
     [   2] = N   2

Stack Calls
===========
   Called from: .\source\classes\DATABASE.PRG => FW_ARRAYTODBF( 1613 )
   Called from: test.prg => MAIN( 22 )

the test.prg

Code (fw): Select all Collapse

#include "fivewin.ch"

REQUEST DBFCDX
REQUEST DBFFPT
EXTERNAL ORDKEYNO,ORDKEYCOUNT,ORDCREATE,ORDKEYGOTO




Function Main()


RddSetDefault( "DBFCDX" )

csvfile:="Lotto.csv"
cText := StrTran( MemoRead( csvfile ), CRLF, Chr(1) )
aData := HB_ATokens( cText, Chr(1), .t., .t. )
AEval( aData, { |c,i| c := StrTran( c, Chr(1), CRLF ), aData[ i ] := HB_ATokens( c, ";", .t., .t. ) } )
Create_Db()
USE LOTTO.DBF NEW ALIAS DST
DST->( FW_ArrayToDBF( aData ) )
Return Nil






Function Create_Db()
  //-------------------------------------//


  DBCREATE('LO',{{'ESTRAZ','D',008,000},;
{'CONCORSO','C',003,000},;
{'BA1','N',002,000},;
{'BA2','N',002,000},;
{'BA3','N',002,000},;
{'BA4','N',002,000},;
{'BA5','N',002,000},;
{'CA1','N',002,000},;
{'CA2','N',002,000},;
{'CA3','N',002,000},;
{'CA4','N',002,000},;
{'CA5','N',002,000},;
{'FI1','N',002,000},;
{'FI2','N',002,000},;
{'FI3','N',002,000},;
{'FI4','N',002,000},;
{'FI5','N',002,000},;
{'GE1','N',002,000},;
{'GE2','N',002,000},;
{'GE3','N',002,000},;
{'GE4','N',002,000},;
{'GE5','N',002,000},;
{'MI1','N',002,000},;
{'MI2','N',002,000},;
{'MI3','N',002,000},;
{'MI4','N',002,000},;
{'MI5','N',002,000},;
{'NA1','N',002,000},;
{'NA2','N',002,000},;
{'NA3','N',002,000},;
{'NA4','N',002,000},;
{'NA5','N',002,000},;
{'PA1','N',002,000},;
{'PA2','N',002,000},;
{'PA3','N',002,000},;
{'PA4','N',002,000},;
{'PA5','N',002,000},;
{'RM1','N',002,000},;
{'RM2','N',002,000},;
{'RM3','N',002,000},;
{'RM4','N',002,000},;
{'RM5','N',002,000},;
{'TO1','N',002,000},;
{'TO2','N',002,000},;
{'TO3','N',002,000},;
{'TO4','N',002,000},;
{'TO5','N',002,000},;
{'VE1','N',002,000},;
{'VE2','N',002,000},;
{'VE3','N',002,000},;
{'VE4','N',002,000},;
{'VE5','N',002,000},;
{'NZ1','N',002,000},;
{'NZ2','N',002,000},;
{'NZ3','N',002,000},;
{'NZ4','N',002,000},;
{'NZ5','N',002,000} }, 'DBFCDX')



    close all
      use &('LO') new
      select LO
      if FILE('LOTTO.DBF')
         delete file &('LOTTO.cdx')
         append from &('LOTTO')
         dbcommitall()
         close all
         delete file &('LOTTO.dbf')
      endif
      close all
      rename &('LO.dbf') to &('LOTTO.dbf')








  //-------------------------------------//
  Return nil

some lines from lotto.csv

Code (fw): Select all Collapse

ESTRAZ;CONCORSO;BA1;BA2;BA3;BA4;BA5;CA1;CA2;CA3;CA4;CA5;FI1;FI2;FI3;FI4;FI5;GE1;GE2;GE3;GE4;GE5;MI1;MI2;MI3;MI4;MI5;NA1;NA2;NA3;NA4;NA5;PA1;PA2;PA3;PA4;PA5;RM1;RM2;RM3;RM4;RM5;TO1;TO2;TO3;TO4;TO5;VE1;VE2;VE3;VE4;VE5;NZ1;NZ2;NZ3;NZ4;NZ5
07/01/1939;1;58;22;47;49;69;0;0;0;0;0;27;57;81;43;61;0;0;0;0;0;40;38;57;67;7;85;44;48;88;55;73;80;39;58;57;73;24;4;39;22;19;43;10;31;27;9;43;61;14;75;0;0;0;0;0
14/01/1939;2;18;77;33;62;19;0;0;0;0;0;31;47;12;7;80;0;0;0;0;0;35;24;52;90;41;4;10;36;63;35;10;77;43;37;35;4;86;61;84;58;82;7;15;88;34;85;21;45;65;56;0;0;0;0;0
21/01/1939;3;68;65;41;28;67;0;0;0;0;0;37;82;69;9;67;0;0;0;0;0;37;23;12;19;27;15;76;82;11;55;29;8;44;69;28;36;74;7;70;76;36;41;43;35;40;76;85;20;42;22;0;0;0;0;0
28/01/1939;4;76;55;48;85;71;0;0;0;0;0;24;40;12;77;30;0;0;0;0;0;47;85;18;38;50;73;34;8;39;17;52;25;63;84;60;56;87;82;90;53;27;48;40;33;67;58;84;53;51;32;0;0;0;0;0

there is an error on array because when I open the dbf I see the records with errors ( see the first column)

I cmake a test with xbrowser aData and it run ok perhaps there is an error on FW_ArrayToDBF function ?

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 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

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: CSV to DBF
Posted: Mon Aug 26, 2013 06:03 PM

It looks like you are having a date format problem. The first record imported the date and the next three were blank. Here are the values in the original file:

07/01/1939
14/01/1939
21/01/1939
28/01/1939

Note that the last three dates have as the first value number greater than 12 so it appears that the import routine thinks that the first number is the month when I expect it is the actually the day-of-month.

So prehaps all you need is:

SET DATE ITALIAN

Regards,
James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: CSV to DBF
Posted: Mon Aug 26, 2013 08:01 PM

Silvio

Mr James is right. We should ourselves take care of date formatting issues when we deal with dates. Our settings should match the data.

1. As Mr James Bott advised, please SET DATE BRITISH and SET CENTURY ON at the beginning of the program. You can change the setting, if you want, only after FW_ArrayToDBF().

2. Because the CSV contains header information in the first row, you need to delete 1st Row of the array aData before writing to DBF. Please ensure that the aData's first row is really Data row.

3. Possibly the last row of aData is in incomplete array. Please delete the last row of aData also.

After these two rectifications, then call FW_ArrayToDBF().

Note: There is no problem with FW_ArrayToDBF() function.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: CSV to DBF
Posted: Tue Aug 27, 2013 06:17 AM

Silvio

Revised code adopting the above recommendataions.
I have also added browse of array and dbf.
I have used your Create_db() function and did not repeat it here.

Code (fw): Select all Collapse

#include "fivewin.ch"
#include "xbrowse.ch"
#include "hbcompat.ch"

REQUEST DBFCDX

function Main()

   local cText, csvfile, aData, aHead

   SET DATE BRITISH
   SET CENTURY ON

   RddSetDefault( "DBFCDX" )

   csvfile:="Lotto.csv"
   cText := StrTran( MemoRead( csvfile ), CRLF, Chr(1) )
   aData := HB_ATokens( cText, Chr(1), .t., .t. )
   AEval( aData, { |c,i| c := StrTran( c, Chr(1), CRLF ), aData[ i ] := HB_ATokens( c, ";", .t., .t. ) } )
   aHead    := aData[ 1 ]
   ADel( aData, 1, .t. )
   if len( ATail( aData ) ) < 2
      ASize( aData, Len( aData ) - 1 )
   endif
   XBROWSER aData TITLE "CSV AS ARRAY" SETUP oBrw:cHeaders := aHead

   Create_Db()
   USE LOTTO.DBF NEW ALIAS DST
   DST->( FW_ArrayToDBF( aData ) )
   DST->( DBGOTOP() )

   SET DATE ITALIAN
   XBROWSER "DST" TITLE "LOTTO.DBF"

return Nil

Notes:
#1) The above way of using HB_ATokens() preserves embedded CRLF inside double quoted strings.
#2) FW_ArrayToDBF() function not only copies array data into the DBF, but also converts the data in the array to correct data types of the fields.

Regards

G. N. Rao.

Hyderabad, India

Silvio.Falconi

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

Re: CSV to DBF
Posted: Tue Aug 27, 2013 07:30 AM

Thanks,
It run also with xharbour ?

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: CSV to DBF
Posted: Tue Aug 27, 2013 07:38 AM

Silvio.Falconi wrote:Thanks,
It run also with xharbour ?

Yes.
Keep the include "hbcompat.ch" always

Regards

G. N. Rao.

Hyderabad, India

Silvio.Falconi

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

Re: CSV to DBF
Posted: Tue Aug 27, 2013 07:49 AM

Nages,
perhpas there isanother error

now I tried load the new file from internet and I tried to converrte it into dbf

http://www.estrazionidellotto.com/estra ... llotto.csv

I saw the csv file have a line empty at init and the have the headers
I add on your function

ADel( aData, 1, .t. )
ADel( aData, 1, .t. )

to erase the first two lines
it make error when use fw:arraytodbf()

Code (fw): Select all Collapse

Application
===========
   Path and name: C:\Work\Errori\cvs\test.Exe (32 bits)
   Size: 2,929,152 bytes
   Compiler version: Harbour 3.2.0dev (Rev. 17516)
   FiveWin  Version: FWH 13.05
   Windows version: 6.1, Build 7600 

   Time from start: 0 hours 0 mins 9 secs 
   Error occurred at: 27/08/2013, 09:45:13
   Error description: Error BASE/1132  Bound error: array access
   Args:
     [   1] = A   { ... }
     [   2] = N   2

but sometimes make this error ...mhm it's strange!!!

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: CSV to DBF
Posted: Tue Aug 27, 2013 08:01 AM

As I said before, the logic works correctly if the data in the CSV file exactly matches the DBF structure and the data is valid.

If we need to handle in-disciplined data, you need to first validate the data and used rectified data as input. What checks you need to make depends on how unreliable the data source is. Programming for such checking becomes very personalized and customized programming. This is beyond the scope of general advice.

Anyway I suggest that you scan aData and see whether the length of every row is 57. Copy only the rows passing the test to a new array aTested and use aTested to write to the DBF.

Code (fw): Select all Collapse

aTested := {}
AEval( aData, { |a| If( Len( a ) == 57, AAdd( aTested, a ), nil ) } )
,,,
...
...
DST->( FW_ArrayToDBF( aTested ) )

Regards

G. N. Rao.

Hyderabad, India

Silvio.Falconi

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

Re: CSV to DBF
Posted: Wed Aug 28, 2013 03:36 PM

Mr Nages,

Now I have another file but with no ";" delimiter and I not Know how I can make to converte this txt into dbf

the file can be download from http://sys.sistemiwinforlife.it/estrazi ... bzh1n.txtv

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: CSV to DBF
Posted: Wed Aug 28, 2013 04:28 PM

The link is not working.
Please post first 10 lines of csv

Regards

G. N. Rao.

Hyderabad, India

Silvio.Falconi

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

Re: CSV to DBF
Posted: Wed Aug 28, 2013 05:36 PM

I have a file in this type

Code (fw): Select all Collapse

Conc.   Data conc.  Ora ESTR1   ESTR2   ESTR3   ESTR4   ESTR5   ESTR6   ESTR7   ESTR8   ESTR9   ESTR10  NUMERONE
1   21/01/2013  12  3   4   5   8   9   13  15  16  17  20  10
2   21/01/2013  13  1   2   3   4   5   6   7   8   16  20  18
3   21/01/2013  14  3   5   7   12  13  14  15  16  18  20  19
4   21/01/2013  15  3   4   5   7   10  11  13  14  15  19  7
5   21/01/2013  16  1   6   8   9   12  15  16  17  19  20  2
6   21/01/2013  17  2   3   5   8   9   13  15  16  17  18  20
7   21/01/2013  18  3   5   6   9   10  11  15  16  18  20  19
8   21/01/2013  19  1   2   3   10  12  15  17  18  19  20  2
9   21/01/2013  20  3   5   6   7   10  11  16  17  18  19  17
10  21/01/2013  21  2   3   6   8   9   12  13  15  16  18  1
11  21/01/2013  22  4   5   6   7   8   9   10  11  15  18  10

and another in this type ( it seems the same)

Code (fw): Select all Collapse

Conc.   Data    Ora                                                 N.
1   29/09/2009  12:00       1   4   5   10  14  15  16  17  18  20      7
2   29/09/2009  13:00       2   5   6   9   10  11  14  15  17  18      2
3   29/09/2009  14:00       1   4   8   9   10  11  16  17  19  20      12
4   29/09/2009  15:00       4   6   7   8   11  12  13  15  19  20      13
5   29/09/2009  16:00       2   3   5   6   7   10  13  16  17  19      17
6   29/09/2009  17:00       2   6   8   11  12  13  14  16  17  19      10
7   29/09/2009  18:00       9   10  12  13  14  15  16  17  19  20      18
8   29/09/2009  19:00       7   8   9   10  12  13  14  16  17  19      6
9   29/09/2009  20:00       3   7   8   9   12  14  16  17  18  19      5
10  30/09/2009  08:00       1   2   3   4   5   6   8   9   16  17      1

the data file is
Conc. N 4
Data conc. date
Ora c 5 ( the first txt I have only two numbers and to another the time complete sample 08:00
ESTR1 N 2
ESTR2 N 2
ESTR3 N 2
ESTR4 N 2
ESTR5 N 2
ESTR6 N 2
ESTR7 N 2
ESTR8 N 2
ESTR9 N 2
ESTR10 N 2
NUMERONE N 2

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 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

FiveTech Support Forums

1. As Mr James Bott advised, please SET DATE BRITISH and SET CENTURY ON at the beginning of the program. You can change the setting, if you want, only after FW_ArrayToDBF().

2. Because the CSV contains header information in the first row, you need to delete 1st Row of the array aData before writing to DBF. Please ensure that the aData's first row is really Data row.

3. Possibly the last row of aData is in incomplete array. Please delete the last row of aData also.