Importing CSV files into different DBF templates based on filename

[eboughey1008]

I created several different templates for my data files when I import from csv to my dbf and the program should know which template to use for importing by my naming conventions. For instance, one csv file has Name, address, address2, city, state, zip (Car dealer PC list stru1.csv). The second template doesn't have an address2 field (Car dealer PC list stru2.csv) and yet another template has first and last name instead of name field (Car dealer PC list stru3.csv).

I have a do while to import below but it isn't working (it keeps using the wrong templates), plus I have to rewrite the same code for each do while statement. I'd like to set up an 'if' statement so that the program looks at a filename and determines which .dbf file to use and import. Below is what I currently have:

lcFile = Sys(2000, lcPathAndMask)
thefile = "mailfile-import2.dbf"
use alltrim(lower(thefile))

ZAP
lcPathAndMask = alltrim(lcprefix)+"* PC*.csv"
Do While !Empty(lcFile) and 'stru2'$lcfile *** this uses mailfile2-import.dbf to import ***
APPEND FROM (lcFile) csv
replace listsource WITH (lcfile)for EMPTY(listsource)
lcFile = Sys(2000, lcPathAndMask, 1)
ENDDO
repl all list_no with left(CLIST_NO,5)
repl all tag with recno()

CLOSE ALL
clear

lcFile = Sys(2000, lcPathAndMask)
thefile = "mailfile-import.dbf"
use alltrim(lower(thefile))

ZAP
lcPathAndMask = alltrim(lcprefix)+"* PC*.csv"
Do While !Empty(lcFile) and !'stru2'$lcfile *** this uses mailfile-import.dbf to import ***
APPEND FROM (lcFile) csv
replace listsource WITH (lcfile)for EMPTY(listsource)
lcFile = Sys(2000, lcPathAndMask, 1)
ENDDO
repl all list_no with left(CLIST_NO,5)
repl all tag with recno()

CLOSE ALL
clear

 

[JRB-Bldr]

Personally I have no idea why you are using the DO WHILE's.
You aren't making any changes within the DO WHILE that would lend itself to a need for them.
Once you have done the APPEND on the single import file, you are done.

I have done something similar in a couple of ways depending on how 'standardized' the filename was.

If the import filename was Not Standardized, then I merely:
1. used Low Level functions to Open the file
2. read in the first 'line' of the file into a string
3. used Low Level functions to Close the file
4. compared the first 'record' string to a set of 'standardized' templates
5. then the application 'knew' what to do and then did it.

However if the import filenames were very 'standardized' then I just set up a DO CASE/ENDCASE and worked on the files as needed.

DIMENSION aryDir(1)
nImportFiles = ADIR(aryDir, <import directory> + "*.csv")
IF nImportFiles > 0
   FOR FileCntr = 1 TO nImportFiles
      cFile = <import directory> + aryDir(FileCntr,1)
      DO CASE
         CASE JUSTFNAME(cFile) = <whatever_1>
            USE ImportTemplate1
            APPEND FROM (cFile)
            < do whatever >

         CASE JUSTFNAME(cFile) = <whatever_2>
            USE ImportTemplate2
            APPEND FROM (cFile)
            < do whatever >

         CASE JUSTFNAME(cFile) = <whatever_1>
            USE ImportTemplate3
            APPEND FROM (cFile)
            < do whatever >
      ENDCASE
      CLOSE DATABASES ALL

      * --- Move Import File out of Directory so that it is not used again ---
      COPY FILE (cFile) TO <somewhere else>
      ERASE (cFile)
   ENDFOR
ENDIF

NOTE - no DO WHILE loops used in either situation.

Lastly it sounds as though you are not using VFP's Debug tools (Breakpoints and/or SET STEP ON) to single step through your code to find out specifically where it is going wrong. You might want to do that first.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

JRB-Bldr, perhaps take a look at SYS(2000), the DO WHILE loops iterate all files of a directory.

Thw logic is wrong, if not all files with same strucutre are in alphabetical order, you only process some of them.

First loop only runs, if the first file you find has 'stru2'$lcfile, if not, the first loops while condition is not fulfilled and all further files are not looked for having 'stru2' in the name.
Overall you skip a lot of files and don't import them.

You should only do one loop only checking for !EMPTY(lcFile) and within the loop do a DO CASE..ENDCASE for the file types.

Second mistake: !'stru2'$lcfile
You have told about stru1, stru2 and stru3. Then you may have totally different files, you should always test for a specific struN for a certain import, even if you import stru1 and stru3 in the same manner, you still also let thru files with no struN at all or totally different files, if you don't filter them by the file skeleton name used in sys(2000).

If you want to stay with iterating all files of a directory twice, then you should set the file skeleton parameter to only return stru1 files for the first loop and stru2 for a second, stru3 for a third loop. The while condition kills you, even if all stru1 files are first, whn you restart the first file is not stru2 and you don't process any stru2 files at all. And vice versa. If file names are mixed you mostly will process one to a few files of the first type of files and then stop, all other iterations don't even start as the first file doesn't fulfill the while condition.

While works this way:
Think of a sequence 1,2,3,4,...N (finite)

Do while number is odd
..
Enddo

This loop ends at 2, as 2 is not odd, it doesn't look at any more items of the sequence, the while condition is not fulfilled, the rest is skipped.

Restarting at 1 and doing:
Do while number is even
..
Enddo

This loop even doesn't start off. 1 is not even, so the whole sequence is skipped.

Bye, Olaf.

 

[eboughey1008]

I haven't had multiple templates before so the do while clause worked fine as it was. The client's data keeps changing up on me though and I want to find an easy way to select a template then run the import. Everything is the same except the field layout. I would rather do something like the below:

lcprefix = "e:\working\jobs\"+alltrim(CLIST_NO)+"-ad\"
lcFile = Sys(2000, lcPathAndMask)

if 'stru1'$lcfile then​

thefile = "mailfile-import.dbf"

elseif 'stru2'$lcfile then​

thefile = "mailfile-import2.dbf"

elseif 'stru2'$lcfile then​

thefile = "mailfile-import3.dbf"

endif​

endif​

endif

use alltrim(lower(thefile))

ZAP
lcPathAndMask = alltrim(lcprefix)+"* PC*.csv"
Do While !Empty(lcFile)
APPEND FROM (lcFile) csv
replace listsource WITH (lcfile)for EMPTY(listsource)
lcFile = Sys(2000, lcPathAndMask, 1)
ENDDO
repl all list_no with left(CLIST_NO,5)
repl all tag with recno()

CLOSE ALL
clear

is there a way to do this? It seems the easiest and least time consuming but I haven't programmed in years and most of that programming has been manipulating current programs instead of writing them myself.

 

[Olaf Doschke]

I pointed to CASE

DO CASE
CASE condition one && IF
*do one thing
CASE condition two && ELSIF
*do another thing
CASE condition three && ELSIF
*do yet another thing
OTHERWISE && final ELSE
*do whatever to catch any other conditions
ENDCASE

You should rather
1. open up all tables for import and zap them
2. iterate all dbf files just with !EMPTY(lcFile)
3. within the iteration do a DO CASE to sort files into target tables

Bye, Olaf.

 

[JRB-Bldr]

Olaf - you are right in that I have never had any reason to use SYS(2000).
Consequently I did not recognize how it was being used.

But in VFP Help it is defined as:

Returns the name of the first file in alphabetic order by file name and extension, that matches a file name skeleton.​

That does not seem to meet the needs of what the user is asking.

However the ADIR() method, which I have always used, puts ALL of the matching file names into the designated Array, which can then be used as I have shown and done many times in the past.

Regardless, I hope that eboughey1008 has gotten the answer they need.
However I still recommend that they begin to use the VFP Debugging tools so that they can do some of their initial problem investigation themselves.

JRB-Bldr

 

[eboughey1008]

I'm not a great programmer like most of you out here. I primarily manipulate programs I've have for years or programs I had someone else write for me for harder programs that are unique to my data processing company.

I'm looking up info on ADIR() and DO CASE/ENDCASE. I'm not sure exactly how to incorporate either into my current program so I'm looking at other examples.

 

[Olaf Doschke]

Well, you have to read on, the code also is guiding you. With the second parameter ,1 you get following files.
The problem is the way the while condition stops processing all files, when a first file does not match the conditions, the rest of the files could still contain more files to proceess with the same structure.

Bye, Olaf.

 

[Olaf Doschke]

I could put something together but not take the final responsibility. You HAVE to understand the final code to know it's not only error free but doing what you need it to do.
I thought I explained CASE and how it compares to your If..elsif...elsif..else...endif. So you can use it in that place.

Bye, Olaf.

 

[Dan Freeman]

Olaf - you are right in that I have never had any reason to use SYS(2000).

There actually isn't much of a reason to use it. Back in Foxbase+ it was the only choice, but once Foxpro 1.0 got Adir() most of us stopped using it.

However, it's good to know about. Since every file found by Adir() begets five array elements, the array can become immense on a large directory listing. It's actually sometimes quicker to iterate file-by-file using sys(2000). (Although a directory listing THAT large tends to point to a design flaw. I might agitate to look into it.)

The other reason to prefer the Adir() approach is you can then write a FOR/NEXT loop that has a finite number of iterations rather than a DO WHILE that runs until it doesn't any more. My spidey sense tingles a little every time I write a loop that doesn't have a clearly defined end point.

 

[eboughey1008]

I guess my sys(2000) shows my age! got a good laugh when you talked about your spidey sense tingling with undefined loops...

My directories are really small; only a few files per job.

 

[Dan Freeman]

And my remembering it shows MY age. LOL

 

[Olaf Doschke]

I had to process directories of a "document management system" and then SYS(2000) turns out to be faster.
When should there ever be a directory with infinite number of files?

You also seem not to have read my article about Quantum Computer Science and Loop Quantum Gravity.

You're excused, because that was a german article. Here's a brief translation.

How does an infinite loop of the type [tt]WHILE 1=1[/tt] end?

Here Information Theory, Mathematics and Physics overlap in the field of modern theories in the outset of Heisenberg's uncertainty principle. There are commands, which have a short-term impact on usual mathematics and cause an uncertainty in the comparison of 1 with 1. In detail this also depends on quantisation of numbers and floating point arithmetics, but I don't want to go too far into the details here.

...

In SQL Server you have the BREAK command which for a short moment breaks the mathematical equality of 1 and 1, so you can escape the loop. (Remark: VFP has the more comfortable and less rigorous EXIT for that.)

By the way - that's only possible since CPUs are clocked at GHz frequencies. Another proof of how the laws of quantum mechanics influence all our lives today.

Bye, Olaf.

 

[Dan Freeman]

Yes, heavy-weight document processing apps are the sort of thing I had in mind.

I had a situation where a "listener" simply observed a folder looking for incoming files from external sources. There was no predicting the volume in advance. I ended up using Adir() *anyway* just because it simplified the code and I actually needed to slow it down. (It sent email confirmations of receipt of the file, and the email server had a throttle of <x> outbound per minute -- I even ended up putting in a sleep() on every iteration of the loop!)

I'll usually go for readable/maintainable over chasing microseconds but there are times when it makes a big enough difference to go the other way.

 

[eboughey1008]

the case/enddcase isn't working for me. I'm still working on it though and will upload the final code that actually works... committed to getting this done!

 

[JRB-Bldr]

When you say CASE/ENDCASE isn't working for you, how so?
You can't just take the code you have above and replace the DO WHILE/ENDDO statements with CASE/ENDCASE.

Is the CASE statement not finding a 'match'?

What are you using for <something> in your CASE <something> lines?

* Do you have 'Standardized' file naming conventions and are therefore using part of the filename in your CASE?
* Or are you using low-level FOPEN, FGETS() statements to get the first line of the CSV file and then using that in your CASE?
Example:

   nSourceHandle = FOPEN(cCSVFile)
   IF nSourceHandle > 0
      * --- 'Read' First Line From CSV File ---
      cHeaderRecord = FGETS(nSourceHandle)
      FCLOSE(nSourceHandle)  && Close the file so that it can be used elsewhere
      <do something with it - like use it in a CASE/ENDCASE based on some criteria>
   ENDIF
   FCLOSE(0)

And then what are you doing once your CASE <something> has been met?

Rather than posting all of your code, just clarify the details where the problem appears.

Good Luck,
JRB-Bldr