help for converting file

[Silvio.Falconi]

Code: Select all  Expand view

I have a file csv as this

"UserName","Work Date","Time Worked"
"Tonino","15 Dicembre 2020","06:00:00"
"","","","In","15-12-2020","08:00"
"","","","Out","15-12-2020","14:00"
"Falconi Silvio","15 Dicembre 2020","00:00:00"
"","","","In","15-12-2020","08:02"
"Falconi Silvio","14 Dicembre 2020","06:15:30"
"","","","In","14-12-2020","07:56"
"","","","Out","14-12-2020","14:11"
"Falconi Silvio","12 Dicembre 2020","00:00:00"
"","","","In","12-12-2020","08:18"
"Falconi Silvio","11 Dicembre 2020","06:03:46"
"","","","In","11-12-2020","07:58"
"","","","Out","11-12-2020","14:02"
"Falconi Silvio","10 Dicembre 2020","06:01:00"
"","","","In","10-12-2020","08:09"
"","","","Out","10-12-2020","14:10"
"","","","Out","10-12-2020","14:10"
"Falconi Silvio","9 Dicembre 2020","05:47:25"
"","","","In","9-12-2020","08:15"
"","","","Out","9-12-2020","14:03"
"Falconi Silvio","7 Dicembre 2020","06:03:25"
"","","","In","7-12-2020","07:59"
"","","","Out","7-12-2020","14:03"
"Falconi Silvio","5 Dicembre 2020","06:05:29"
"","","","In","5-12-2020","08:07"
"","","","Out","5-12-2020","14:13"
"Falconi Silvio","4 Dicembre 2020","06:05:05"
"","","","In","4-12-2020","07:59"
"","","","Out","4-12-2020","14:04"
"Falconi Silvio","3 Dicembre 2020","06:03:46"
"","","","In","3-12-2020","07:56"
"","","","Out","3-12-2020","14:00"
"Falconi Silvio","2 Dicembre 2020","05:41:17"
"","","","In","2-12-2020","08:19"
"","","","Out","2-12-2020","14:00"
"Falconi Silvio","1 Dicembre 2020","06:03:09"
"","","","In","1-12-2020","07:59"
"","","","Out","1-12-2020","14:02"
"Falconi Silvio","30 Novembre 2020","05:59:07"
"","","","In","30-11-2020","08:04"
"","","","Out","30-11-2020","14:03"

the dbf is
UserName (30C) WorkDate (d8) Worked (8C) Activity(10C) Workdate(d8) Time(8C)




on excel I have ( an sample)

[Marc Venken]

You want the CSV data into the database .or. .and. the exel to the dbf ?

[Silvio.Falconi]

I Wish save on dbf
I showed my fields

[Otto]

Hello Silvio,

I'm posting a function here that Mr. Rao gave us at the Fivewin meeting.
Actually, you should have it, because you have been at the sessions.

You must correct the header to:
"UserName","WorkDate","TimeWorked", "Field1", "Field2","Field3","Field4"

Sincerely Otto

viewtopic.php?f=3&t=33515

Code: Select all  Expand view

#include 'fivewin.ch'
#include 'xbrowse.ch'

function Main()

local cBuf
local aData, aHeaders

cBuf := MemoRead( "c:\fwh\samples\prqrcodemp4.csv" )


cBuf := STRTRAN(cBuf,",",";")
cBuf := STRTRAN(cBuf,'"','')

// parse and make a multi dimentional array
if cBuf[ -1 ] == 26 // check and remove Ctrl-Z
cBuf := Left( cBuf, Len( cBuf ) - 1 )
endif
if Right( cBuf, 2 ) != CRLF
// pad with CRLF if needed, not to miss the last line
cBuf += CRLF
endif
cBuf := StrTran( cBuf, CRLF, Chr( 10 ) )
aData := hb_aTokens( cBuf, Chr(10) )
AEval( aData, { |c,i| aData[ i ] := hb_aTokens( c, ';' ) } )    //Trennzeichen   ;'
aHeaders := aData[ 1 ]
aData := ADel( aData, 1 )
aSize( aData, Len( aData ) - 1 )

// Parsing is done

// now view the data quickly in xbrowse before writing in DBF
xBrowse( aData, ; // data to browse
"Parsed Data as Array", ; // title
.f., ; // no autosort
{ |oBrw| AEval( oBrw:aCols, { |o,i| o:cHeader := aHeaders[ i ] } ) } ;
) // codeblock above assigns header names
*/
// Extending the program to Write DBF
WriteToDBF( 'INPUT.DBF', aData, aHeaders )

// Check the DBF
USE INPUT
XBrowse( )

return nil

static function WriteToDBF( cDbf, aData, aHeaders )

local aStruct := {}
local n, nLen := Len( aData )
? cDbf
AEval( aHeaders, ;
{ |c| AAdd( aStruct, ;
{ Upper( Left( Trim( c ), 10 ) ), 'C', 2, 0 } );
} )
// desirable to remove embedded chars not acceptable in fieldnames
// and to check for duplicates
// that logic is not included here
 xbrowse(aHeaders)
for n := 1 to nLen
AEval( aData[ n ], ;
{ |c,i| c := Trim( c ), ;
aStruct[ i ][ 3 ] := Max( Len( c ), aStruct[ i ][ 3 ] ), ;
aData[ n ][ i ] := c ;
} )
next n

dbCreate( cDbf, aStruct )
USE ( cDbf ) NEW ALIAS OUT EXCLUSIVE

for n := 1 to nLen
OUT->( dbAppend() )
AEval( aData[ n ], { |c,i| OUT->( FieldPut( i, c ) ) } )
next

OUT->( dbCloseArea() )

return nil

 

[Silvio.Falconi]

thanks Otto
But I wanted the record as
0

Thanks but I wanted something simpler
type :
UserName (30C)
Activity(10C)
Workdate(d8)
Time(8C)

type
Falconi Silvio |in|15/12/2020|08:00
Falconi Silvio |out|15/12/2020|14:00

I take the csv file from the internet (from my website php) and then with the application I have already done I redo the calculation

p.s. your test on harbour not run I can compile it only with xharbour

[Silvio.Falconi]

Now I correct the export csv from web (php)

Code: Select all  Expand view

"UserName","Activity","Work Date","Time Worked"
"Falconi Silvio","In","16-12-2020","14:01"
"Tonino","In","15-12-2020","08:00"
"Tonino","Out","15-12-2020","14:00"
"Falconi Silvio","In","15-12-2020","08:02"
"Falconi Silvio","Out","15-12-2020","14:05"
"Falconi Silvio","In","14-12-2020","07:56"
"Falconi Silvio","Out","14-12-2020","14:11"
"Falconi Silvio","In","12-12-2020","08:18"
"Falconi Silvio","In","11-12-2020","07:58"
"Falconi Silvio","Out","11-12-2020","14:02"
"Falconi Silvio","In","10-12-2020","08:09"
"Falconi Silvio","Out","10-12-2020","14:10"
"Falconi Silvio","Out","10-12-2020","14:10"
"Falconi Silvio","In","9-12-2020","08:15"
"Falconi Silvio","Out","9-12-2020","14:03"
"Falconi Silvio","In","7-12-2020","07:59"
"Falconi Silvio","Out","7-12-2020","14:03"
"Falconi Silvio","In","5-12-2020","08:07"
"Falconi Silvio","Out","5-12-2020","14:13"
"Falconi Silvio","In","4-12-2020","07:59"
"Falconi Silvio","Out","4-12-2020","14:04"
"Falconi Silvio","In","3-12-2020","07:56"
"Falconi Silvio","Out","3-12-2020","14:00"
"Falconi Silvio","In","2-12-2020","08:19"
"Falconi Silvio","Out","2-12-2020","14:00"
"Falconi Silvio","In","1-12-2020","07:59"
"Falconi Silvio","Out","1-12-2020","14:02"
"Falconi Silvio","In","30-11-2020","08:04"
"Falconi Silvio","Out","30-11-2020","14:03"

perhaps it could be easier to process