Truncated data! Importing text file delimited by commas with Wizard.

[Andre Globensky]

Hello all,

In the old Foxpro 3, there was an option button in the step 1A of the import Wizard,
that had a checkbox "SCAN ALL RECORDS", and imported the data OK.

But in the new versions of Fox 6, 7 and 8 you can only see the first 50 records and the wizard imports data with the maximum length of those first 50 records.

If you have records after those 50 that are greater in length, data are truncated.

Any solutions

Thanks

 

[johncook]

I am unfamiliar with the wizard but can tell you that when using a view, the same truncation occurs.

Let's say you are pulling the data from a field, employee_name and the first employee is named Jack whereas 2nd employee is named Jackson, the "son" would be truncated. Now this only occurs when you do things in the view like Select tablex.employee_name as cempname. The important thing is if you said, Select PADR(tablex.employee_name,50) as cempname all would work perfectly.

If you can pad the data so that all of it is the maximum length, your problem should go away. Perhaps that is not feasible.
John

 

[Andre Globensky]

The data are in text format and i do not know the maximum length of the records that are coming in.
The Scan All option was great for that.

I was looking in the xsource.zip to see if there was a possibility to raise the 50 records to a greater number or including the "Scan" somewhere.

Haven't work with views, but i'm using already the PADR function in other programs. The problem is that i'm in a Wizard and can't put the PADR or other function in use.

 

[JRB-Bldr]

Within the Wizard you might want to set your field width for a "worst case scenario" to complete the import.

Then perform your scan of the records or do a query to determine the MAX length of the field.

Then finally, use ALTER TABLE to re-set the field width.

Another alternative approach might be instead of using the VFP Import Wizard, you could use Excel Automation for your comma delimited text file import.

By doing so you could then use Excel's Column Auto-fit to set the Column (Field) width to the maximum width necessary and then do a SaveAs a DB4 file to make it usable for your VFP application.

Good Luck,
JRB-Bldr

 

[Andre Globensky]

I know you can set the width of each field but it is time consuming, if it where a one shot deal i wouldn't mind but it's not.

We are presently opening the text file in Excel, saving it in Excel and import with wizard, but there's a restriction as for the number of records in Excel, and it's time consuming(we receive a lot of text file).

Any Ideas on how to scan more then the first 50 records within the wizard or other prg?

 

[rgbean]

Well the "simple" answer is that I believe you have all the source code for the Import Wizard - change it to do what you want!

Just expand all the appropriate files from XSOURCE.ZIP. In VFP 8.0, it's in my directory "C:\Program Files\microsoft visual foxpro 8\Tools\xsource\".

Rick

 

[Andre Globensky]

Exactly what i found Rick, thanks
Incremented the iSampleSize = 50 to 200000 in the iwproc
and recompiled and it did the trick

Thank you all

 

[baltman]

Here's a 'super delimited import prg' I just adapted from some code. Be aware that you might need to tweak a bit for your situation. TT posts sometimes insert a line break, so you might have to put some lines back together.

Also, if there are headers in the text file that you want to use to name the table's fields, you can pull them and execute an alter table rename column col1 to (eval(col1)) inside an for i = 1 to afields() while on recno() 1 before the data types are applied.

lcTargetData="input_sample.txt"
CLOSE ALL
SET TALK OFF

super_delim_import(lcTargetData,",",CHR(34),.t.) &&file name, comma delim with " character delimiter, header = true
BROWSE NOWAIT

PROCEDURE super_delim_import
LPARAMETERS lcFileName, lcDelim, lcCharDelim, llRow1Header

IF llRow1Header=.t.
 lcHeader=" FOR RECNO()>1"
 lcAndHeader=" AND RECNO()>1"
ELSE
 lcHeader=""
 lcAndHeader=""
ENDIF

lnFieldCnt=0
loHandle=FOPEN(lcTargetData)
DO WHILE not Feof(loHandle)
	lcString=FGETS(loHandle,3000)
	lnFieldCnt=MAX(lnFieldCnt,OCCURS(lcDelim,lcString)) &&field count
ENDDO

CREATE TABLE preImportDBF (Col1 c(254))
FOR lnColCnt = 1 TO lnFieldCnt
  ALTER TABLE preImportDBF ADD COLUMN ("col"+TRANSFORM(lnColCnt+1)) c(254)
ENDFOR

?lnFieldCnt
FCLOSE(loHandle)
loHandle=.null.

APPEND FROM (lcTargetData) TYPE DELIMITED WITH CHARACTER &lcDelim WITH &lcCharDelim

SQLImportDBF =""
FOR lnColCnt = 1 TO AFIELDS(laTemp)
CALCULATE MAX(LEN(ALLTRIM(EVALUATE(FIELD(lnColCnt))))) TO lnMaxLen &&determine max len

scan
&& Determine data type
lcFieldType="C" &&starting assumption
locate FOR ("\" $ EVALUATE(FIELD(lnColCnt)) OR ;
"*" $ EVALUATE(FIELD(lnColCnt)) OR ;
"+" $ EVALUATE(FIELD(lnColCnt)) ;
OR "/" $ EVALUATE(FIELD(lnColCnt)) OR ;
("-" $ EVALUATE(FIELD(lnColCnt)) AND ;
LEFT(ALLTRIM(EVALUATE(FIELD(lnColCnt))),1)#"-"); 
OR type(EVALUATE(FIELD(lnColCnt)))#"N") ;
and LEN(ALLTRIM(EVALUATE(FIELD(lnColCnt))))>0 &lcAndHeader 

IF eof()=.t. && Then Number Field or Date Field
lcFieldType="N"
ENDIF

CALCULATE MIN(VAL(EVALUATE(FIELD(lnColCnt)))) TO min_val ;
FOR VAL(EVALUATE(FIELD(lnColCnt)))>0 &lcAndHeader 

CALCULATE Max(VAL(EVALUATE(FIELD(lnColCnt)))) TO max_val &lcHeader 

CALCULATE MIN((EVALUATE(FIELD(lnColCnt)))) TO min_text_val &lcHeader 
CALCULATE Max((EVALUATE(FIELD(lnColCnt)))) TO max_text_val &lcHeader 

IF CTOD(min_text_val)#{} AND CTOD(max_text_val)#{} &&then a date type 1
	lcFieldType="D"
ENDIF

IF lcFieldType="C" &&Not a Number or Date
calc MAX(LEN(ALLTRIM(EVALUATE(FIELD(lnColCnt))))) to totwidth &lcHeader 
ENDIF
	
IF lcFieldType="N" && Number Field or Date Field
	IF year({12/30/1899}+min_val)>=1900 AND (max_val)<70000 &&year 2094, then a date type 2
		lcFieldType="D"
		ALTER TABLE DBF() alter COLUMN (FIELD(lnColCnt)) c(12) &&make wide enough
		
		REPLACE ALL (FIELD(lnColCnt)) WITH DTOC({12/30/1899}+VAL(EVALUATE(FIELD(lnColCnt)))) ;
		FOR VAL(EVALUATE(FIELD(lnColCnt)))>0 &lcAndHeader 
	ENDIF

	IF min_val>=19000000 AND max_val<20940000 &&year 1900,2094, then a date type 3
		lcFieldType="D"
		ALTER TABLE DBF() alter COLUMN (FIELD(lnColCnt)) c(12) &&make wide enough
		REPLACE ALL (FIELD(lnColCnt)) WITH ;
		 SUBSTR(EVALUATE(FIELD(lnColCnt)),5,2)+"/"+;
		 RIGHT(ALLTRIM(EVALUATE(FIELD(lnColCnt))),2)+"/"+;
		 LEFT(EVALUATE(FIELD(lnColCnt)),4) FOR VAL(EVALUATE(FIELD(lnColCnt)))>0 &lcAndHeader 
	ENDIF

	IF lcFieldType="N" &&if still a number
	CALCULATE MAX(IIF("." $ EVALUATE(FIELD(lnColCnt))=.f.,0,;
	  LEN((CHRTRAN(STR(VAL(EVALUATE(FIELD(lnColCnt))),20,9),"0","")))-;
	  (1+LEN((CHRTRAN(STR(INT(VAL(EVALUATE(FIELD(lnColCnt))))),"0","")))))) TO decimalcnt &lcHeader 

	decimalcnt=decimalcnt-IIF(decimalcnt=1,1,0)

	CALCULATE MAX(LEN(ALLTRIM(STR(ROUND(VAL(EVALUATE(FIELD(lnColCnt))),0),20)))) to basewidth &lcHeader 
	totwidth=basewidth+decimalcnt+IIF(decimalcnt>0,1,0)
	ENDIF
ENDIF

SQLImportDBF=SQLImportDBF+" add column "+("col"+trans(lnColCnt))+;
  " "+lcFieldType+iif(lcFieldType="D","","("+;
  ALLTRIM(STR(IIF(lcFieldType="D",8,totwidth))))+;
  IIF(lcFieldType="N",","+ALLTRIM(STR(IIF(lcFieldType="N",decimalcnt,0)))+")","")+;
  IIF(lcFieldType="C",")","")
ENDSCAN
ENDFOR

CREATE TABLE ImportDBF (f1 c(1))
ALTER TABLE ImportDBF &SQLImportDBF drop column f1
APPEND FROM preImportDBF

IF VARTYPE(EVALUATE(FIELD(AFIELDS(latemp))))="N"
CALCULATE MAX(EVALUATE(FIELD(AFIELDS(latemp)))) TO lnTestEmpty
ELSE
CALCULATE MAX(LEN(ALLTRIM(EVALUATE(FIELD(AFIELDS(latemp)))))) TO lnTestEmpty
ENDIF

IF lnTestEmpty=0
 ALTER TABLE ImportDBF drop column (FIELD(AFIELDS(latemp)))
ENDIF

IF llRow1Header
GO TOP
DELETE
PACK
ELSE
GO TOP
ENDIF  
ENDPROC