Creating tab delimited files from Clipper 2

[fbizzell]

I have the need to create a tab delimited file of data from an Clipper (dbase III Plus) data file. The application that needs this text file to import data can not use a fixed field length text file or a space delimited text file. It must have one that is delimited by tabs.

Can this be done?

 

[Elkari]

Try this -

function TabDelimit
local i, cString, nFieldCount, nOutHandle

fcreate( nOutHandle, 'tabfile.txt' )

use dataset
go top
nFieldCount := fcount()

do while ( ! eof() )
cString := ''
for i := 1 to nFieldCount
cString += fieldget( i ) + chr( 9 )
next
fwrite( nOutHandle, left( cString, len( cString ) - 1 ) + chr( 13 ) + chr( 10 ) )
dbskip()
enddo

fclose( nOutHandle )
close

return ( .t. )

If you need the last tab character on the line, just remove the LEFT function from FWRITE.

I hope this helps you!

 

[HotEye]

I believe Clipper does not support TAB delimited files directly, but it is a programming language.

Post your email address and I will email back a CSV function I have written.

HotEye

 

[Elkari]

My Bad!!!

The FCREATE should read -

nOutHandle := fcreate( 'tabfile.txt' )

Sorry about that!

 

[fbizzell]

Thanks for all the helpful suggestions. I am trying them now...will let you know.

 

[fbizzell]

Note to Elkari,

I am getting an error on the following line:

cString += fieldget( i ) + chr( 9 )

The error is:

Error BASE/1081 Argument error: +

Frank

 

[Elkari]

Sorry, the assumption was that all the fields in the database were character fields. This error tells me there are numeric, date, memo and/or possibly logic fields in there as well. If you can get the value of 'i' to determine which field caused the problem, you will see it isn't a character field.

You'll need to add this check before the cString line and make a change to that line as well. Also add the variable cData to the local line.

if ( valtype( fieldget( i ) ) == 'M' )
elseif ( valtype( fieldget( i ) ) == 'D' )
cData := dtos( fieldget( i ) )
elseif ( valtype( fieldget( i ) ) == 'N' )
cData := str( fieldget( i ) )
elseif ( valtype( fieldget( i ) ) == 'L' )
cData := iif( fieldget( i ), '1', '0' )
else
cData := fieldget( i )
endif

cString += cData + chr( 9 )

A brief explanation -
1) a valtype of 'M' refers to a memo field. The code to retrieve this is a little more involved, so it was just bypassed.
2) a valtype of 'L' refers to a logical field. Standard use of the strings '1' and '0' to represent .t. and .f.
3) the others should be obvious.

 

[fbizzell]

Note to Elkari:

I still get the same error on the line:

cstring += cData + chr(9)

I put in all the other changes you sent. Here is a complete copy of the revised code:

function TabDelimit
local i, cString, nFieldCount, nOutHandle, cdata

nOutHandle := fcreate( 'tabfile.txt' )

use inv846 //this is my dbf file
go top
nFieldCount := fcount()

do while ( ! eof() )

if ( valtype( fieldget( i ) ) == 'M' )
elseif ( valtype( fieldget( i ) ) == 'D' )
cData := dtos( fieldget( i ) )
elseif ( valtype( fieldget( i ) ) == 'N' )
cData := str( fieldget( i ) )
elseif ( valtype( fieldget( i ) ) == 'L' )
cData := iif( fieldget( i ), '1', '0' )
else
cData := fieldget( i )
endif

cString := ''
for i := 1 to nFieldCount

cString += cData + chr( 9 )//get the argument error
next
fwrite( nOutHandle, left( cString, len( cString ) - 1 ) + chr( 13 ) + chr( 10 ) )
dbskip()
enddo

fclose( nOutHandle )
close

return ( .t. )

 

[Elkari]

Sorry I was not more specific, but the IF statement was to go inside the FOR loop just above the cString += cData + chr( 9 ) line. That way each field is being check for it's data type. Just out of curiosity, what is the structure of the database you are trying to use this function on?

I'm surprised you did not get an error with the IF statment since the variable 'i' hasn't been initialized yet.

 

[fbizzell]

Sorry, I am not sure where to put the if statements. Could you modify the code and send it to me. Sorry to cause you so much work. Here is the structure of the database:

Structure for database: F:inv846.dbf
Number of data records: 4
Date of last update : 08/28/02
Field Field Name Type Width Dec
1 COMPANY Character 15
2 INVOICE Character 1
3 REFERENCE Character 30
4 TRANS_DATE Character 8
5 ITEM_HUB Character 48
6 ITEM_QTY Numeric 15
7 ITEM_UOM Character 4
** Total ** 122

 

[Elkari]

It's that ITEM_QTY field that is causing the problem. It needs to be changed from a numeric field to a string. This is what the function should look like.

function TabDelimit
local i, cData, cString, nFieldCount, nOutHandle

nOutHandle := fcreate( 'tabfile.txt' )

use inv846 //this is my dbf file
go top
nFieldCount := fcount()

do while ( ! eof() )

cString := ''
for i := 1 to nFieldCount

if ( valtype( fieldget( i ) ) == 'M' ) // Memo Field
elseif ( valtype( fieldget( i ) ) == 'D' ) // Date Field
cData := dtos( fieldget( i ) )
elseif ( valtype( fieldget( i ) ) == 'N' ) // Numeric Field
cData := str( fieldget( i ) )
elseif ( valtype( fieldget( i ) ) == 'L' ) // Logical Field
cData := iif( fieldget( i ), '1', '0' )
else
cData := fieldget( i ) // Character Field
endif

cString += cData + chr( 9 )
next

fwrite( nOutHandle, left( cString, len( cString ) - 1 ) + chr( 13 ) + chr( 10 ) )

dbskip()
enddo

fclose( nOutHandle )
close

return ( .t. )

THIS should now work without any more problems and it wasn't any trouble at all (as long as I get paid ;-))

 

[fbizzell]

Note to Elkari,

Working fine now. Thanks a million. Thank you.

But I would like egg in my beer. I now want to reverse the process and import into a clipper database from a tab delimted text file. I can import from a sdf format (structured data format which is a fixed field length type of file) but reading into a dbf file from a tab delimited text file presents other problems. One line may have
xxxxxtabxxtabxxxxxx and the next line may have
xxxxxxxxtabxxxxtabxxx etc.

Frank

 

[Elkari]

And I thought people are weird for putting a lime into beers. Here's how to reverse the process.

function TabIn
local i, cData, cString, nCount, nFieldCount, nFileSize, nInHandle

nInHandle := fopen( 'tabfile.txt' )
nFileSize := fseek( nInHandle, 0, 2 ) // Find file size
fseek( nInHandle, 0, 0 )

use inv846 //this is my dbf file
go top
nFieldCount := fcount()

**-- Loop until end of file
do while ( fseek( nInHandle, 0, 1 ) < nSize )

cString := readln( nInHandle )
nCount := 1

**-- There's data to add
if ( len( cString ) > 0 )
append blank
rlock()
endif

**-- Loop to parse each line
do while ( len( cString ) > 0 )
nPos := at( chr( 9 ), cString ) // Check for a TAB
if ( nPos == 0 )
exit
endif
cData := substr( cString, 1, nPos - 1 )

**-- Convert data to proper field format
if ( valtype( fieldget( nCount ) ) == 'M' ) // Memo Field
elseif ( valtype( fieldget( nCount ) ) == 'D' ) // Date Field
cData := stod( cData )
elseif ( valtype( fieldget( nCount ) ) == 'N' ) // Numeric Field
cData := val( cData )
elseif ( valtype( fieldget( nCount ) ) == 'L' ) // Logical Field
cData := iif( cData == '1', .t., .f. )
endif

fieldput( nCount, cData )
nCount++
**-- Check for too much data on a line
if ( nCount > nFieldCount )
exit
endif

**-- Reposition string
cString := substr( cString, nPos + 1 )
enddo
unlock
enddo

fclose( nInHandle )
close

return ( .t. )

You'll also need this function.

function readln ( nHandle )
local cString, nPos, ;
cReturn := NULL

do while ( TRUE )

if ( len( ( cString := freadstr( nHandle, 256 ) ) ) == 0 )
exit

elseif ( ( nPos := at( chr( 10 ) + chr( 13 ), cString ) ) > 0 )
cReturn += left( cString, nPos - 1 )
if ( nPos < len( cString ) - 1 )
fseek( nHandle, - ( len( cString ) - nPos - 1 ), 1 )
endif
exit

endif
cReturn += cString

enddo

return ( cReturn )


NOTE: There are three assumptions at work here. 1) Dates are stored in the TAB file as YYYYMMDD. 2) The data in the TAB file is in the same order as the fields in the database. 3) No memo fields will be filled in from a TAB file.

 

[fbizzell]

Note to Elkari:

I think that I am taking advantage of your generosity. I have not tried this new code yet but will do so tomorrow.
However, I may have a problem because the dates are stored in the tab file as mm/dd/yy and if the date is for example:

09/05/02 it stores only 9/5/02
11/01/02 is stored as 11/1/02
etc.

The other 2 assumptions are correct. The data in the tab file will be in the same order as the order of the fields and there are not memo fields.

Frank

 

[Elkari]

Usually when I store dates in a text file, I put them in a string format which is what the function DTOS does. Have no fear, there is another function DTOC to handle the way you have your dates.

Examples:

dtos( date() ) --> '20020906' as a character string
stod( '20020906' ) --> 09/06/2002 as a date value type

dtoc( date() ) --> '09/06/2002' as a character string
ctod( '9/6/02' ) --> 09/06/2002 as a date value type

Just substitute references of DTOS and STOD with the coresponding DTOC and CTOD in the above functions. Note that DTOC leaves the leading zero on the month and day and gives the full year.

 

[fbizzell]

Thanks,

I am trying all of this out now...will back to you.

Frank

 

[fbizzell]

Note to Elkari:

Works great but only imports the first record then stops.
Also I had to change a couple of lines:

Do While (TRUE) // changed to Do While .t.
cReturn:=NULL //changed to cReturn:=&quot;&quot;
do while ( fseek( nInHandle, 0, 1 ) < nSize ) // changed to do while ( fseek( nInHand,0,1) <nFileSize)



I am not sure there is a end of line marker but I assume so and it is stopping at that point or trying to put all the data in the first record.

I could email you the dbf and the tab delimted text file if you need it to trouble shoot this.

Frank

 

[Elkari]

OOps...forgot to remove my defines.

Another change you needed is to this line.

elseif ( ( nPos := at( chr( 10 ) + chr( 13 ), cString ) ) > 0 )


Reverse chr( 10 ) + chr( 13 ) to chr( 13 ) + chr( 10 )

 

[fbizzell]

THANKS ELKARI! YOU ARE A GENIOUS! It is great to know that there are people out there that can bail us novices out when we need help. I have been afraid that the day would come when one could no longer find any Clipper experts out there...that they all moved on to something else more exciting. Clipper is such a great programming language that I can't believe we are going to let it die one of these days. For general business applications it can't be beat by Bill or anyone else!

I have one more request and I will let you rest. The text file has a one line header at the top that I would like to ignore. Can we skip past the first line of the text file?

Frank

 

[Elkari]

Right after you open the file, do a READLN. This will bypass the single header line.