APPEND COMMAND OF TEXTFILES INTO ONE TABLE USING GETDIR()

[GLABSOKENKEN1]

HI,

May I need your help, I have 15 to 20 text files daily, I need to append these files in one table (merged.dbf), I try using getDIR() function inside the command button to find the target folder w/c contained all the text files. but my problem is i need the code for automatic appended of all text files into one table (merged.dbg)

here sample from vfp screen (.scx):

APPEND ALL TEXT FILES TO ONE TABLE

---> CLICK 1st BUTTON HERE (Then find the target folder contained all textfiles)

---> Click 2nd BUTTON HERE for the APPEND (To append all textfiles to merged.dbf)

Can you please provide sample code or array to create program for multiple append. Thanks

Your reply is more highly appreciated.

 

[JRB-Bldr]

You cannot Append ALL files with one single line command.

However once you know the directory where the text files are, you can use the ADIR() command to get an array of all of the filenames which match the command 'skeleton'

Then, once you have that array of filenames, you can go through the Array and Append each separate file into the receiver DBF.

One thing to keep in mind is that ALL of text files have to be in the same format and data lay-out and that format needs to match the receiver DBF so that it would work.

You might want to look at the previously posted:

http://www.tek-tips.com/viewthread.cfm?qid=1766374

While the issue is not exactly the same, some of the suggestions or part of the suggestions are applicable.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Well, that thread is convoluted with other problems than processing a directory, though the goal is quite similar.

In that thread I stated I won't give out code trying to mend what he couldn't do. The crux of CSV import via APPEND is, APPPEND doesn't tell you about truncated import and other problems of a non matching DBF you append to. That person prooved to be too limited to understand what happens if files don't come in expected order and also not understands a simple CASE construct, so I assume he would also just take code not throwing errors for granted to work and I wouldn't risc that.

You can iterate over files of a directory this way:

#Define cnFilenameColumn 1
LOCAL lnNumberOfFilesFound, lnArrayRow
lnNumberOfFilesFound = ADIR(laFiles,Home()+"*.txt")
FOR lnArrayRow=1 TO lnNumberOfFilesFound 
? laFiles[lnArrayRow,cnFilenameColumn]
ENDFOR

There is already one thing to point out, notice the generated array column 1 contains only the file name, though I used a file skeleton parameter to contain the full path HOME(). The array does not contain the path.
Because I use Home(), this sample code should result in just one file REDIST.TXT, interesting read by the way, but it is just this mere file name, not [tt]C:\PROGRAM FILES (X86)\MICROSOFT VISUAL FOXPRO 9\REDIST.TXT[/tt] or whatever your HOME() path is.

The second important point is, the generated array is released after the code executed. So putting only the ADIR() into button1.click you don't have the laFiles array for button2 to process, as it vanishes right after being created. So - since you want one button to only produce the array the second button processes, you will need to copy the ADIR result array to some place it continues to exist, when the second button is clicked. You'd either do ACOPY to a form or control property or you convert the array into a cursor by creating one and SQL-Insert into it or APPEND FROM ARRAY. Just to be clear, that merely appends the filenames of the array ADIR created into some cursor or DBF you prepared for that intermediate result of the file name list, not the files themselves.

I think you can do it from there, you iterate all file names either in the array or in the cursor and then append each single file to your import DBF. Just to stress once more, what JRB-Bldr said:

ALL of text files have to be in the same format and data lay-out and that format needs to match the receiver DBF so that it would work.

You have to understand APPEND does not throw an error when part of a text line is not imported but truncated. APPEND also isn't good with data conforming to CSV rules having multiline text fields delimited with double quotes. VFP treats the linefeeds within such a text value as record separator.

You may have much more work ahead of you, than you think.

Bye, Olaf.

 

[Olaf Doschke]

A little off topic verbose explanation how weird it is ADIR creates an array and what its scope is, and what scope is in general anyway:

A thing to understand conceptual in the VFP language is when and how variables are generated and in what scope they exist and release.
VFP allows to generate variables on the fly without the declaration commands LOCAL or PUBLIC (and some other possibiliites I spare to explain for not getting too verbose)

You can do something like [tt]ldToday = DATE()[/tt] without first declaring ldToday via [tt]LOCAL ldToday[/tt]. (Besides not needing to specify a type, again a topic I leave out.)

What you can't do is access a variable, before it exists. [tt]? tdToday[/tt] works after setting tdToday, but not before. Besides, it compiles anyway, the compiler does not judge, whether the variable may exist at runtime.

What you also can't do for that same matter is using a variable name as parameter of a function, so [tt]DATE(lnYear,lnMonth,lnDay)[/tt] would error not finding these three variables, if they don't exist. So parameters of a function have to preexist and are not created here. If they would, they would get .F. and [tt]DATE(.F.,.F.,.F.)[/tt] also doesn't work. Again [tt]DATE(lnYear,lnMonth,lnDay)[/tt] compiles nevertheless, as variables may come in from anywhere else at runtime.

Now I get to the point where that relates to ADIR. ADIR also is a function and we pass a non existant and not predefined laFiles variable to it. From that perspective it should error. This is a special case, as you can also read in the help topic, actually we pass in the name of the array, in any other place in code this would be shorthand for addressing the first element of the array only. But we don't just pass one value, we pass the name of the array ADIR should - if it exists - use and extend, or - if it does not yet exist - create. This is something you can't do in a user defined function, I can't even tell you how that is done behind the scenes and how this is compiled, but it's noticable because of this. If you're not used to FoxPro, you must be confused why such things work at all. Even if you're used to weak typed languages.

The scope of variables not declared LOCAL or PUBLIC is private. That means they exist in the context of the current code until the next explicit or implicit RETURN (ie the end of a method), but also will be visible variables to code called from there. That also is true for the laFiles array variable created by ADIR, so you could call a form.method from here and it would be able to read laFiles, but laFiles is released and vanishes when you exit the code and don't persist it as something having a longer living context as the form object or a workarea of the form session. The private context and scope the ADIR works in is not the form oject, it's not the button object, it's the click method on the callstack, in the moment ADIR is done. So laFiles does not live as long as the form is there or the button, it only exists until the end of the click event. The scope of a property in comparison to that - no matter if native or user defined property and no matter if defined at designtime or added at runtime - is the scope and lifetime of the object it belongs to. So variables exist in the context/scope of callstack level. Properties exist in the context/scope and lieftime of their object, so you may persist the laFiles array by creating a form property via ACOPY. And then you can refer to this any time later, eg in another buttons click event code.

Bye, Olaf.

 

[JRB-Bldr]

So that you won't get confused by the other aspects of the referenced other question, here is a simplification of the suggestion of using ADIR()

* --- Get the Import Directory ---
cImportDir = GETDIR()

* --- Now use it to find the individual CSV files in that directory ---
DIMENSION aryDir(1)
nImportCSVFiles = ADIR(aryDir, ADDBS(cImportDir) + "*.csv")
IF nImportCSVFiles > 0
   * --- Cycle through the 'found' CSV files and Append Each one separately ---
   FOR nFileCntr = 1 TO nImportCSVFiles
      cCSVFile = ADDBS(cImportDir) + aryDir(nFileCntr,1)

      * --- Now Import that Specific File ---
      SELECT DestinationDBF  && The recipient DBF table
      APPEND FROM (cCSVFile) CSV

      * --- Move Import File out of Directory so that it is not used again ---
      COPY FILE (cCSVFile) TO <somewhere else>
      ERASE (cCSVFile)  && Remove this file from Import directory
   ENDFOR
ENDIF

Note that in order for this to work, all of the CSV files need to have the Same data format and that needs to match the field layout of the DestinationDBF. Otherwise you would need to utilize additional processing to determine what the CSV data format was and handle it accordingly (as discussed in the other referenced link).


Good Luck,
JRB-Bldr