自己寫的CSV IMPORT
取代FOXPRO 之 Append From xxx.CSV TYPE CSV
原因:使用內建之IMPORT指令,會出現line is too long (列過長)之錯誤
注意:本版本無法辨識由EXCEL轉存之 CSV 檔
| 可辨識的格式為: “abc”,”123”,”we”…. 所有欄位皆以””包括 |
*green 2010/05/23
*傳入cvs檔案,轉換存入cAlias,錯誤記錄轉入cCSVErrFile
*傳回轉換筆數
Function CSVTRAN2
Lparameters cCSVfile,cAlias,cCSVErrFile
Local handle, lcCommand,esckey,nRecord
If !File(cCSVfile)
Return .F.
Endif
handle = Fopen(cCSVfile)
If handle <=0
Return .F.
Endif
*
Fseek(m.handle,0) && Go Top
nRecord=0
nn=0
rec1pos=0
rr=0
Do While !Feof(m.handle)
*!* esckey=Inkey()
*!* If esckey=27
*!* Cancel
*!* Endif
lcCommand = Fread(m.handle, 8192)
haveRecEnd=Occurs(Chr(10),lcCommand)
If haveRecEnd>0
ii=1
Do While ii<=haveRecEnd
If ii>1
rec1pos=Atc(Chr(10),lcCommand,ii-1)
Else
rec1pos=0
Endif
rec2pos=Atc(Chr(10),lcCommand,ii)
rr=rr+1
Dimension RecStr[rr]
RecStr[rr]=Substr(lcCommand,rec1pos+1,rec2pos-rec1pos)
nn=nn+1
If nn=1
*記錄結尾
Wait '匯入中-'+cCSVfile+':'+Str(nRecord) Windows Nowait
pstr=''
For jj=1 To rr
pstr=pstr+RecStr[jj]
Next jj
*大於最大限制
*nRecord=nRecord+1
*IF ck_fld(cAlias,pstr,cCSVErrFile,nRecord) &&填入值
IF ck_csvfld(cAlias,pstr,cCSVErrFile,nRecord) &&填入值
nRecord=nRecord+1
Endif
nn=0
rr=0
Endif
ii=ii+1
Enddo
*
lastPOS=Ratc(Chr(10),lcCommand,1)
rr=rr+1
Dimension RecStr[rr]
RecStr[rr]=Substr(lcCommand,lastPOS+1) &&取最後段之字串
Else
*一定大於8192
rr=rr+1
Dimension RecStr[rr]
RecStr[rr]=lcCommand
Endif
Enddo
Fclose(m.handle)
Wait Clear
Return nRecord
ENDFUNC
*Green 2010/5/23
*CSVTRAN的子程式,分解單筆CSV記錄(p_str),存入cAlias
Function ck_fld
Lparameters cAlias,p_str,cCSVErrFile,nRecord
is_Debug=.F.
*IF nRecord>=6568
* SET STEP ON
*ENDIF
op_str=p_str
*先清空
p_str='(start)'+ALLTRIM(STRTRAN(p_str,CHR(10),[]))+'(End)'
*先替換原內含欄分隔字元[',']為[(NotFldDiv)]
p_str=STRTRAN(p_str,[','],[(NotFldDiv)])
*-----------------------------------------------------------樣版替換(注意:有先後順序)
*1. [,"""] --> [,"(->Inch<-)]
DO WHILE !Occurs([,""","],p_str)=0
p_str=STRTRAN(p_str,[,""","],[,(->Inch<-)","])
ENDDO
DO WHILE !Occurs([,"""],p_str)=0
p_str=STRTRAN(p_str,[,"""],[,"(->Inch<-)])
ENDDO
*2. [""",] --> [(->Inch<-)",]
DO WHILE !Occurs([""",],p_str)=0
p_str=STRTRAN(p_str,[""",],[(->Inch<-)",])
ENDDO
*-----------------------------------------------------------樣版替換(注意:有先後順序)
*3. [,"",] --> [,'',]
DO WHILE !Occurs([,"",],p_str)=0
p_str=STRTRAN(p_str,[,"",],[,'',])
ENDDO
*4start and end
p_str=STRTRAN(p_str,[(start)"",],[(start)'',])
p_str=STRTRAN(p_str,[,""(End)],[,''(End)])
p_str=STRTRAN(p_str,[(start)],[])
p_str=STRTRAN(p_str,[(End)],[])
*5. [""] --> [(->Inch<-)]
DO WHILE !Occurs([""],p_str)=0
p_str=STRTRAN(p_str,[""],[(->Inch<-)])
ENDDO
*6. ["]--> [']
p_str=STRTRAN(p_str,["],['])
*7. [(->Inch<-)] --> ["]
p_str=STRTRAN(p_str,[(->Inch<-)],["])
*8.修正錯誤
p_str=STRTRAN(p_str,[',',','],[','.','])
*9. FIND [',']
Fldnum=Occurs([','],p_str)
*Fldnum=Occurs('","',p_str)
*p_str=STRTRAN(p_str,'",",","','",".","')
*取得各欄
If Fcount(cAlias)=Fldnum+1
Select (cAlias)
Append Blank
For fnum=1 To Fldnum+1
If fnum>1
*fld1pos=Atc('","',p_str,fnum-1)+1
fld1pos=Atc([','],p_str,fnum-1)+1
Else
fld1pos=0
Endif
*fld2pos=Atc('","',p_str,fnum)
fld2pos=Atc([','],p_str,fnum)
fldna = Fields[fnum]
Select IMPORT_TMP
If fld2pos=0
wfldna = Substr(p_str,fld1pos+2,Len(p_str)-fld1pos-3)
wfldna = STRTRAN(wfldna,[(NotFldDiv)],[',']) &&還原
Else
wfldna = Substr(p_str,fld1pos+2,fld2pos-fld1pos-2)
wfldna = STRTRAN(wfldna,[(NotFldDiv)],[',']) &&還原
Endif
Replace &fldna With wfldna
* Fwrite(gnErrFile ,'['+STR(fnum)+']'+wfldna+CHR(10))
Next
Else
*? 'Field Count Error'
IF !EMPTY(cCSVErrFile)
If File(cCSVErrFile) && 檔案是否存在？
gnErrFile = Fopen(cCSVErrFile,12) && 如果存在，以讀寫方式開啟
Else
gnErrFile = Fcreate(cCSVErrFile) && 如果不存在，就建立它
ENDIF
FSEEK(gnErrFile ,0,2) && 移至檔尾
If gnErrFile < 0 && 檢查開啟檔案錯誤
Wait '不能開啟或建立輸出檔案' Window
ELSE
*Fwrite(gnErrFile,'['+STR(nRecord)+']'+p_str)
Fwrite(gnErrFile,op_str)
IF is_Debug
Fwrite(gnErrFile,p_str)
ENDIF
FCLOSE(gnErrFile)
ENDIF
ENDIF
RETURN .F.
ENDIF
RETURN .T.
Endfunc
*Green 2010/05/23
*傳入CSV,計算筆數
Function CSVRECCOUNT
Lparameters cCSVfile
Local handle,nRet
If !File(cCSVfile)
Return .F.
Endif
handle = Fopen(cCSVfile)
If handle <=0
Return .F.
Endif
*
Fseek(m.handle,0) && Go Top
nRet=0
Do While !Feof(handle)
nRet=nRet+Occurs(Chr(10),Fread(handle,8192))
Enddo
Fclose(handle)
Return nRet
Endfunc
*Green 2010/05/23
*傳入CSV,計算筆數 (2) 另一方式計算CSV筆數
Function CSVRECCOUNT2
Lparameters cCSVfile
cFile = FILETOSTR(cCSVfile)
nLines = ALINES(aFile,cFile)
Return nLines
EndFunc
沒有留言:
張貼留言