Converting CVS files

HunterEC
Posts: 723
Joined: Tue Sep 04, 2007 8:45 am

Converting CVS files

Post by HunterEC »

Guys:

I'm trying to import a 4.61GB CSV file (comma delimited, characters fields between " characters). Using the following program, it only imports the first data line.

Code: Select all

REQUEST DBFCDX

PROCEDURE Main

   USE Datafile VIA "DBFCDX"
   APPEND FROM Csvdata DELIMITED WITH (,)
   USE
RETURN
 
Here's the sample data (all fields are on one line on the data file):
"1750384806","1","","","","BISBEE","ROBERT","","DR.","","MD","","","","","","","
","","","5219 CITY BANK PKWY","STE 35","LUBBOCK","TX","794073545","US","80678520
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Converting CVS files

Post by nageswaragunupudi »

Assuming
lines are delimited by CRLF,
you have already opened DBF with exactly the same structure opened exclusively

you can try this code:

Code: Select all

   hCsv     := fOpen( cCsvFile )
   do while HB_FReadLine( hCsv, @cLine, CRLF ) == 0
      aVals    := &( "{" + cLine + "}" )
      DBAPPEND()
      AEVAL( aVals, { |c,i| FieldPut( i, c ) } )
   enddo
   fclose( hCsv )
 
Regards

G. N. Rao.
Hyderabad, India
HunterEC
Posts: 723
Joined: Tue Sep 04, 2007 8:45 am

Re: Converting CVS files

Post by HunterEC »

Rao:

It does not work. The dbf structure is the same as that of the txt file. Here's the complete code (the meter does not work either):

Code: Select all

#define CRLF   CHR(13) + CHR(10)

#include "Fivewin.ch"


REQUEST DBFCDX

PROCEDURE Main
   LOCAL hCsv, cCsvFile := "Data.txt"
   LOCAL oDlg2, oMeter, nActual := 0, nTotal

   USE Data VIA "DBFCDX"
   hCsv := FOPEN( cCsvFile )

   nActual := 0
   nTotal  := RECCOUNT()

   DEFINE DIALOG oDlg2 TITLE "Processing ..."

   @ 2, 1 METER oMeter  VAR nActual TOTAL nTotal OF oDLg2 SIZE 140 , 20

   ACTIVATE DIALOG oDlg2 ON PAINT EVAL({|| oMeter:settotal( nTotal ),      ;
      DisableX (@oDlg2, .T.), AddRecs (@oMeter, @nActual, @oDlg2, @hCsv)}) ;
      CENTERED

   FCLOSE( hCsv )
   USE
RETURN


*******************************************************************************
*** FUNCTION DisableX(oWin, lDisable) to Disable X button of Window/Dialog  ***
*******************************************************************************

STATIC FUNCTION DisableX(oWin, lDisable)
   #define MF_DISABLED      2
   #define MF_BYPOSITION    1024  // 0x0400

   LOCAL hMenu  := 0, nCount := 0

   IF lDisable
      hMenu  = GetSystemMenu(oWin:hWnd, .F.)
      nCount = GetMItemCount(hMenu)
      IF oWin:ClassName() = "TDIALOG"
         RemoveMenu(hMenu, 1, nOR( MF_BYPOSITION, MF_DISABLED) )
      ELSE
         RemoveMenu(hMenu, nCount - 1, nOR( MF_BYPOSITION, MF_DISABLED) )
         RemoveMenu(hMenu, nCount - 2, nOR( MF_BYPOSITION, MF_DISABLED) )
      ENDIF
      DrawMenuBar( oWin:hWnd )
   ELSE
      GetSystemMenu( oWin:hWnd, .T. )
      DrawMenuBar( oWin:hWnd )
   ENDIF

   #undef MF_DISABLED
   #undef MF_BYPOSITION
RETURN NIL
// EOF: DisableX


STATIC FUNCTION AddRecs (oMeter, nActual, oDlg2, hCsv)
   LOCAL cLine, aVals

   DO WHILE HB_FReadLine( hCsv, @cLine, CRLF ) == 0
      aVals    := &( "{" + cLine + "}" )
      DBAPPEND()
      AEVAL( aVals, { |c,i| FieldPut( i, c ) } )

      oMeter:set( nActual )
      nActual++

      SysRefresh()
   ENDDO
   oDlg2:end()
RETURN (.T.)
// EOF: AddRecs
 
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Converting CVS files

Post by nageswaragunupudi »

1. "Does not work" means what is happening?
2. Are you sure the line delimiter is CRLF?
3. First let us concentrate on making sure the main logic works and later we can add frills like meters, menus,etc.

Please post dbf structure as array
and
Post 10 lines of csv file.
Regards

G. N. Rao.
Hyderabad, India
HunterEC
Posts: 723
Joined: Tue Sep 04, 2007 8:45 am

Re: Converting CVS files

Post by HunterEC »

Rao:

1. Not working === not importing the text file into the dbf
2. 200% sure the delimiter is CRLF

First 10 data lines:
"1679576722","1","","","","WIEBE","DAVID","A","","","M.D.","","","","","","","","","","PO BOX 2168","","KEARNEY","NE","688482168","US","3088652512","3088652506","3500 CENTRAL AVE","","KEARNEY","NE","688472944","US","3088652512","3088652506","05/23/2005","07/08/2007","","","","M","","","","","","207X00000X","12637","NE","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","645540","01","KS","FIRSTGUARD","1553","01","NE","BCBS","93420WI","04","NE","","46969","01","KS","BCBS","B67599","02","","","046969WI","04","KS","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","X","","","","","","","","","","","","","","","","","","","","",""
"1588667638","1","","","","PILCHER","WILLIAM","C","DR.","","MD","","","","","","","","","","562 PARK ST","STE 310","JACKSONVILLE","FL","322042962","US","9046332021","9046339793","1824 KING ST","SUITE 300","JACKSONVILLE","FL","322044735","US","9043881820","9043881827","05/23/2005","09/29/2008","","","","M","","","","","","207RC0000X","032024","GA","N","207RC0000X","ME68414","FL","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","208143","01","FL","AVMED","06BDGPK","04","GA","","0897705","01","FL","AETNA","110123591","04","FL","RAILROAD MCARE","27888Z","04","FL","","510265","01","GA","BCBS","27888","01","FL","BCBS","C73899","02","FL","","00706626A","05","GA","","251286600","05","FL","","00532485C","05","GA","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","",""
"1497758544","2","","<UNAVAIL>","CUMBERLAND COUNTY HOSPITAL SYSTEM, INC","","","","","","","CAPE FEAR VALLEY HOME HEALTH AND HOSPICE","3","","","","","","","","3418 VILLAGE DR","","FAYETTEVILLE","NC","283044552","US","9106096740","","3418 VILLAGE DR","","FAYETTEVILLE","NC","283044552","US","9106096740","","05/23/2005","09/26/2011","","","","","NAGOWSKI","MICHAEL","","CEO","9106096700","251G00000X","HC0283","NC","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","3401562","05","NC","","341562","04","NC","PROVIDER NUMBER","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N","","","MR.","","","","","","","","","","","","","","","","",""
"1306849450","1","","","","SMITSON","HAROLD","LEROY","DR.","II","M.D.","","","","","","","","","","810 LUCAS DR","","ATHENS","TX","757513446","US","9036756778","9036752333","810 LUCAS DR","","ATHENS","TX","757513446","US","9036756778","9036752333","05/23/2005","01/03/2008","","","","M","","","","","","2085R0202X","E5444","TX","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","B26530","02","TX","","83R321","08","TX","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","",""
"1215930367","1","","","","GRESSOT","LAURENT","","DR.","","M.D.","","","","","","","","","","PO BOX 4356","DEPT 664","HOUSTON","TX","772104356","US","2814405006","2814406149","17323 RED OAK DR","","HOUSTON","TX","770901243","US","2814405006","2814406149","05/23/2005","08/06/2013","","","","M","","","","","","174400000X","H6257","TX","N","207RH0003X","H6257","TX","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","E43866","02","TX","","830005153","08","TX","","0533800001","07","TX","","89G024","08","TX","","1215930367","08","TX","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","",""
"1023011178","2","","<UNAVAIL>","NAPA VALLEY HOSPICE & ADULT DAY SERVICES","","","","","","","","","","","","","","","","414 S JEFFERSON ST","","NAPA","CA","945594515","US","7072589080","7072582476","414 S JEFFERSON ST","","NAPA","CA","945594515","US","7072589080","7072582476","05/23/2005","10/17/2011","","","","","VOLKERTS","KEITH","REGIS","DIRECTOR OF FINANCE","7072589080","251G00000X","100000741","CA","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","051537","04","CA","HOSPICE MEDICARE NO","HPC01537G","05","CA","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N","","","MR.","","","","","","","","","","","","","","","","",""
"1932102084","1","","","","ADUSUMILLI","RAVI","K","","","MD","","","","","","","","","","2940 N MCCORD RD","","TOLEDO","OH","436151753","US","4198423000","4198423048","2940 N MCCORD RD","","TOLEDO","OH","436151753","US","4198423000","4198423048","05/23/2005","04/23/2012","","","","M","","","","","","207RC0000X","4301081344","MI","N","207RC0000X","35069014","OH","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","4042622","08","OH","","4042624","08","OH","","060060579","08","OH","","P00751116","01","","RAILROAD MEDICARE","0178623","05","OH","","AD4257781","08","OH","","MI1635026","08","MI","","0792003","08","OH","","0792002","08","OH","","MI1635021","08","MI","","4042627","08","OH","","E21287","02","","","4042628","08","OH","","4042629","08","OH","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","",""
"1841293990","1","","","","WORTSMAN","SUSAN","","","","MA-CCC","","","","","","","","","","68 ROCKLEDGE RD","APT 1C","HARTSDALE","NY","105303455","US","2124814464","","425 E 25TH ST","","NEW YORK","NY","100102547","US","2124814464","","05/23/2005","07/08/2007","","","","F","","","","","","231H00000X","000396-1","NY","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","M72081","04","NY","PROVIDER NUMBER","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","",""
"1750384806","1","","","","BISBEE","ROBERT","","DR.","","MD","","","","","","","","","","5219 CITY BANK PKWY","STE 35","LUBBOCK","TX","794073545","US","8067852045","8067222908","113 WALNUT ST","","IDALOU","TX","793294003","US","8068922537","8068922726","05/23/2005","01/13/2012","","","","M","","","","","","207R00000X","J8461","TX","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","G22052","02","TX","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","",""

DBF Structure:
Sorry I do not have a tool to put them as an array :-(
Last Update: 08/28/13
# Records: 0
# Fields: 329 Bytes/Record: 7908
----------------------------------------
Field FieldName Type Wid Dec
------ --------- ---- --- ---
1 NPI C 010 000
2 ETC C 001 000
3 RNPI C 010 000
4 EIN C 009 000
5 PON C 070 000
6 PLN C 035 000
7 PFN C 020 000
8 PMN C 020 000
9 PNPT C 005 000
10 PNST C 005 000
11 PCT C 020 000
12 POON C 070 000
13 POONTC C 001 000
14 POLN C 035 000
15 POFN C 020 000
16 POMN C 020 000
17 PONPT C 005 000
18 PONST C 005 000
19 POCT C 020 000
20 POLNTC C 001 000
21 PFLBMA C 055 000
22 PSLBMA C 055 000
23 PBMACN C 040 000
24 PBMASN C 040 000
25 PBMAPC C 020 000
26 PBMACC C 002 000
27 PBMATN C 020 000
28 PBMAFN C 020 000
29 PFLBPLA C 055 000
30 PSLBPLA C 055 000
31 PBPLACN C 040 000
32 PBPLASN C 040 000
33 PBPLAPC C 020 000
34 PBPLACC C 002 000
35 PBPLATN C 020 000
36 PBPLAFN C 020 000
37 PED D 008 000
38 LUD D 008 000
39 NPIDRC C 002 000
40 NPIDD D 008 000
41 NPIRD D 008 000
42 PGC C 001 000
43 AOLN C 035 000
44 AOFN C 020 000
45 AOMN C 020 000
46 AOTP C 035 000
47 AOTN C 020 000
48 HPTC1 C 010 000
49 PLN1 C 020 000
50 PLNSC1 C 002 000
51 HPPTS1 C 001 000
52 HPTC2 C 010 000
53 PLN2 C 020 000
54 PLNSC2 C 002 000
55 HPPTS2 C 001 000
56 HPTC3 C 010 000
57 PLN3 C 020 000
58 PLNSC3 C 002 000
59 HPPTS3 C 001 000
60 HPTC4 C 010 000
61 PLN4 C 020 000
62 PLNSC4 C 002 000
63 HPPTS4 C 001 000
64 HPTC5 C 010 000
65 PLN5 C 020 000
66 PLNSC5 C 002 000
67 HPPTS5 C 001 000
68 HPTC6 C 010 000
69 PLN6 C 020 000
70 PLNSC6 C 002 000
71 HPPTS6 C 001 000
72 HPTC7 C 010 000
73 PLN7 C 020 000
74 PLNSC7 C 002 000
75 HPPTS7 C 001 000
76 HPTC8 C 010 000
77 PLN8 C 020 000
78 PLNSC8 C 002 000
79 HPPTS8 C 001 000
80 HPTC9 C 010 000
81 PLN9 C 020 000
82 PLNSC9 C 002 000
83 HPPTS9 C 001 000
84 HPTC10 C 010 000
85 PLN10 C 020 000
86 PLNSC10 C 002 000
87 HPPTS10 C 001 000
88 HPTC11 C 010 000
89 PLN11 C 020 000
90 PLNSC11 C 002 000
91 HPPTS11 C 001 000
92 HPTC12 C 010 000
93 PLN12 C 020 000
94 PLNSC12 C 002 000
95 HPPTS12 C 001 000
96 HPTC13 C 010 000
97 PLN13 C 020 000
98 PLNSC13 C 002 000
99 HPPTS13 C 001 000
100 HPTC14 C 010 000
101 PLN14 C 020 000
102 PLNSC14 C 002 000
103 HPPTS14 C 001 000
104 HPTC15 C 010 000
105 PLN15 C 020 000
106 PLNSC15 C 002 000
107 HPPTS15 C 001 000
108 OPI1 C 020 000
109 OPITC1 C 002 000
110 OPIS1 C 002 000
111 OPII1 C 080 000
112 OPI2 C 020 000
113 OPITC2 C 002 000
114 OPIS2 C 002 000
115 OPII2 C 080 000
116 OPI3 C 020 000
117 OPITC3 C 002 000
118 OPIS3 C 002 000
119 OPII3 C 080 000
120 OPI4 C 020 000
121 OPITC4 C 002 000
122 OPIS4 C 002 000
123 OPII4 C 080 000
124 OPI5 C 020 000
125 OPITC5 C 002 000
126 OPIS5 C 002 000
127 OPII5 C 080 000
128 OPI6 C 020 000
129 OPITC6 C 002 000
130 OPIS6 C 002 000
131 OPII6 C 080 000
132 OPI7 C 020 000
133 OPITC7 C 002 000
134 OPIS7 C 002 000
135 OPII7 C 080 000
136 OPI8 C 020 000
137 OPITC8 C 002 000
138 OPIS8 C 002 000
139 OPII8 C 080 000
140 OPI9 C 020 000
141 OPITC9 C 002 000
142 OPIS9 C 002 000
143 OPII9 C 080 000
144 OPI10 C 020 000
145 OPITC10 C 002 000
146 OPIS10 C 002 000
147 OPII10 C 080 000
148 OPI11 C 020 000
149 OPITC11 C 002 000
150 OPIS11 C 002 000
151 OPII11 C 080 000
152 OPI12 C 020 000
153 OPITC12 C 002 000
154 OPIS12 C 002 000
155 OPII12 C 080 000
156 OPI13 C 020 000
157 OPITC13 C 002 000
158 OPIS13 C 002 000
159 OPII13 C 080 000
160 OPI14 C 020 000
161 OPITC14 C 002 000
162 OPIS214 C 002 000
163 OPII14 C 080 000
164 OPI15 C 020 000
165 OPITC15 C 002 000
166 OPIS15 C 002 000
167 OPII15 C 080 000
168 OPI16 C 020 000
169 OPITC16 C 002 000
170 OPIS16 C 002 000
171 OPII16 C 080 000
172 OPI17 C 020 000
173 OPITC17 C 002 000
174 OPIS17 C 002 000
175 OPII17 C 080 000
176 OPI18 C 020 000
177 OPITC18 C 002 000
178 OPIS18 C 002 000
179 OPII18 C 080 000
180 OPI19 C 020 000
181 OPITC19 C 002 000
182 OPIS19 C 002 000
183 OPISI19 C 080 000
184 OPI20 C 020 000
185 OPITC20 C 002 000
186 OPIS20 C 002 000
187 OPII20 C 080 000
188 OPI21 C 020 000
189 OPITC21 C 002 000
190 OPIS21 C 002 000
191 OPII21 C 080 000
192 OPI22 C 020 000
193 OPITC22 C 002 000
194 OPIS22 C 002 000
195 OPII22 C 080 000
196 OPI23 C 020 000
197 OPITC23 C 002 000
198 OPIS23 C 002 000
199 OPII23 C 080 000
200 OPI24 C 020 000
201 OPITC24 C 002 000
202 OPIS24 C 002 000
203 OPII24 C 080 000
204 OPI25 C 020 000
205 OPITC25 C 002 000
206 OPIS25 C 002 000
207 OPII25 C 080 000
208 OPI26 C 020 000
209 OPITC26 C 002 000
210 OPIS26 C 002 000
211 OPII26 C 080 000
212 OPI27 C 020 000
213 OPITC27 C 002 000
214 OPIS27 C 002 000
215 OPII27 C 080 000
216 OPI28 C 020 000
217 OPITC28 C 002 000
218 OPIS28 C 002 000
219 OPII28 C 080 000
220 OPI29 C 020 000
221 OPITC29 C 002 000
222 OPIS29 C 002 000
223 OPII29 C 080 000
224 OPI30 C 020 000
225 OPITC30 C 002 000
226 OPIS30 C 002 000
227 OPII30 C 080 000
228 OPI31 C 020 000
229 OPITC31 C 002 000
230 OPIS31 C 002 000
231 OPII31 C 080 000
232 OPI32 C 020 000
233 OPITC32 C 002 000
234 OPIS32 C 002 000
235 OPII32 C 080 000
236 OPI33 C 020 000
237 OPITC33 C 002 000
238 OPIS33 C 002 000
239 OPII33 C 080 000
240 OPI34 C 020 000
241 OPITC34 C 002 000
242 OPIS34 C 002 000
243 OPII34 C 080 000
244 OPI35 C 020 000
245 OPITC35 C 002 000
246 OPIS35 C 002 000
247 OPII35 C 080 000
248 OPI36 C 020 000
249 OPITC36 C 002 000
250 OPIS36 C 002 000
251 OPII36 C 080 000
252 OPI37 C 020 000
253 OPITC37 C 002 000
254 OPIS37 C 002 000
255 OPII37 C 080 000
256 OPI38 C 020 000
257 OPITC38 C 002 000
258 OPIS38 C 002 000
259 OPII38 C 080 000
260 OPI39 C 020 000
261 OPITC39 C 002 000
262 OPIS39 C 002 000
263 OPII39 C 080 000
264 OPI40 C 020 000
265 OPITC40 C 002 000
266 OPIS40 C 002 000
267 OPII40 C 080 000
268 OPI41 C 020 000
269 OPITC41 C 002 000
270 OPIS41 C 002 000
271 OPII41 C 080 000
272 OPI42 C 020 000
273 OPITC42 C 002 000
274 OPIS42 C 002 000
275 OPII42 C 080 000
276 OPI43 C 020 000
277 OPITC43 C 002 000
278 OPIS43 C 002 000
279 OPII43 C 080 000
280 OPI44 C 020 000
281 OPITC44 C 002 000
282 OPIS44 C 002 000
283 OPII44 C 080 000
284 OPI45 C 020 000
285 OPITC45 C 002 000
286 OPIS45 C 002 000
287 OPII45 C 080 000
288 OPI46 C 020 000
289 OPITC46 C 002 000
290 OPIS46 C 002 000
291 OPII46 C 080 000
292 OPI47 C 020 000
293 OPITC47 C 002 000
294 OPIS47 C 002 000
295 OPII47 C 080 000
296 OPI48 C 020 000
297 OPITC48 C 002 000
298 OPIS48 C 002 000
299 OPII48 C 080 000
300 OPI49 C 020 000
301 OPITC49 C 002 000
302 OPIS49 C 002 000
303 OPII49 C 080 000
304 OPI50 C 020 000
305 OPITC50 C 002 000
306 OPIS50 C 002 000
307 OPII50 C 080 000
308 ISP C 001 000
309 IOS C 001 000
310 POLBN C 070 000
311 POTIN C 009 000
312 AONPT C 005 000
313 AONST C 005 000
314 AOCT C 020 000
315 HPTG01 C 070 000
316 HPTG02 C 070 000
317 HPTG03 C 070 000
318 HPTG04 C 070 000
319 HPGT05 C 070 000
320 HPTG06 C 070 000
321 HPTG07 C 070 000
322 HPTG08 C 070 000
323 HPTG09 C 070 000
324 HPTG10 C 070 000
325 HPTG11 C 070 000
326 HPTG12 C 070 000
327 HPTG13 C 070 000
328 HPTG14 C 070 000
329 HPTG15 C 070 000

Thank you very much Mr. Rao !
ADutheil
Posts: 352
Joined: Sun May 31, 2009 6:25 pm
Location: Salvador - Bahia - Brazil

Re: Converting CVS files

Post by ADutheil »

You should set all fieds type as "C" in your DBF and use APPEND FROM DELIMITED command. No need to specify the delimiter as CVS is default.
Regards,

André Dutheil
FWH 13.04 HB 3.2 BCC 5.82 MinGW 4.5.2 MSVS 10
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Converting CVS files

Post by nageswaragunupudi »

When the field types were not character type, you need to convert those columns into the correct type before saving to dbf. Now we see there are 4 date fields and we added conversion to dates.

This is a working function and conversion worked for the 10 rows.

Code: Select all

function HunterCSV()

   local hCsv, aVals, cLine
   local cCsvFile := "hunter.csv"

   SET DATE AMERICAN
   SET CENTURY ON

   USE HUNTER NEW EXCLUSIVE

   hCsv     := fOpen( cCsvFile )
   do while HB_FReadLine( hCsv, @cLine, CRLF ) == 0
      aVals    := &( "{" + cLine + "}" )
      if Len( aVals ) == 329
         aVals[ 37 ]    := CTOD( aVals[ 37 ] )
         aVals[ 38 ]    := CTOD( aVals[ 38 ] )
         aVals[ 40 ]    := CTOD( aVals[ 40 ] )
         aVals[ 41 ]    := CTOD( aVals[ 41 ] )

         DBAPPEND()
         AEVAL( aVals, { |c,i| FieldPut( i, c ) } )
      endif
   enddo
   fclose( hCsv )

   XBROWSE()

return nil
 
You may first check this with 10 rows and then adopt the logic suitably, by providing meter, etc.

This is the browse of converted dbf with 10 rows

Image
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Converting CVS files

Post by nageswaragunupudi »

ADutheil wrote:You should set all fieds type as "C" in your DBF and use APPEND FROM DELIMITED command. No need to specify the delimiter as CVS is default.
Yes
You can do this first and later convert character dates to datetyoe
Regards

G. N. Rao.
Hyderabad, India
HunterEC
Posts: 723
Joined: Tue Sep 04, 2007 8:45 am

Re: Converting CVS files

Post by HunterEC »

Rao:

Thank you very much. It worked out perfectly with the sample data, with the full file (4.61GB) it does not work.

André:

With the full file (4.61GB) the APPEND FROM <filename> DELIMITED does not work.


Thank you guys.
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Converting CVS files

Post by nageswaragunupudi »

when you say it does not work, please let us know if the program stops with any errors and if so what are the errors.
André:

With the full file (4.61GB) the APPEND FROM <filename> DELIMITED does not work.
APPEND FROM ... DELIMITED is very reliable and should work well. For this you need to set field types of field no.37,38,40 and 41 as character type width 10. Then this would work well.



Always importing from such data is not smooth. Main reason being the data not being precisely in the required format through out the file. Somewhere some deviations will the there.

is it a One time Job?
If so try someway to send me the CSV file.
Regards

G. N. Rao.
Hyderabad, India
User avatar
carlos vargas
Posts: 1421
Joined: Tue Oct 11, 2005 5:01 pm
Location: Nicaragua

Re: Converting CVS files

Post by carlos vargas »

Rao,

do a few months, a user help request to import a CSV file of some megabyte since [x] Harbour had failed when processing a certain percentage, there was no way to import both xHarbour malfunction as Harbour, so much so that it turned better to make a small module in vfp for that specific process, this module hera not great but the appen from ... working properly and that is something that [x] harbor was not working properly. Now as I wrote it was a few months if not years, but in the changelog not observed work related to this incident. :-(


import using odbc for cvs?

http://www.htmlgoodies.com/primers/data ... nents.html

salu2
carlos vargas
Salu2
Carlos Vargas
Desde Managua, Nicaragua (CA)
HunterEC
Posts: 723
Joined: Tue Sep 04, 2007 8:45 am

Re: Converting CVS files

Post by HunterEC »

Rao:

When I say it does not work it means that the records are not appended. I changed the fields to character type and tried with the APPEND ... to no results. The file is in a 400MB zip file. Let me know how can I send it to you. Thank you.


Carlos:

Thanks for the tip. I also thing that xHarbour and Harbour are at fault on this topic.
User avatar
James Bott
Posts: 4654
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA
Contact:

Re: Converting CVS files

Post by James Bott »

You could always use the low level file functions to split the file into several smaller pieces, then import each piece.

Regards,
James
ADutheil
Posts: 352
Joined: Sun May 31, 2009 6:25 pm
Location: Salvador - Bahia - Brazil

Re: Converting CVS files

Post by ADutheil »

HunterEC,

the file you are importing might be corrupted at some point or some data might not fit the expected pattern.
Regards,

André Dutheil
FWH 13.04 HB 3.2 BCC 5.82 MinGW 4.5.2 MSVS 10
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Converting CVS files

Post by nageswaragunupudi »

ADutheil wrote:HunterEC,

the file you are importing might be corrupted at some point or some data might not fit the expected pattern.
That's my suspicion too.
I would like to work hands on once I get the file. I like that kind of work.
Regards

G. N. Rao.
Hyderabad, India
Post Reply