Prestashop Mapping solution

[Marc Venken]

Hello,

"Prestashop is a popular free shopcart system"

I created some data from dbf's to csv for import into Prestashop modules 'import'. That works.

Now I want to open a dbf on 1 side and a csv on the otherside and be able to link the files togetter with the field.

Dbf CSV

Field A Data1
Field B Data2
Field C Data3

example :
The data1 should go in field C, Data2 in Field A,.....

I believe it is called MAPPING fields.

Any part of source to show how I should proceed.

The csv are random in structure, since I get them from my brands and they contain many field.
I want to populate 10's of CSV into 1 dbf file

Regards
Marc Venken

[James Bott]

Marc,

Does the first record of the CSV files, contain the fieldnames?

What do you mean when you say you want to link the DBF and CVS files? Do you want to import the files into a standard DBF format?

You say the CSV files are random in structure. Do you mean they contain the same data but it might be in a different order? Or?

James

[Marc Venken]

James,

The first record from the csv has indeed always a header. I'm able to convert a csv file to a dbf but I want 1 master dbf file where all the csv-files will be imported.

The csv files contains different data (every supplier has it own structure data) but I want to import them in a correct field in the dbf.
Mostly the csv have easy 20-80 fields.

Dbf Master Csv1 Csv2 Csv3

1 ArtNr 1 Code 1 ID 1 Art
2 Naam 2 color 2 Naam 2 Name
3 Price 3 prijs 3 Name 3 quant
4 Tax 4 Naam 4 data 4 Price
....
sure 50 fields in dbf

so Csv1 1 > 1 , 2 not used , 3 > 3, 4 >2
Csv2 1 > 1, 2 > 2, 3 not used, 4 not used
Csv3 1 > 1, 2 > 2, 3 not used , 4 > 3

The csv files will never have the same structure as the dbf, but I will import the relevant data and add some standard data (like Tax 21%) when not in the csv.

Maybe a Xbrowse with the content off the fields

The Picture : (Import module from Prestashop addons)

Left are the CSV headers to see and right we can select with field in the dbf to use as target.

Regards,
Marc

[James Bott]

Marc,

OK, I am not clear, but it sounds like they all have the same structure, but some of the fields may not be used. If so, this simplifies things a lot.

First you need to import each file into a temp DBF. The temp DBF needs to be the same structure as the master DBF. Substitute the brackets with the file names you wish to use.

USE [MASTER DBF]
COPY STRUCTURE TO [TEMP DBF]
USE [TEMP DBF]
APPEND FROM [CVS FILE] DELIMITED

Then you have to delete the first record since it will be the header record. This is the only reason you need a temp DBF.

GO TOP
DELETE
USE

Then append the temp DBF to the master DBF.

USE [MASTER DBF]
APPEND FROM [TEMP DBF] FOR .NOT. DELETED

That's it.

OK, if my assumptions are wrong, let me know.

James

[James Bott]

Marc,

I have re-read your posts and now I think I have misunderstood.

It seems that each CSV file has different fieldnames and in a different order than the master file. If so, then as you stated, you will either need to restructure each file before importing it, or you will need a separate routine that processes each CSV file one record at a time. This will require a fair amount of work.

I would probably start by figuring out how to identify each type of CSV file and then give it an ID no.

Then use the ID no. to determine how to process the data. Rather than writing code for each type, I would attempt to map each field (e.g. field 5 in the CSV goes into field 7 of the master DBF). You can store all the mappings in the same DBF so you don't have to write code for each CSV, you just add data to the file. Then you do write code to process the CSVs according to the data in the conversion DBF.

You may also want to create temp DBFs with the structure needed to import the CSV files. This way you can create a temp dbf for the CSV, then import the CSV to the temp DBF, then use that DBF to process the data. Come to think of it, if you use the standard DBF fieldnames in the temp DBFs, then you don't need code to import the data. Just delete the first record (the header) and append the temp DBF to the master DBF. If I remember correctly, the fields in the master and temp DBFs don't need to be in the same order, they just need to have the same fieldnames. If so, then you don't need any code to import the temp DBFs.

Things to try:
1) First use low-level file commands to read the header of the CSV file, then use that to find the file-type. (Maybe use a checksum of the header)
2) Use that file-type to determine which DBF structure to use. Store the structures in a database. (All structures use standard fieldnames)
3) Create a temp DBF using the structure
4) Open the temp DBF, append the CSV, open the temp DBF, delete the first record, close the DBF
5) Open the master DBF, append from the temp DBF (using FOR ! DELETED), delete the temp DBF.
6) Done

I do love a challenge!

James

[James Bott]

Here is a checksum() function you can use:

Code: Select all  Expand view

Function checksum( cString )
   Local nChecksum:=0, i:=0
   for I = 1 to len( cString )
      nChecksum:= nChecksum + asc(substr(cString,i,1))
   next
Return nChecksum

 

James

[James Bott]

Here is a function to read the header record of a CSV file.

Code: Select all  Expand view

// Get first record of CSV file (contains fields)
Function GetHeader(cFile)
   Local cBuffer:= space(1000)
   Local cHeader:=""
   Local nHandle:=0
   if file(cFile)
      nHandle:= fopen(cFile,0)
      cHeader:= freadstr( nHandle, len(cBuffer) )
      cHeader:= left(cHeader,at( chr(13)+chr(10),cHeader ) )
      fclose(cFile)
   else
      msgInfo("File "+cFile+" not found.")
   endif
return cHeader
 

[Marc Venken]

James,

I have this code so far : (Code snippeds from samples and forum)

Program Menu 1 :

CSV conversion from any csv to dbf file. First loop to determine the lenght of all data, the second to fill the database
Limits : ONLY DELIMITED WITH ',' will work now (for my use ok for now)

chekups from missing csv file or other delimited stuff enz... will be later if needed

Program Menu 2 : (from samples)

This with use any selected dbf file in the current dir and create a master dbf for the sample to work.
Then it will show a Xbrowse with the left (MASTER.DBF) and right the selected dbf.
Both will show there own structure and 1 data field.

Thats how far I'm.

Now how to Map the 2 dbf's ?? I've not seen yet a Xbrowse sample where maybe a drag and drop can be used or we make a extra column and put the field nummers into it ?

I'm thinking....

Marc

Code: Select all  Expand view

#include "FiveWin.ch"
#include "report.ch"
#include "hbcompat.ch"
#include 'ord.ch'
#include "XBrowse.Ch"
#include "fileio.ch"


REQUEST DBFCDX

#define WID  600
#define HGT  350

FUNCTION Main


RDDSetDefault( "DBFCDX" )
set( 4, "mm/dd/yyyy" )
set( 5, 1930 )
SET DATE BRITISH
set deleted on
SetHandleCount( 100 )
setkey( VK_F12,{|| Dbfsopen() } )



   SET 3DLOOK ON

   DEFINE BRUSH oBrush STYLE TILED       // FiveWin new predefined Brushes

   DEFINE WINDOW oWnd TITLE "F A C T U M A T" MENU BuildMenu() BRUSH oBrush

   DEFINE BUTTONBAR oBar OF oWnd

   DEFINE FONT oDlFont NAME 'Courier New' SIZE 8,15


   SET MESSAGE OF oWnd TO "Version 07.07.16 : "

   ACTIVATE WINDOW oWnd MAXIMIZED

   oWnd:end()
   oFont:end()
   //oInitrep:end()
   dbcloseall()
   //freelibrary(hBor)
   //SET RESOURCES TO
return nil

function BuildMenu()

   //Local aMenu:= validMenu()
   local oMenu

   MENU oMenu
      MENUITEM "&Csv Transform" ACTION csvtransform()
      MENUITEM "&Dbf open" action dbf_open()
   ENDMENU

return oMenu

function csvtransform()
LOCAL hCsv, cLine, aLine, nI := 0,aFiles[ADIR("*.csv")]

ADIR("*.csv", aFiles)

nFile = msglist(aFiles )
cFile = aFiles[nFile]
cDbf = STRTRAN(cFile, ".csv", ".dbf")

//  Calculate the max field lengt
//  Caution : If there are CDX-files, dbf ceation will faile !!  erase them first of code it...

IF ( hCsv := fOpen( cFile , 16 ) ) > 0
    HB_FReadLine( hCsv, @cLine, chr( 10 ) )
    aHeader = strtoarr(cLine)
    nlenarray = len(aHeader)
    aFieldcount = array(nLenarray)
    afill(aFieldcount,1)
    nTester = 1
    nTeller = 1
    WHILE HB_FReadLine( hCsv, @cLine, chr( 10 ) ) == 0
//      oWnd:SetMsg( "Process data "+str(nTeller++))  // Show progress, but slows down on large files

      FOR I := 1 TO nLenarray

      //cLine := subStr( cLine, at( ["], cLine ) + 1 )
      //cStr =  subStr( cLine, 1, at( ["], cLine ) - 1 )
      //nlengte = len(alltrim(cStr))


      if I < nLenarray
        cStr = subStr( cLine, 1, at( [,], cLine ) - 1 )
        cStr = STRTRAN(cStr, '"', '')
      else
        cStr = STRTRAN(cLine, '"', '')
      endif

      nlengte = len(cStr)

      if aFieldcount[ I ] < nLengte
         afieldcount[i] = nLengte
      endif

      cLine := subStr( cLine, at( [,], cLine ) + 1 )

      //cLine := subStr( cLine, at( ["], cLine ) + 1 )


      NEXT

    ENDDO
    builddbf(aHeader,aFieldcount,cDbf)
    fClose( hCsv )
ELSE
    alert( "BAD LUCK" )
    return
ENDIF
fClose( hCsv )
close all

// fill de database

USE &cDbf NEW EXCL ALIAS HUNT

IF ( hCsv := fOpen( cFile, 16 ) ) > 0
    WHILE HB_FReadLine( hCsv, @cLine, chr( 10 ) ) == 0
        //oWnd:SetMsg( "Process data "+str(nTeller--))  // Show progress, but slows down on large files
        nI ++
        IF nI > 1
            hunt->( dbAppend() )
            FillFields( cLine )
        ENDIF
    ENDDO
    fClose( hCsv )
    alert( str( nI ) )
ELSE
    alert( "BAD LUCK 2" )
    return
ENDIF
xbrowse()
close all
RETURN NIL


STATIC FUNCTION FillFields( cLine )
LOCAL nJ

nMax = hunt->( fCount() )
FOR nJ := 1 TO nMax
    //cLine := subStr( cLine, at( ["], cLine ) + 1 )
    //hunt->( FieldPut( nJ, subStr( cLine, 1, at( ["], cLine ) - 1 ) ) )
    //cLine := subStr( cLine, at( ["], cLine ) + 1 )


    if nJ < nMax
      cStr = subStr( cLine, 1, at( [,], cLine ) - 1 )
      cStr = STRTRAN(cStr, '"', '')
      hunt->( FieldPut( nJ, cStr ))
      cLine := subStr( cLine, at( [,], cLine ) + 1 )
    else
      cStr = STRTRAN(cLine, '"', '')
      hunt->( FieldPut( nJ, cStr ))  // Process last item, regel
    endif

NEXT
RETURN NIL

function strtoArr(cMaat)
   Local aTemp:={}
   if !empty(cMaat)
     do while at(",",cMaat) > 0
        AADD(aTemp,substr(cMaat,1,at(",",cMaat)-1))
        cMaat = substr(cMaat,at(",",cMaat)+1)
     enddo
     AADD(aTemp,alltrim(cMaat))
   endif
return aTemp

function Builddbf(adata,Alengte,cDbf)
   LOCAL aStru := {}

//   msginfo(atostr(adata))

   for i = 1 to len(adata)
     cField = STRTRAN(aData[i], '"', '')
     aAdd( aStru, { cField , "C", alengte[i]+1 , 0 } )
   next
   dbCreate( cDbf , aStru )
return

Static Function AToStr( aArray )

   Local cStr := "", nI, nLen := Len( aArray )

   For nI := 1 To nLen
      cStr += AllTrim( aArray[nI] ) + If( nI < nLen, ", ", "" )
   Next


Return cStr


function show_data(cDbf)
  USE &cDbf NEW EXCL ALIAS DATA
  xbrowse()
  close all
return


Function XbrShowSizes( oBrw )

   XBrowse( ArrTranspose( { oBrw:cHeaders, oBrw:nWidths } ), nil, nil, { |o| o:cHeaders := { "Header", "Width" } } )

return nil

function DbfsOpen()
  Local aLocal := {}
   for n = 1 to 255
      if ! Empty( Alias( n ) )
         AADD( aLocal,Str( n, 3 ) + ": " + If( Select() == n,"=> ", "   " ) +  PadR( Alias( n ), 15 ) )
      endif
      for j = 1 to 15
        if ! Empty( ( Alias( n ) )->( IndexKey( j ) ) )
           AADD( aLocal,"    "+ ( Alias( n ) )->( OrdName( j ) ) +" -> "+ PadR( ( Alias( n ) )->( IndexKey( j ) ), 35 ) )
        endif
     next
   next
   cTemp = MsgSelect( aLocal )
   sysrefresh()
return

function MsgSelect( aItems, cValue, cTitle )

   local oDlg

   DEFINE FONT oFont NAME "Courier New" SIZE 8,15

   DEFAULT cTitle := "Maak uw keuze"

   DEFINE DIALOG oDlg FROM 5,10 TO 24, 95 font oFont TITLE cTitle

   @  1, 2 LISTBOX cValue ITEMS aItems SIZE 305, 110  OF oDlg

   @ 7, 05 BUTTON "&OK"     OF oDlg SIZE 40, 12  ACTION oDlg:End() DEFAULT
   @ 7, 12 BUTTON "&Cancel" OF oDlg SIZE 40, 12  ACTION ( cValue := "", oDlg:End() )

   ACTIVATE DIALOG oDlg CENTERED

return cValue

function buildmasterdbf()

   aStruct := {}
   AADD(aStruct, { "ID"      , "C", 10, 0 }) // Id
   AADD(aStruct, { "Aktief"  , "N",  1, 0 }) // Aktief (0/1)
   AADD(aStruct, { "Naam"    , "C", 50, 0 }) // Naam *
   AADD(aStruct, { "Cat_main", "C", 50, 0 }) // Categorieën (x,y,z...)
   AADD(aStruct, { "Cat_Sub1", "C", 50, 0 }) // Categorieën (x,y,z...)
   AADD(aStruct, { "Prijs"   , "N",  9, 2 }) // Prijs excl. Btw of Prijs incl. Btw
   AADD(aStruct, { "Tax"     , "C",  1, 0 }) // Tax rules id                                 // NO USED
   AADD(aStruct, { "gr_prijs", "C",  1, 0 }) // Groothandelsprijs // NOT
   AADD(aStruct, { "offer"   , "C",  1, 0 }) // Aanbieding (0/1)  // NOT
   AADD(aStruct, { "dis_val" , "C",  1, 0 }) // Discount amount // NOT
   AADD(aStruct, { "dis_per" , "C",  1, 0 }) // Discount percent  // NOT
   AADD(aStruct, { "dis_from", "C",  1, 0 }) // Discount from (yyyy-mm-dd) // NOT
   AADD(aStruct, { "dis_to  ", "C",  1, 0 }) // Discount to (yyyy-mm-dd)  // NOT
   AADD(aStruct, { "Art_Code", "C",  8, 0 }) // Referentie
   AADD(aStruct, { "Art_Lev" , "C", 20, 0 }) // Referentie leverancier
   AADD(aStruct, { "Leveranc", "C", 18, 0 }) // Leverancier  // NOT
   AADD(aStruct, { "Fabrikan", "C", 18, 0 }) // Fabrikant
   AADD(aStruct, { "Ean"     , "C",  1, 0 }) // EAN13        // NOT
   AADD(aStruct, { "Upc"     , "C",  1, 0 }) // UPC          // NOT
   AADD(aStruct, { "Milieu"  , "C",  1, 0 }) // Milieuheffing   // NOT
   AADD(aStruct, { "Gewicht" , "C",  1, 0 }) // Gewicht         // NOT
   AADD(aStruct, { "Aantal"  , "N",  3, 0 }) // Aantal
   AADD(aStruct, { "Kort_txt", "C", 50, 0 }) // Korte omschrijving
   AADD(aStruct, { "Memo"    , "C",255, 0 }) // Omschrijving
   AADD(aStruct, { "Tags"    , "C",  1, 0 }) // Tags (x,y,z...)
   AADD(aStruct, { "Meta_tit", "C",  1, 0 }) // Meta titel
   AADD(aStruct, { "Meta_zoe", "C",  1, 0 }) // Meta zoekwoorden
   AADD(aStruct, { "Meta_inf", "C",  1, 0 }) // Meta omschrijving
   AADD(aStruct, { "Url_rewr", "C",  1, 0 }) // URL rewritten
   AADD(aStruct, { "Msg_sto" , "C",  1, 0 }) // Bericht indien op voorraad
   AADD(aStruct, { "Msg_nsto", "C",  1, 0 }) // Bericht indien niet op voorraad
   AADD(aStruct, { "Url_pic" , "C",150, 0 }) // URLs afbeelding (x,y,z...)
   AADD(aStruct, { "Optie"   , "C",  1, 0 }) // 1= foto wissen bij upload
   AADD(aStruct, { "Beschikb", "C",  1, 0 }) // Only available online
   AADD(aStruct, { "Kleur"   , "C", 30, 0 }) // Only available online
   AADD(aStruct, { "Size"    , "C", 10, 0 }) // Only available online

   DbCreate( "master.dbf", aStruct)
   use master
   append blank
   replace id with "5101000"
   replace aktief with 1
   replace naam with "The Name of the artikel"
   replace cat_main with "Main Menu"
   replace cat_Sub1 with "Sub Menu"
   //  All fields in order to give a sample of the data needed
   close all
return


FUNCTION dbf_open()

   local oDlg, oData, oStru, oFont, aDbf[ADIR("*.dbf")]

   Buildmasterdbf()  // only for testing.

   // Select a random dbf to Map/link to the master

   ADIR("*.dbf", aDbf)
   nFile = msglist(aDbf )
   cDbf = aDbf[nFile]

   USE master NEW ALIAS MASTER
   if Select( "MASTER" ) == 0
      MsgStop( "Can not open MasterFile.DBF" )
      return 1
   endif

   USE &cDbf NEW ALIAS CUST
   if Select( "CUST" ) == 0
      MsgStop( "Can not open File.DBF" )
      return 1
   endif

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-12

   DEFINE DIALOG oDlg SIZE 2*WID,2*HGT PIXEL FONT oFont

   @ 10,10 XBROWSE oData SIZE 200,320 PIXEL OF oDlg ;
      DATASOURCE MASTER->( DbStruct() ) ;
      COLUMNS 1,2,3,4, ;
      { |x| If( x == nil, MASTER->( FieldGet( oData:nArrayAt ) ), ;
         MASTER->( If( DbRLock(), ( FieldPut( oData:nArrayAt, x ), DbRUnLock() ), nil ) ) ) } ;
      HEADERS "FldName","Typ","Len","Dec","FieldValue" ;
      COLSIZES 120,40,40,40,200 ;
      AUTOCOLS CELL LINES

   @ 10,220 XBROWSE oStru SIZE -10,-10 PIXEL OF oDlg ;
      DATASOURCE CUST->( DbStruct() ) ;
      COLUMNS 1,2,3,4, ;
      { |x| If( x == nil, CUST->( FieldGet( oStru:nArrayAt ) ), ;
         CUST->( If( DbRLock(), ( FieldPut( oStru:nArrayAt, x ), DbRUnLock() ), nil ) ) ) } ;
      HEADERS "FldName","Typ","Len","Dec", "FieldValue" ;
      COLSIZES 120,40,40,40,400 ;
      AUTOCOLS CELL LINES

   WITH OBJECT oData
      :bClrSel          := { || { CLR_BLACK, CLR_HGRAY }}
      :bClrEdits        := { || { CLR_BLACK, CLR_YELLOW }}
      //:lColChangeNotify := .t.
//      :bChange          := { || oStru:nArrayAt := oData:SelectedCol():nCreationOrder, //oStru:Refresh() }

      //:nEditTypes       := EDIT_GET
      //:bOnChanges       := { || oStru:Refresh() }
      //
      :CreateFromCode()
   END

   WITH OBJECT oStru
      :bClrSel          := { || { CLR_BLACK, CLR_HGRAY }}
 //     :bChange          := { || oData:SelectCol( oData:ColPos( oData:oCol( oStru:nArrayAt ) ), .t. ) }

 //     :nStretchCol      := 5
      //
      WITH OBJECT :aCols[ 5 ]
         //:nEditType     := EDIT_GET
         :bClrEdit      := { || { CLR_BLACK, CLR_YELLOW }}
         //:bOnChange     := { || oData:RefreshCurrent() }
      END
      //
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont
   close all
return 0


 

[James Bott]

Marc,

Questions:

1) How many types of CSV files do you have?

2) Will there be lots more types you don't have yet?

3) Who is going to be using this program; lots of users in lots of locations, one admin, or?

Knowing the answers to these questions helps to determine the best approach to building a good working interface. If you (personally) have to map 10 CSV file types once that would different than dozens of users in lots of locations having to this on a daily basis.

On a different topic:

I have confirmed that you can append a DBF to another when the fieldnames are in a different order. So, really all you need to do is change the CSV fieldnames to the standard ones in the master DBF (still using the order in which the fields occur in the CSV file). This could be done simply by replacing the header file.

Read the header and find the checksum.
Lookup the new replacement header
Write the new header to a temp file
Copy all the records (except the old header) into the new file.
Append the new file to the master DBF

If there are only a few CSV file types (and few to no new types can be expected) and only one person is going to need to do this, then I am not sure I would spend much time writing code to do this. You could probably do this on paper.

If there are lots of file types (and more are expected) and lots of people are going to be doing this, then yes, I would write code.

For mapping fields on screen, I would suggest two side-by-side browses both with two fields, field-order and field-name. The master file would be displayed on the left and the CSV file on the right. The master file's fields would be in natural order with the field numbers 1 through whatever, and the CSV browse would have empty fields for the field number and the fieldnames. The user could scroll through the master file looking for the master fieldname that matches the fieldname in the CSV, then type in the master field number in the field number of the CSV. When done, the data is used to create new header for the CSV file which contains all the proper fieldnames of the master file. Then the CSV is converted to a different CSV with the new header record as shown above. Finally, the new CSV is imported to the master DBF.

You might be able to automate some of this by scanning the CSV fieldnames for ones that match the master file, then posting the field number of the master file into the field number of the CSV, then displaying the two on screen for the user to fill in the rest.

Once you have the new header format, you can save this in a memo field and the checksum of the original header in another field. Then when you get a new CSV file you just read the checksum of the header and lookup the new header for that checksum.

James

[Marc Venken]

James,

There will be more en more csv files in the future, and they will change a lot !

I'm using it just for my personel use in order to get to know FWin and more of it's functions. I'ts a learning process ...

So, now i'm able to have a master file to the left and a csv file to the right. On the left browse, there is a button on the last field. When I click on it, it will show me all the field from the csv file and I can select it from the list. The record in the master file is updated than and I color it red on the button list, so that i know the field has already been selected.

So that is working...

Now I have a master file like this :

fields in master file (ART_ID and NAME_ID) are the fieldnames from the csv file and need to give his data to ID en NAME

ID ART_ID
NAme NAME_ID

I need to make the DBF loop where the data of all record from field Art_ID will go to ID and NAME_ID to Name.

Update file :

Code: Select all  Expand view

#include "FiveWin.ch"
#include "report.ch"
#include "hbcompat.ch"
#include 'ord.ch'
#include "XBrowse.Ch"
#include "fileio.ch"


REQUEST DBFCDX

#define WID  600
#define HGT  350

FUNCTION Main


RDDSetDefault( "DBFCDX" )
set( 4, "mm/dd/yyyy" )
set( 5, 1930 )
SET DATE BRITISH
set deleted on
SetHandleCount( 100 )
setkey( VK_F12,{|| Dbfsopen() } )



   SET 3DLOOK ON

   DEFINE BRUSH oBrush STYLE TILED       // FiveWin new predefined Brushes

   DEFINE WINDOW oWnd TITLE "F A C T U M A T" MENU BuildMenu() BRUSH oBrush

   DEFINE BUTTONBAR oBar OF oWnd

   DEFINE FONT oDlFont NAME 'Courier New' SIZE 8,15


   SET MESSAGE OF oWnd TO "Version 22.10.16 : "

   ACTIVATE WINDOW oWnd MAXIMIZED

   oWnd:end()
   oFont:end()
   //oInitrep:end()
   dbcloseall()
   //freelibrary(hBor)
   //SET RESOURCES TO
return nil

function BuildMenu()

   //Local aMenu:= validMenu()
   local oMenu

   MENU oMenu
      MENUITEM "&Csv Transform" ACTION csvtransform()
      MENUITEM "&Mapping data" action master()
   ENDMENU

return oMenu

function csvtransform()
LOCAL hCsv, cLine, aLine, nI := 0,aFiles[ADIR("*.csv")]

ADIR("*.csv", aFiles)

nFile = msglist(aFiles )
cFile = aFiles[nFile]
cDbf = STRTRAN(cFile, ".csv", ".dbf")

//  Calculate the max field lengt
//  Caution : If there are CDX-files, dbf ceation will faile !!  erase them first of code it...

IF ( hCsv := fOpen( cFile , 16 ) ) > 0
    HB_FReadLine( hCsv, @cLine, chr( 10 ) )
    aHeader = strtoarr(cLine)
    nlenarray = len(aHeader)
    aFieldcount = array(nLenarray)
    afill(aFieldcount,1)
    nTester = 1
    nTeller = 1
    WHILE HB_FReadLine( hCsv, @cLine, chr( 10 ) ) == 0
//      oWnd:SetMsg( "Process data "+str(nTeller++))  // Show progress, but slows down on large files

      FOR I := 1 TO nLenarray

      //cLine := subStr( cLine, at( ["], cLine ) + 1 )
      //cStr =  subStr( cLine, 1, at( ["], cLine ) - 1 )
      //nlengte = len(alltrim(cStr))


      if I < nLenarray
        cStr = subStr( cLine, 1, at( [,], cLine ) - 1 )
        cStr = STRTRAN(cStr, '"', '')
      else
        cStr = STRTRAN(cLine, '"', '')
      endif

      nlengte = len(cStr)

      if aFieldcount[ I ] < nLengte
         afieldcount[i] = nLengte
      endif

      cLine := subStr( cLine, at( [,], cLine ) + 1 )

      //cLine := subStr( cLine, at( ["], cLine ) + 1 )


      NEXT

    ENDDO
    builddbf(aHeader,aFieldcount,cDbf)
    fClose( hCsv )
ELSE
    alert( "BAD LUCK" )
    return
ENDIF
fClose( hCsv )
close all

// fill de database

USE &cDbf NEW EXCL ALIAS HUNT

IF ( hCsv := fOpen( cFile, 16 ) ) > 0
    WHILE HB_FReadLine( hCsv, @cLine, chr( 10 ) ) == 0
        //oWnd:SetMsg( "Process data "+str(nTeller--))  // Show progress, but slows down on large files
        nI ++
        IF nI > 1
            hunt->( dbAppend() )
            FillFields( cLine )
        ENDIF
    ENDDO
    fClose( hCsv )
    alert( str( nI ) )
ELSE
    alert( "BAD LUCK 2" )
    return
ENDIF
xbrowse()
close all
RETURN NIL


STATIC FUNCTION FillFields( cLine )
LOCAL nJ

nMax = hunt->( fCount() )
FOR nJ := 1 TO nMax
    //cLine := subStr( cLine, at( ["], cLine ) + 1 )
    //hunt->( FieldPut( nJ, subStr( cLine, 1, at( ["], cLine ) - 1 ) ) )
    //cLine := subStr( cLine, at( ["], cLine ) + 1 )


    if nJ < nMax
      cStr = subStr( cLine, 1, at( [,], cLine ) - 1 )
      cStr = STRTRAN(cStr, '"', '')
      hunt->( FieldPut( nJ, cStr ))
      cLine := subStr( cLine, at( [,], cLine ) + 1 )
    else
      cStr = STRTRAN(cLine, '"', '')
      hunt->( FieldPut( nJ, cStr ))  // Process last item, regel
    endif

NEXT
RETURN NIL

function strtoArr(cMaat)
   Local aTemp:={}
   if !empty(cMaat)
     do while at(",",cMaat) > 0
        AADD(aTemp,substr(cMaat,1,at(",",cMaat)-1))
        cMaat = substr(cMaat,at(",",cMaat)+1)
     enddo
     AADD(aTemp,alltrim(cMaat))
   endif
return aTemp

function Builddbf(adata,Alengte,cDbf)
   LOCAL aStru := {}

//   msginfo(atostr(adata))

   for i = 1 to len(adata)
     cField = STRTRAN(aData[i], '"', '')
     aAdd( aStru, { cField , "C", alengte[i]+1 , 0 } )
   next
   dbCreate( cDbf , aStru )

return

Static Function AToStr( aArray )

   Local cStr := "", nI, nLen := Len( aArray )

   For nI := 1 To nLen
      cStr += AllTrim( aArray[nI] ) + If( nI < nLen, ", ", "" )
   Next


Return cStr


function show_data(cDbf)
  USE &cDbf NEW EXCL ALIAS DATA
  xbrowse()
  close all
return


Function XbrShowSizes( oBrw )

   XBrowse( ArrTranspose( { oBrw:cHeaders, oBrw:nWidths } ), nil, nil, { |o| o:cHeaders := { "Header", "Width" } } )

return nil

function DbfsOpen()
  Local aLocal := {}
   for n = 1 to 255
      if ! Empty( Alias( n ) )
         AADD( aLocal,Str( n, 3 ) + ": " + If( Select() == n,"=> ", "   " ) +  PadR( Alias( n ), 15 ) )
      endif
      for j = 1 to 15
        if ! Empty( ( Alias( n ) )->( IndexKey( j ) ) )
           AADD( aLocal,"    "+ ( Alias( n ) )->( OrdName( j ) ) +" -> "+ PadR( ( Alias( n ) )->( IndexKey( j ) ), 35 ) )
        endif
     next
   next
   cTemp = MsgSelect( aLocal )
   sysrefresh()
return

function MsgSelect( aItems, cValue, cTitle )

   local oDlg

   DEFINE FONT oFont NAME "Courier New" SIZE 8,15

   DEFAULT cTitle := "Maak uw keuze"

   DEFINE DIALOG oDlg FROM 5,10 TO 24, 95 font oFont TITLE cTitle

   @  1, 2 LISTBOX cValue ITEMS aItems SIZE 305, 110  OF oDlg

   @ 7, 05 BUTTON "&OK"     OF oDlg SIZE 40, 12  ACTION oDlg:End() DEFAULT
   @ 7, 12 BUTTON "&Cancel" OF oDlg SIZE 40, 12  ACTION ( cValue := "", oDlg:End() )

   ACTIVATE DIALOG oDlg CENTERED

return cValue



static function ValidProdID( oGet, oCol )

   local nVal     := oGet:VarGet()

   if .not. PRD->( DBSEEK( nVal ) )
      PRD->( DBSEEK( nVal, .t. ) )
      oGet:cText           := oCol:Value
      oCol:lRunBtnAction   := .t.
   endif

return .t.

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

static function ProductSelect( nCode )

   local nSelect
   local oDlg, oBrw, oFont

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 400,500 PIXEL FONT oFont TITLE "PRODUCTS"

   @ 30,10 XBROWSE oBrw SIZE -10,-30 PIXEL OF oDlg ;
      DATASOURCE "PRD" ;
      COLUMNS "ID","veld","string","Select" ;
      CELL LINES NOBORDER AUTOSORT ;
      COLSIZES 50,100,100,40 ;

//   oBrw:aCols[2]:bClrStd := { || IF( PRD->select , { CLR_BLACK,CLR_WHITE } , { CLR_HRED,CLR_WHITE } ) }
   oBrw:aCols[2]:bClrStd := { || IF( PRD->select , { CLR_WHITE,CLR_HRED } , { CLR_BLACK,CLR_WHITE } ) }

   WITH OBJECT oBrw
      :bLDblClick    := { || nSelect := PRD->veld, oDlg:End() }
      :bKeyChar      := { |nKey| If( nKey == VK_RETURN, ;
               ( nSelect := PRD->veld, oDlg:End() ), nil ) }
      //

      :CreateFromCode()
   END


   //@ 230,150 BUTTON "Select" SIZE 40,14 PIXEL OF oDlg ACTION ( nSelect := PRD->veld, oDlg:End() )

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

   PRD->select = .t.

return nSelect

//----------------------------------------------------------------------------//
static function CalcRow( oBrw )

   oBrw:Amount:VarPut( oBrw:Quantity:Value * oBrw:Price:Value )

return nil


function buildmasterdbf()

   aStruct := {}
   AADD(aStruct, { "ID"      , "C", 35, 0 }) // Id
   AADD(aStruct, { "Aktief"  , "C", 35, 0 }) // Aktief (0/1)
   AADD(aStruct, { "Naam"    , "C", 35, 0 }) // Naam *
   AADD(aStruct, { "Cat_main", "C", 35, 0 }) // Categorieën (x,y,z...)
   AADD(aStruct, { "Cat_Sub1", "C", 35, 0 }) // Categorieën (x,y,z...)
   AADD(aStruct, { "Prijs"   , "C", 35, 0 }) // Prijs excl. Btw of Prijs incl. Btw
   AADD(aStruct, { "Tax"     , "C", 35, 0 }) // Tax rules id                                 // NO USED
   AADD(aStruct, { "gr_prijs", "C", 35, 0 }) // Groothandelsprijs // NOT
   AADD(aStruct, { "offer"   , "C", 35, 0 }) // Aanbieding (0/1)  // NOT
   AADD(aStruct, { "dis_val" , "C", 35, 0 }) // Discount amount // NOT
   AADD(aStruct, { "dis_per" , "C", 35, 0 }) // Discount percent  // NOT
   AADD(aStruct, { "dis_from", "C", 35, 0 }) // Discount from (yyyy-mm-dd) // NOT
   AADD(aStruct, { "dis_to  ", "C", 35, 0 }) // Discount to (yyyy-mm-dd)  // NOT
   AADD(aStruct, { "Art_Code", "C", 35, 0 }) // Referentie
   AADD(aStruct, { "Art_Lev" , "C", 35, 0 }) // Referentie leverancier
   AADD(aStruct, { "Leveranc", "C", 35, 0 }) // Leverancier  // NOT
   AADD(aStruct, { "Fabrikan", "C", 35, 0 }) // Fabrikant
   AADD(aStruct, { "Ean"     , "C", 35, 0 }) // EAN13        // NOT
   AADD(aStruct, { "Upc"     , "C", 35, 0 }) // UPC          // NOT
   AADD(aStruct, { "Milieu"  , "C", 35, 0 }) // Milieuheffing   // NOT
   AADD(aStruct, { "Gewicht" , "C", 35, 0 }) // Gewicht         // NOT
   AADD(aStruct, { "Aantal"  , "C", 35, 0 }) // Aantal
   AADD(aStruct, { "Kort_txt", "C", 35, 0 }) // Korte omschrijving
   AADD(aStruct, { "Memo"    , "C", 35, 0 }) // Omschrijving
   AADD(aStruct, { "Tags"    , "C", 35, 0 }) // Tags (x,y,z...)
   AADD(aStruct, { "Meta_tit", "C", 35, 0 }) // Meta titel
   AADD(aStruct, { "Meta_zoe", "C", 35, 0 }) // Meta zoekwoorden
   AADD(aStruct, { "Meta_inf", "C", 35, 0 }) // Meta omschrijving
   AADD(aStruct, { "Url_rewr", "C", 35, 0 }) // URL rewritten
   AADD(aStruct, { "Msg_sto" , "C", 35, 0 }) // Bericht indien op voorraad
   AADD(aStruct, { "Msg_nsto", "C", 35, 0 }) // Bericht indien niet op voorraad
   AADD(aStruct, { "Url_pic" , "C", 35, 0 }) // URLs afbeelding (x,y,z...)
   AADD(aStruct, { "Optie"   , "C", 35, 0 }) // 1= foto wissen bij upload
   AADD(aStruct, { "Beschikb", "C", 35, 0 }) // Only available online
   AADD(aStruct, { "Kleur"   , "C", 35, 0 }) // Only available online
   AADD(aStruct, { "Size"    , "C", 35, 0 }) // Only available online
   AADD(aStruct, { "Struct"  , "C", 35, 0 }) // Only available online

   DbCreate( "master.dbf", aStruct)

   use master NEW
   append blank
   replace master->id with "5101000"
   replace master->aktief with "1"
   replace master->naam with "The Name of the artikel"
   replace master->cat_main with "Main Menu"
   replace master->cat_Sub1 with "Sub Menu"
   //  All fields in order to give a sample of the data needed

   adbf := {}
   AADD(adbf, { "ID"      , "N",  3, 0 }) // Id
   AADD(adbf, { "Veld"    , "C", 50, 0 }) // Aktief (0/1)
   AADD(adbf, { "string"  , "C", 50, 0 }) // Naam *
   AADD(adbf, { "select"  , "L",  1, 0 }) // Naam *

   DbCreate( "struct.dbf", adbf)

   use struct NEW

   close all

return


FUNCTION master()

   local oDlg, oData, oStru, oFont, aDbf[ADIR("*.dbf")]

   Buildmasterdbf()  // only for testing.

   // Select a random dbf to Map/link to the master

   ADIR("*.dbf", aDbf)
   nFile = msglist(aDbf )
   cDbf = aDbf[nFile]

   USE struct new ALIAS PRD

   USE master NEW ALIAS MASTER
   if Select( "MASTER" ) == 0
      MsgStop( "Can not open MasterFile.DBF" )
      return 1
   endif

   USE &cDbf NEW ALIAS CUST
   if Select( "CUST" ) == 0
      MsgStop( "Can not open File.DBF" )
      return 1
   endif

   dbselectarea("CUST")

   for i = 1 to CUST->(FCOUNT())
      PRD->(dbappend())
      replace PRD->id with i
      replace PRD->veld with Fieldname(i)      // len en valtype
   next


   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-12

   DEFINE DIALOG oDlg SIZE 2*WID,2*HGT PIXEL FONT oFont

   @ 10,10 XBROWSE oData SIZE 200,320 PIXEL OF oDlg ;
      DATASOURCE MASTER->( DbStruct() ) ;
      COLUMNS 1, ;
      { |x| If( x == nil, MASTER->( FieldGet( oData:nArrayAt ) ), ;
         MASTER->( If( DbRLock(), ( FieldPut( oData:nArrayAt, x ), DbRUnLock() ), nil ) ) ) } ;
      HEADERS "FldName","FieldValue" ;
      COLSIZES 120,250 ;
      AUTOCOLS CELL LINES

   @ 10,220 XBROWSE oStru SIZE -10,-10 PIXEL OF oDlg ;
      DATASOURCE CUST->( DbStruct() ) ;
      COLUMNS 1,2,3,4, ;
      { |x| If( x == nil, CUST->( FieldGet( oStru:nArrayAt ) ), ;
         CUST->( If( DbRLock(), ( FieldPut( oStru:nArrayAt, x ), DbRUnLock() ), nil ) ) ) } ;
      HEADERS "FldName","Typ","Len","Dec", "FieldValue" ;
      COLSIZES 120,40,40,40,400 ;
      AUTOCOLS CELL LINES

   WITH OBJECT oData
      WITH OBJECT :oCol( 2 )
         :nEditType     := EDIT_GET_BUTTON
         :bClrSel          := { || { CLR_BLACK, CLR_HGRAY }}

         //:bEditValid    := { |oGet| ValidProdID( oGet, oBrw:oCol( 1 ) ) }
         :bEditBlock    := { |r,c,oCol| ProductSelect( oCol:Value ) }
         :bOnChange     := { |oCol,uOldVal| oData:FieldValue:VarPut( PRD->VELD ) }

      ENDWITH
      :bClrEdits        := { || { CLR_BLACK, CLR_YELLOW }}
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }
      :CreateFromCode()
   END

   WITH OBJECT oStru
      :bClrSel          := { || { CLR_BLACK, CLR_HGRAY }}
 //     :bChange          := { || oData:SelectCol( oData:ColPos( oData:oCol( oStru:nArrayAt ) ), .t. ) }

 //     :nStretchCol      := 5
      //
      WITH OBJECT :aCols[ 5 ]
         //:nEditType     := EDIT_GET
         :bClrEdit      := { || { CLR_BLACK, CLR_YELLOW }}
         //:bOnChange     := { || oData:RefreshCurrent() }
      END
      //
      :CreateFromCode()
   END

   @ 205,350 BUTTON "Close"    SIZE 40,14 PIXEL OF oDlg ACTION oDlg:End()

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

   close all
return 0

 

[James Bott]

Marc,

I think with the approach of mapping the fields, you are going to have to write a routine to add each file type and each record is going to have to be added via code.

If you rename the fields by posting a new header into the CSV file with the needed fiednames (as I suggested in my last post), then you will only need a few lines of code to import any CSV.

Questions?

James

[Marc Venken]

James,

You are right, but I noticed that I have to change/convert the data in the csv also to a wanted format.
The Csv files will never come in the right format and/or field combination.

ex. The csv file has a fielddata like this :

Fieldname in CSV : Name with data -> "PANDS WITH A FULL COTTON INLINE IN POL/KAT"

With the mapping now, I see the data in the right browse.
I could put the folowing in the Masterfile field Name : "UpperLower(csv->Name)" Result : "Pands with a full cotton inline in pol/kat"

Or

Fieldname : Price in csv has a format of "1232.1241" 4 decimals. In my Masterfile i could say "Val(Price,6,2)" Result "1232.12"

So I could transform every csv field into a wanted format into the master file.

I need to look into a function how to get the right data from those str's or macro's into my masterfield target

The source field here will contain also some typical functions like (Upper, substr, Val, )

Do while !master->eof()
master->target = master->source // where source will be something like above : "Val(csv->Price,6,2)" or "Lower(csv->naam)

// master-<target = "val(csv->price,6,2)" // -> It will look like this I think

master->(dbskip())
enddo

This code will not work, I have to work on that one, but it lookes like macro expanding a fielddata or so ???

[Marc Venken]

James,

Forgotten :

I also have to put for the half of all fields standard data, not provided into the csv.

Stock = "1"
Language = "NL"
Currence = "EUR"

These data can be pre filled than into the master file, since I create 1 record with relevant data.

[James Bott]

Marc,

I would still look for a common method (and/or a data driven method) instead of hardcoding for every file type.

>Fieldname : Price in csv has a format of "1232.1241" 4 decimals. In my Masterfile i could say

I am not sure about this, but I think this will automatically happen when you import it. Need to test it.

Alternatively, you could preprocess the header to the standard fieldnames, then determine which fields need to be numeric, and then process the CSV by removing quotes around numeric data. Then you can import the data as numeric.

["Val(Price,6,2)" Result "1232.12" -- the 6 should actually be a 7--it has a length of 7 including the decimal]

>The source field here will contain also some typical functions like (Upper, substr, Val, )

Upper() and Lower() are easy--you could just pre-process every required field in every CSV with those needed functions before the file is imported. You would do this whether it is needed or not, just so you don't have to write a routine for each file type.

So, I still think you can write a generic routine to process all the files. Even if there are a few exceptions, then you only need to write separate routines for those exceptions and everything else goes through the same routine.

I think that if you put a little more effort in up front (to find common routines) then you save lots of grunt work writing different code for each file-type.

James

[James Bott]

Marc,

I also have to put for the half of all fields standard data, not provided into the csv.

Stock = "1"
Language = "NL"
Currence = "EUR"

These data can be pre filled than into the master file, since I create 1 record with relevant data.

I don't work with different languages much. Could you explain in more detail? Are you saying you are going to have to translate text during the import too? Yikes!

Currency: So there is a field in each file containing which currency is used? If so then a simple DO CASE loop for each field should solve this. However, I expect you will need to use the record's date (assuming there is one) to lookup the exchange rate that was in effect at the time the data was collected in order to do the proper conversion.

Still, look for commonality rather than coding for each file-type.

James