help for converting file

Post Reply
User avatar
Silvio.Falconi
Posts: 4956
Joined: Thu Oct 18, 2012 7:17 pm

help for converting file

Post by Silvio.Falconi »

Code: Select all

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)
Image
I use : FiveWin for Harbour August 2020 (Revision) - Harbour 3.2.0dev (r1712141320) - Bcc7.30 - xMate ver. 1.15.3 - PellesC
User avatar
Marc Venken
Posts: 727
Joined: Tue Jun 14, 2016 7:51 am

Re: help for converting file

Post by Marc Venken »

You want the CSV data into the database .or. .and. the exel to the dbf ?
Marc Venken
Using: FWH 20.08 with Harbour
User avatar
Silvio.Falconi
Posts: 4956
Joined: Thu Oct 18, 2012 7:17 pm

Re: help for converting file

Post by Silvio.Falconi »

I Wish save on dbf
I showed my fields
I use : FiveWin for Harbour August 2020 (Revision) - Harbour 3.2.0dev (r1712141320) - Bcc7.30 - xMate ver. 1.15.3 - PellesC
User avatar
Otto
Posts: 4470
Joined: Fri Oct 07, 2005 7:07 pm
Contact:

Re: help for converting file

Post by 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

http://forums.fivetechsupport.com/viewt ... =3&t=33515

Image

Code: Select all

#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 

 
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org

********************************************************************
User avatar
Silvio.Falconi
Posts: 4956
Joined: Thu Oct 18, 2012 7:17 pm

Re: help for converting file

Post by 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
I use : FiveWin for Harbour August 2020 (Revision) - Harbour 3.2.0dev (r1712141320) - Bcc7.30 - xMate ver. 1.15.3 - PellesC
User avatar
Silvio.Falconi
Posts: 4956
Joined: Thu Oct 18, 2012 7:17 pm

Re: help for converting file

Post by Silvio.Falconi »

Now I correct the export csv from web (php)

Code: Select all

"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

Image
I use : FiveWin for Harbour August 2020 (Revision) - Harbour 3.2.0dev (r1712141320) - Bcc7.30 - xMate ver. 1.15.3 - PellesC
Post Reply