A converter from text-file ( with delimiters ) to DBF ?

[ukoenig]

Hello,

i have to create a converter from Text to DBF.
The textfile can have many texlines and is the result of another software.
( telefon-calls )
The first thing i have to do is to create a empty DBF with the fieldnames of the first text-line.
Second : a scanning of each textline for delimiter ;
that means to write the text from ( 0 to the first ; ) to the 1. DBF-field,
then from the first ( ; between the second ; ) to the next DBF-field
and so on.
I tested a text-import with EXCEL but the result, saving the import as a DBF-file was bad.

The problem : sometimes, the telefon-company changes the text-structure.
That means, every time, before i start the converting, at first i have to create the DBF-structure from the 1. textline.
The textfile includes Character, date and numeric-fields.
For the import i can ignore the different field-types.
The field-types i can change, after the converting is done.

Somebody knows a better, easy solution to do this ?

Rechnung;Kundennr;Rufnummer;Netz;Art;Gesprdat;Gesprzeit;gewRufnr;gewNetz;Zeitber;Dauer;Volumen;Kennzeich;Preis;Code
C813354668;1063567698;0160 / 1853735;E+;NX;07.07.08;18:58:11;01732644433;VF;;00:28;0;0,0823;0,0000;NX
C813354668;1063567698;0160 / 1853735;E+;NX;08.07.08;13:09:19;017650146215;o2;;00:03;0;0,0088;0,0000;NX
C813354668;1063567698;0160 / 1853735;E+;NX;09.07.08;10:21:07;017650146215;o2;;00:30;0;0,0881;0,0000;NX
C813354668;1063567698;0160 / 1853735;E+;NX;10.07.08;14:12:36;01604789082;TM;;01:40;0;0,2938;0,0000;NX

Regards
Uwe

[Otto]

Hello Uwe,
I use STRTOKEN.
Regards,
Otto

cToken := StrToken( cText,2,';' )

// <cText> The text from where we want to extract a token.
// <nOcurrence> The numeric ocurrence of the token inside <cText>.
// <cSepChar> The character that separates the tokens. By default it is a space (" ").

[xProgrammer]

Hi Uwe

StrToken() is probably the most flexible approach but if you are after a quick and dirty no programming type solution you could use

Code: Select all  Expand view  RUN

APPEND FROM <text-file> DELIMITED WITH ";"

Regards
xProgrammer

[ukoenig]

Hello Otto,

i don't want to extract a token, i need the text between 2 delimiters
for the DBF-fields.

sample :

The textfile

Code: Select all  Expand view  RUN

AAAA;BBBB;CCCC;DDDD;EEEE    // Text-Header
111;2;33;4;5555    // values 1. Line
11;333;5;88;7      // values 2. Line

The DBF at the end must look like :

AAAA  BBBB  CCCC  DDDD  EEEE  // Fieldnames
111   2     33    4     5555  // Values
11    333   5     88    7



In my tool-collection, i found a function < PARSESTR >
with this function it is possible,
to extract a stringpart between 2 delimiters.

To create a empty DBF from the 1. textline is no problem,
but to find out the field-length.
For that, i have to look for the max., used length between 2 delimiters
for each field.

but maybe there is something, to do it in a better way.

Thank you
Regards
Uwe

[ukoenig]

Hello xProgrammer,

I tested :

APPEND FROM <text-file> DELIMITED WITH ";"

The Textfile appends but the fields are wrong.

it works only like "AAA";"BBB";"CCC"

but i have : AAA;BBB;CCC

I think, i have to write something with my function PARSESTR()

Thank you very much
Uwe

[James Bott]

Uwe,

Take a look at the TLex class.

>I don't want to extract a token, i need the text between 2 delimiters
for the DBF-fields.

The text between delimiters is called a token.

James

[ukoenig]

Sorry,
sometimes my bad english-translation.
i mixed token and delimiter.

That is the function < PARSESTR >

I can scan a textline and save the stringpart between two delimiters
to a array.
Maybe it is a solution for my problem.

Code: Select all  Expand view  RUN


FUNCTION PARSESTR(cText, cDelim)
/*
  Parses a string into substrings
  wherever <cDelim> is found and returns an
  array of these substrings. 
  An empty string will result in an empty array.
  <cDelim> defaults to a comma.

  Example: PARSESTR("AAA;BBB;CCC") => { "AAA", "BBB", "CCC" }
*/

local aTemp_ := {}
local cWork, n := 1

default cDelim := ","

if !empty(cText)
    cWork := cText
    do while n > 0
        n := at(cDelim, cWork)
        if n > 0
            aadd(aTemp_, left(cWork, n - 1))
            cWork := substr(cWork, n + len(cDelim))
        else
            aadd(aTemp_, cWork)
       endif
   enddo
endif

Return(aTemp_)



Regards
Uwe

[nageswaragunupudi]

As a small sample I coverted the data given in the first post into an multidimenttional array and browsed it with xbrowse with the following code: ( This code works with xHarbour )

Code: Select all  Expand view  RUN

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

function Main()

   local cBuf
   local aData, aHeaders

   cBuf  := MemoRead( 'input.txt' )
   // 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, ';' ) } )
   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

return nil


This is the result



Then I clicked the Excel button, and saved the excel sheet produced by xbrowse in DBF format.

Once parsing is done I am sure it becomes easier for us to ascertain data types, lengths and write in to DBF with appropriate structure.

[nageswaragunupudi]

Above sample is extended to check field widths and write to DBF.

Code: Select all  Expand view  RUN

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

function Main()

   local cBuf
   local aData, aHeaders

   cBuf  := MemoRead( 'input.txt' )
   // 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, ';' ) } )
   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 )

   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

   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


With an input text file of 40,000 rows, the program ( obviously not optimized for speed ) took 0.44 secs to parse and 2.9 seconds to write dbf.

[Antonio Linares]

Uwe,

As Otto has pointed, StrToken() is highly optimized as it is entirely based in C language so its speed is excellent. Here you have a working example:

Code: Select all  Expand view  RUN

#include "FiveWin.ch"

function Main()

   local cText := "C813354668;1063567698;0160 / 1853735;E+;NX;07.07.08;18:58:11;01732644433;VF;;00:28;0;0,0823;0,0000;NX"
   local n, cResult := ""
   
   for n = 1 to 15
      cResult += StrToken( cText, n, ";" ) + CRLF
   next   
   
   MsgInfo( cResult )
   
return nil   


[Otto]

Antonio,
could you please show how we could automatically check how often the delimiter is present?

Thanks in advance
Otto

PS: O.T. I use so many times for checking if a variable is empty
this code:
if len(ALLTRIM(cText) = 0
Is there a build in function existing?

[Antonio Linares]

Otto,

Use StrCharCount( cText, cChar ). Its also implemented in C code so it is very fast. Example:

Code: Select all  Expand view  RUN

#include "FiveWin.ch"

function Main()

   local cText := "C813354668;1063567698;0160 / 1853735;E+;NX;07.07.08;18:58:11;01732644433;VF;;00:28;0;0,0823;0,0000;NX"
   local n, cResult := "", nTimes := StrCharCount( cText, ";" ) + 1
   
   for n = 1 to nTimes
      cResult += StrToken( cText, n, ";" ) + CRLF
   next   
   
   MsgInfo( cResult )
   
return nil   


[hua]

Otto,
Use StrCharCount( cText, cChar ). ...

Antonio,
Can cChar length be bigger than 1? For example StrCharCount(cText, CRLF)

TIA

[gkuhnert]

Otto,

PS: O.T. I use so many times for checking if a variable is empty
this code:
if len(ALLTRIM(cText) = 0
Is there a build in function existing?

Maybe you can use this?

Code: Select all  Expand view  RUN

if empty(cText)
...

[Antonio Linares]

Hua,

No, just one char.

But you could use StrTran() to replace CRLFs into ";" or similar