CSV to DBF

[Silvio.Falconi]

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

[cnavarro]

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

[avista]

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: Select all  Expand view

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

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]

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: Select all  Expand view

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.

[Silvio.Falconi]

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: Select all  Expand view

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: Select all  Expand view

#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: Select all  Expand view

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 ?

[James Bott]

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

[nageswaragunupudi]

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.

[nageswaragunupudi]

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: Select all  Expand view

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

[Silvio.Falconi]

Thanks,
It run also with xharbour ?

[nageswaragunupudi]

Thanks,
It run also with xharbour ?

Yes.
Keep the include "hbcompat.ch" always

[Silvio.Falconi]

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: Select all  Expand view

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!!!

[nageswaragunupudi]

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: Select all  Expand view

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

[Silvio.Falconi]

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

[nageswaragunupudi]

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

[Silvio.Falconi]

I have a file in this type

Code: Select all  Expand view

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: Select all  Expand view

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