Where is the code for importing a pipe separated file into a table?

[HGAutomator]

Hi,

This thread

http://hmgforum.com/viewtopic.php?t=246 ... 279619245e

mentions an implemenation of a PIPE character separator. But I can't locate this implemenation anywhere.

The files I'm working with are separated by PIPEs "|", but aren't delimited by anything. So I can use the VFP command

Code: Select all

APPEND FROM FILE.TXT DELIMITED WITH CHARACTER "|" FOR THISFIELD = 'XXX'

, and the file gets imported into a table perfectly.

But I've tried different versions of APPEND FROM, and the best that happens are blank records with maybe the first field imported.

I can parse everything out of course, but wanted to see first if there's a command comparable to Foxpro for this purpose.

[Red2]

Hi HGAotomator,

I have not tried it but I found this old HMG reference:

APPEND FROM <xcFile>
[FIELDS <idField list>]
[<scope>] [WHILE <lCondition>] [FOR <lCondition>]
[SDF | DELIMITED [<xcDelimiter> | WITH BLANK ] | [VIA <xcDriver>]]

Maybe this would allow you to specify the delimiter character.
Hope this might help.

[HGAutomator]

Sounds good, Red. I'll report back, with results.

Hi HGAotomator,

I have not tried it but I found this old HMG reference:

APPEND FROM <xcFile>
[FIELDS <idField list>]
[<scope>] [WHILE <lCondition>] [FOR <lCondition>]
[SDF | DELIMITED [<xcDelimiter> | WITH BLANK ] | [VIA <xcDriver>]]

Maybe this would allow you to specify the delimiter character.
Hope this might help.

[mustafa]

Hola amigos:
Creo que para solucionar el problema primero hay que convertir "|" en ","
Se me ocurre utilizar el Bloc de Notas y Remplazar por la ","
Crear un código prg parecido.

Code: Select all

#include "hmg.ch"
Function Main

PRIVATE Mundos := "sample.txt" // <-- File donde se ha sustituido "|" por ","
USE CODEPAGE NEW EXCLUSIVE  // <---- File DBF
APPEND FROM ( Mundos ) DELIMITED  // <--- no poner nada más 
MsgInfo("Ok Tranfer")
Return

y de DBF a TXT
USE CODEPAGE NEW EXCLUSIVE
Copy to sample2.txt delimited with " " // SE CREA COMA ===> , <== EN CADA CAMPO PARA LUEGO RECUPERAR


*------------------------------------------------------------------------------*

Hello friends:
I think to fix the problem you first have to convert "|" in ","
It occurs to me to use Notepad and Replace by the ","
Create a similar prg code.

Code: Select all

#include "hmg.ch"
Function Main
PRIVATE Worlds := "sample.txt" // the File where "|" has been replaced by ","
USE CODEPAGE NEW EXCLUSIVE // <---- File DBF
APPEND FROM ( Worlds ) DELIMITED // <--- don't put anything else
MsgInfo("Ok Transfer")
return

and from DBF to TXT
USE CODE PAGE NEW EXCLUSIVE
Copy to sample2.txt delimited with " " // CREATE COMMA ===> , <== IN EACH FIELD AND THEN RETRIEVE

[HGAutomator]

Thanks Mustafa, but I don't want to replace the pipes with commas. There might be embedded commas in the data.

Anyway, I'll try Red's suggestions when I have a chance this week, but will use Visual Foxpro to do the Import. VFP doesn't require any kind of conversion of the file first.

[Red2]

Hi HGAutomator,

Today I went ahead and actually tried to get the syntax I suggested to work for an arbitrary separator character.
It was NOT successful.

If you discover a working command syntax would you please share it?
Thank you!

[AUGE_OHR]

hi,

you can use MEMREAD(), MLCOUNT() and MEMOLINE() to "read" a "Line" of *.CSV
than use hb_ATokens() to get Array to use for FIELDPUT()

if your *.CSV is > 4 GB (!) than you can use

Code: Select all

   oFile := vfFileRead() :New( cFileToOpen,,, )
   oFile:Open()

   DO WHILE ! oFile:IsEOF()
      aToken := hb_ATokens( oFile:ReadLine(), cDelimiter )
      DoAppendReplace(aToken)
      oFile Skipe()
   ENDDO
   
   oFile:Close()

[HGAutomator]

Thanks Auge_Ohr,

Of course we can do this. But I'm specifically trying to find out if there is a built-in command for it.

For Delimited files, I can always use the following in a pinch. I've been trying to get a chance to clean up the code and post it here, but something else keeps diverting attention. This is a console rewrite of Text2Db by Timm Sodtalbers.

Here it is, in case anyone needs it

Code: Select all

#include "minigui.ch"
#include "FILEIO.CH"

REQUEST DBFCDX
REQUEST ABS, ALLTRIM, AT, CHR, CTOD, CDOW, CMONTH, DATE, DAY, DELETED, DESCEND, DTOC
REQUEST DTOS
REQUEST DOW, EMPTY, HB_ATOKENS

REQUEST I2BIN, L2BIN, LEFT, LEN, LOWER, LTRIM, MAX, MIN, MONTH, OS
REQUEST PAD, PADC, PADL, PADR, RAT, RECNO, RIGHT, ROUND, RTRIM, SPACE, STOD, STR
REQUEST STRZERO, SUBSTR, REPLICATE, TIME, TRANSFORM, TRIM, UPPER, VAL, VALTYPE, YEAR
REQUEST SOUNDEX




*  FUNCTION ---------------------------------------------------------------
*  Function....: TEXT2DB
* --------------------------------------------------------------------------
FUNCTION TEXT2DB( Source_s, Destination_s, IniFile_s )

	Local DateFormat_s
	Local Error_o
	Local IsExpressionValid_b := .F.
	Local AppendToExistingTable_s := ""

	// Determines which lines to include in the Table
	Local Validation_Expression_s

	// These are referred to & used, in the .ini configuration file IniFile_s
	PUBLIC LINETEXT
	PUBLIC TOKENS_A

	cls(23, chr(177))
	SETCOLOR( "W+/B,B/W,,W+/B")
	ClearStatusArea()

   IF Source_s = Nil .OR. Destination_s = Nil .OR. IniFile_s = Nil
			@ 7, 5 SAY "Syntax:  TEXT2DB [Source Text file to import from] [Destination table to output to] [Ini Configuration file name]"
      RETURN(.F.)
   EndIf


	IF !HB_FileExists( IniFile_s )
      ? "File not found: " + AllTrim( IniFile_s )
      RETURN(.F.)
   EndIf
   IF !HB_FileExists( Source_s )
      ? "File not found: " + AllTrim( Source_s )
      RETURN(.F.)
   EndIf

   DateFormat_s := Lower( AllTrim( ProfileString( IniFile_s, "General", "DateFormat", "" ) ) )
   AppendToExistingTable_s := Lower( AllTrim( ProfileString( IniFile_s, "General", "APPEND", "" ) ) )


   IF .NOT. EMPTY( DateFormat_s )
      SET DATE FORMAT DateFormat_s
   EndIf

	Process_TextFile( Source_s, Destination_s, IniFile_s, AppendToExistingTable_s )


RETURN( Nil )


FUNCTION Process_TextFile( Source_s, Destination_s, IniFile_s, AppendYN_s )

	Local Field_s
	Local Fields_a   := {}
	Local Nth_Expression_n := 0
	Local Nth_Field_n := 1
	Local lContinue := .T.
	Local Lines_n := 0
	Local Lines_s := ""
	Local RDD_s := AllTrim( ProfileString( IniFile_s, "General", "RDD", "DBFCDX" ) )
	Local Validation_Expression_s
	Local Current_Line_n := 0
	Local Nth_Line_n := 0
	Local First_Line_s := ""
	Local Expressions_to_Evaluate_a := {}
	Local Expression_to_Evaluate_s := ""
	Local Value_to_Add_to_Table_x := ""
	Local PreParse_s := ""
	Local PostProcess_s := ""
	Local IsHeaderRow_s := ""
	Local IsFooterRow_s:= ""
	Local LineProcessDescription_s := ""
	Local IsExpressionValid_b := .F.

	Local Error_o
	Local Error_b
	Local SaveError_b


	AppendYN_s = Upper( AppendYN_s )
	PreParse_s := AllTrim( ProfileString( IniFile_s, "PreProcess", "PROC", "" ) )
	Validation_Expression_s := ProfileString( IniFile_s, "Conditions", "Valid", "" )
	IsHeaderRow_s := AllTrim( ProfileString( IniFile_s, "Headers_and_Footers", "header", "" ) )
	IsFooterRow_s := AllTrim( ProfileString( IniFile_s, "Headers_and_Footers", "footer", "" ) )

	// Loop through the list of fields.
	 // Aadd( Fields_a, { "SOURCE", "C", 50, 0, Source_s } )
   DO WHILE lContinue = .T.

      Field_s := ProfileString( IniFile_s, "Fields", AllTrim(STR(Nth_Field_n,4) ), "" )

      IF .NOT. EMPTY( Field_s )


         AADD( Fields_a, { AllTrim( StrToken( Field_s, 1, "|" ) ), ;
                          AllTrim( StrToken( Field_s, 2, "|" ) ), ;
                          VAL( AllTrim( StrToken( Field_s, 3, "|" ) ) ), ;
                          VAL( AllTrim( StrToken( Field_s, 4, "|" ) ) ) } )
					
					AADD( Expressions_to_Evaluate_a, StrToken( Field_s, 5, "|" ) )
         	Nth_Field_n := Nth_Field_n + 1

      ELSE
         	lContinue := .F.
      EndIf

   ENDDO

		IF LEN( Fields_a ) = 1
		  ? "No fields defined in " + AllTrim( IniFile_s )
		  RETURN(.F.)
		ELSE
			If AppendYN_s == "YES"
				If .NOT. File( Destination_s )
		  		DBCREATE( Destination_s, Fields_a, RDD_s )
		  	Else
					// TODO: Check field specs against existing table.
		  	EndIf
		  Else	// If the table should be replaced
				DBCREATE( Destination_s, Fields_a, RDD_s )
			EndIf

		EndIf

		CLOSE

	TRY

		HB_Fuse(Source_s)

		Lines_n := HB_FlastRec()
		Lines_s := AllTrim( Str( Lines_n ) )
		Current_Line_n := hb_FRecNo()
		First_Line_s := HB_FReadLN()


		SELECT 0
		USE ( Destination_s ) ALIAS "DESTINATION_DB" VIA ( RDD_s )

		DO WHILE ! hb_FAtEof()
			LINETEXT := HB_FReadAndSkip()
			Nth_Line_n := Nth_Line_n + 1


			// If it's over 1,000 lines, then don't waste time displaying the progress of every line.
			If Lines_n > 1000
				// Just mention every 1000th line, if it's thousands of lines to process.
				If Nth_Line_n % 1000 == 0
					LineProcessDescription_s := "Processing line " + AllTrim( Str( Nth_Line_n ) ) + " of " + Lines_s
					@ 7, 5 SAY "Processing " + Str(Nth_Line_n) + " of " + Str( Lines_n )
				EndIf
			Else
				// If it's less than a thousand lines, then show the progress of each line.
				LineProcessDescription_s := "Processing line " + AllTrim( Str( Nth_Line_n ) ) + " of " + Lines_s
				@ 7, 5 SAY "Processing " + Str(Nth_Line_n) + " of " + Str( Lines_n )
			End If

			// If the source text file has an initial field heading row, skip this first row and start with the one.
			If IsHeaderRow_s == "YES"
				If Nth_Line_n == 1
					LOOP
				EndIf
			EndIf

			// If the source text file has a final footer line, ignore this final row.
			If IsFooterRow_s == "YES"
				If Nth_Line_n == Lines_n
					LOOP
				EndIf
			EndIf

			If .NOT. Empty( PreParse_s )
				TOKENS_A := &(PreParse_s)
			EndIf

			If Empty( Validation_Expression_s )
				IsExpressionValid_b := .T.
			Else
				IsExpressionValid_b := &(Validation_Expression_s)
			EndIf

			If IsExpressionValid_b

	      	DESTINATION_DB->(DBAPPEND())

					Error_b := ERRORBLOCK( { | object | BREAK( object ) } )
					BEGIN SEQUENCE

					AEVal( Expressions_to_Evaluate_a, { | Expression_to_Evaluate_s, Nth_Expression_n | Set_Field_Value( Expression_to_Evaluate_s, Nth_Expression_n, Nth_Line_n, LINETEXT ) } )

					RECOVER USING Error_b
					END SEQUENCE
					ERRORBLOCK( SaveError_b )

			EndIf
		EndDo

   	DESTINATION_DB->(DBCLOSEAREA())

		CATCH Error_o

			? "Error: Description: ", Error_o:Description, " Operation: ", Error_o:Operation
			Close all
			Return

	FINALLY

			HB_FUse()

	END


RETURN (.T.)



FUNCTION cls(ncColorAtt,cFillCharacter)
Local cColorString
cColorString   := iif(valtype(ncColorAtt)=="N",at2char(ncColorAtt),ncColorAtt)
cFillCharacter := repl( iif(cFillCharacter#Nil,cFillCharacter," "),9 )
dispbox(0,0,maxrow(),maxcol(),cFillCharacter,cColorString)
RETURN ''

Function At2char(nColor)
Local aFore   := {"N","B","G","BG","R","RB","GR","W",;
                  "N+","B+","G+","BG+","R+","RB+","GR+","W+"}
Local aBack   := {"N","B","G","BG","R","RB","GR","W",;
                  "N*","B*","G*","BG*","R*","RB*","GR*","W*"}
Local nFore         := nColor%16
Local nBack         := INT(nColor/16)
Local cForeground   := aFore[nFore+1]
Local cBackGround   := aBack[nBack+1]
return ( cForeground+'/'+cBackGround )

Function ClearStatusArea()

dispbox( 5, 5, 12, 200, repl(chr(177),9),"+N/N"  )

Return Nil


Function Remove_Beg_End_Quotes( OriginalString_s )
Local QuotesRemoved_s := ""
Local Length_of_Original_String_n := Len( Alltrim( OriginalString_s ) )


OriginalString_s := AllTrim( OriginalString_s )
QuotesRemoved_s := OriginalString_s

Length_of_Original_String_n := Len( Alltrim( OriginalString_s ) )

// Return the string, EXCEPT FOR the last quote character.
If Right( OriginalString_s, 1 ) == '"'
	QuotesRemoved_s := Left( OriginalString_s, Length_of_Original_String_n-1 )
EndIf



// Return the string, EXCEPT FOR the first quote character.
If Left( QuotesRemoved_s, 1 ) == '"'
	QuotesRemoved_s := Substr( QuotesRemoved_s, 2 )
EndIf

Return QuotesRemoved_s


// Assign Evaluation of Nth Expression to Nth Field
Procedure Set_Field_Value( Expression_to_Evaluate_s, Nth_Expression_n, LineNumber_from_File_n, Line_From_File_s )
	Local Value_to_Add_to_Table_x

	/*
	Local Error_b
	Error_b := ERRORBLOCK( { | object | BREAK( object ) } )
	*/
		Try
			Value_to_Add_to_Table_x := &(Expression_to_Evaluate_s)
			DESTINATION_DB->(FIELDPUT( Nth_Expression_n, Value_to_Add_to_Table_x ))
		Catch
			? "Problem evaluating " + "expression number " + Str( Nth_Expression_n ) + ", " + Expression_to_Evaluate_s
			// TODO: Add the Value of the expression.  If possible, add the line number of the file.
		END

Return

Sample .ini file:

[General]
DateFormat=mm/dd/yyyy

[Conditions]


[PreProcess]
#Tab Delimited
PROC=HB_ATOKENS( LINETEXT, Chr(9) )

#If header=YES, then Skip the Header line, line #1
#If footer=YES, then Skip the Last line
[Headers_and_Footers]
header=YES
footer=NO


#This defines how to extract each field from each line in the text file.
#Some functions that can be used, include
# Remove_Beg_End_Quotes( value ) - Remove quotes from the beginning and ending of strings
# SubString( value, StartingPosition, EndingPosition ) - Standard Substring function
# CTOD( value ) - Convert Date string "mm/dd/yyyy" to a Date
# STOD( value ) - Convert Date string "yyyymmdd" to a Date
# Val( value) - Convert a string value to a number
[Fields]
1=FIRSTFLD |C| 50| 0| Remove_Beg_End_Quotes( TOKENS_A[1] )
2=SECONDFLD |C| 15| 0| Remove_Beg_End_Quotes( TOKENS_A[2] )
3=THIRDFLD |C| 8| 0| TOKENS_A[3]
4=THISDATE |D| 8| 0| CTOD( TOKENS_A[4] )

[mustafa]

Possible solution ? ---> DELIMITED with PIPE "|"

Code: Select all

#include "hmg.ch"

FUNCTION MAIN()

    USE Cli_Ent NEW                                   //<-- Dbf Master file
    COPY TO Cli_Ent2                                  // <-- Copy Master to New File
    DBCloseAll()

    USE Cli_Ent NEW                                  //<-- Dbf Master file
    COPY TO sample2.txt DELIMITED WITH ({,"|"})      // A Txt File is created with the content of Cli_Ent.Dbf
    DBCloseAll()

    USE Cli_Ent2 New //<-- dbf file Copied from Master
    ZAP //<-- Delete Dbf File Content
    APPEND FROM sample2.txt DELIMITED WITH ({,"|"})     // <-- From the Txt File, the Data is passed from txt to Dbf
   Return
   

The file sample2.txt is separated by PIPE "|" not by ","
and the content of this file is correctly incorporated into the second Dbf file that has been created
Same as the first "Master"

Regards/Salam/ Saludos
Mustafa

[franco]

I do not have a starting file with PIPE delimiter but you could try
APPEND FROM FILE.TXT DELIMITED WITH CHR(124)
Sometimes quoted items do not exactly match ASC codes.