topic-41061

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

CSV TO DBF
Posted: Sat Nov 13, 2021 12:55 PM

I must convert this into dbf

CodUM,DescrUM,Molt,NumDec,Tipo,CodDescrUM
CF10,Confezione 10 pezzi,10,0,Numero,CF10 - Confezione 10 pezzi
CF2,Confezione 2 pezzi,2,0,Numero,CF2 - Confezione 2 pezzi
CF20,Confezione 20 pezzi,20,0,Numero,CF20 - Confezione 20 pezzi
CF24,Confezione 24 pezzi,24,0,Numero,CF24 - Confezione 24 pezzi
CF3,Confezione 3 pezzi,3,0,Numero,CF3 - Confezione 3 pezzi
CF4,Confezione 4 pezzi,4,0,Numero,CF4 - Confezione 4 pezzi
CF5,Confezione 5 pezzi,5,0,Numero,CF5 - Confezione 5 pezzi
CF6,Confezione 6 pezzi,6,0,Numero,CF6 - Confezione 6 pezzi
KG,Chilogrammi,1,3,Peso,KG - Chilogrammi
LT,Litri,1,3,Volume,LT - Litri
MC,Metri cubi,1,3,Volume,MC - Metri cubi
MQ,Metri quadri,1,3,Lunghezza,MQ - Metri quadri
MT,Metri,1,3,Lunghezza,MT - Metri
NR,Numero,1,0,Numero,NR - Numero
PZ,Pezzi,1,0,Numero,PZ - Pezzi

Any solution ?
the filed are on first line

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

FranciscoA

Posts: 2170

Joined: Fri Jul 18, 2008 01:24 AM

Re: CSV TO DBF
Posted: Sat Nov 13, 2021 07:09 PM

Here an example that you can modify.

Code (fw): Select all Collapse

#include "fivewin.ch"
Function Main()    //CsvToArrToDbf()    
  Local aArray := {}, aData := {}, aStruct := {}
  Local cTxtFile := MemoRead(".\CsvDbf.csv")    //your csv
  local nLines   := MlCount(cTxtFile)
  LOCAL n, aCols, aTypes, cSep := ",", nIniDataRow := 1, n2

  //Tipos al que serán convertidos (todos traen "C" por ser archivo de texto)
  aTypes := {"C","C","N","N","C","C"}

 //Si la primera linea contiene los headers
  aCols := HB_ATokens( Trim(Memoline(cTxtFile, 254, 1 )), cSep )
  nIniDataRow := 2  //Lin en que comienzan los datos

  For n := 1 to len(aCols)
     aadd( aStruct, { aCols[n], aTypes[n], 20, 0 } )
  Next
  DBCREATE( ".\CSVDBF", aStruct )
  DbUseArea(.t.,,".\CSVDBF", "CSVDBF", .f.)
  DbSelectArea("CSVDBF")

  FOR n := nIniDataRow TO nLines
     aArray := HB_ATokens( Trim(Memoline(cTxtFile, 254, n )), cSep ) 
     if !Empty(aArray)
        AEval( aArray, {|a,n| aArray[n] := ConvertType(aArray[n], aTypes[n]) } )
        DbAppend()
        For n2 := 1 to len(aArray) 
           FieldPut( n2, aArray[n2] )
        Next
     endif
  NEXT

  XBROWSER "CSVDBF"
  CSVDBF->(dbcloseArea())
  FERASE(".\CSVDBF.DBF")

Return nil

Francisco J. Alegría P.

Chinandega, Nicaragua.

Fwxh-MySql-TMySql

rhlawek

Posts: 195

Joined: Sun Jul 22, 2012 07:01 PM

Re: CSV TO DBF
Posted: Sun Nov 14, 2021 07:18 PM

APPEND FROM <sourcefile> DELIMITED WITH ","

You still need to deal with removing the first row of the csv which has the field names, which is simple enough.

Silvio.Falconi

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

Re: CSV TO DBF
Posted: Mon Nov 15, 2021 08:41 AM

FranciscoA wrote:Here an example that you can modify.

Code (fw): Select all Collapse

#include "fivewin.ch"
Function Main()    //CsvToArrToDbf()    
  Local aArray := {}, aData := {}, aStruct := {}
  Local cTxtFile := MemoRead(".\CsvDbf.csv")    //your csv
  local nLines   := MlCount(cTxtFile)
  LOCAL n, aCols, aTypes, cSep := ",", nIniDataRow := 1, n2

  //Tipos al que serán convertidos (todos traen "C" por ser archivo de texto)
  aTypes := {"C","C","N","N","C","C"}

 //Si la primera linea contiene los headers
  aCols := HB_ATokens( Trim(Memoline(cTxtFile, 254, 1 )), cSep )
  nIniDataRow := 2  //Lin en que comienzan los datos

  For n := 1 to len(aCols)
     aadd( aStruct, { aCols[n], aTypes[n], 20, 0 } )
  Next
  DBCREATE( ".\CSVDBF", aStruct )
  DbUseArea(.t.,,".\CSVDBF", "CSVDBF", .f.)
  DbSelectArea("CSVDBF")

  FOR n := nIniDataRow TO nLines
     aArray := HB_ATokens( Trim(Memoline(cTxtFile, 254, n )), cSep ) 
     if !Empty(aArray)
        AEval( aArray, {|a,n| aArray[n] := ConvertType(aArray[n], aTypes[n]) } )
        DbAppend()
        For n2 := 1 to len(aArray) 
           FieldPut( n2, aArray[n2] )
        Next
     endif
  NEXT

  XBROWSER "CSVDBF"
  CSVDBF->(dbcloseArea())
  FERASE(".\CSVDBF.DBF")

Return nil

this function run ok onlyu when I found a csv have logic fields the function go out at line 16
a question ...

//Tipos al que serán convertidos (todos traen "C" por ser archivo de texto)
aTypes := {"C","C","N","N","C","C"}

I must set the field type ?

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

FranciscoA

Posts: 2170

Joined: Fri Jul 18, 2008 01:24 AM

Re: CSV TO DBF
Posted: Mon Nov 15, 2021 01:27 PM

You can use field types, if you want it.
In the following example all fields are character type

Code (fw): Select all Collapse

#include "fivewin.ch"

Function Main()  
  Local aArray := {}, aData := {}, aStruct := {}
  Local cTxtFile := MemoRead(".\CsvDbf.csv")
  local nLines   := MlCount(cTxtFile)
  LOCAL n, aCols,  cSep := ",", nIniDataRow := 1

  aCols := HB_ATokens( Trim(Memoline(cTxtFile, 254, 1 )), cSep )
  nIniDataRow := 2

  For n := 1 to len(aCols)
     aadd( aStruct, { aCols[n], "C", 20, 0 } )
  Next
  DBCREATE( ".\CSVDBF", aStruct )
  DbUseArea(.t.,,".\CSVDBF", "CSVDBF", .f.)
  DbSelectArea("CSVDBF")
  
  FOR n := nIniDataRow TO nLines
     aArray := HB_ATokens( Trim(Memoline(cTxtFile, 254, n )), cSep ) 
     if !Empty(aArray)
        DBAppend()            
        AEval( aArray, {|a,n| FieldPut(n, aArray[n]) } )
     endif
  NEXT

  XBROWSER "CSVDBF"

  CSVDBF->(DbCloseArea())
  FERASE(".\CSVDBF.DBF")

Return nil

Francisco J. Alegría P.

Chinandega, Nicaragua.

Fwxh-MySql-TMySql

FiveTech Support Forums

Continue the discussion