export foxpro tables to excel

[axc25]

Hello! i have one table with the fields: ID, city, client_name. I need to generate a .xls file for each city. For example if i have 100 cities i need to generate 100 xls files from one command. is this possible? thanx!

 

[Dan Freeman]

From one command? No.

However, if your "one command" can be DO MYPROGRAM, then yes.

Let me say up front that you can create MYPROGRAM many different ways. I present here only one.

  Select id, city, client_name ;
    from (yourtable) ;
    sort by city ;
    into cursor foo

   Select Foo
   Do While Not EOF()
      filename = Trim(foo.city)
      Copy To &filename TYPE XLS While Trim(foo.city) = filename
   Enddo

 

[Mike Lewis]

Dan,

I think a small error crept into your code.

Instead of this:

Copy To &filename TYPE XLS While Trim(foo.city) = filename

do this:

Copy To &filename TYPE XLS [b]FOR[/b] Trim(foo.city) = filename

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Or mabye not. Is cursor Foo meant to be in City order? If so, the WHILE is good, but the SELECT needs an ORDER BY (not SORT BY).

mIKE

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

Yes, the typo is in the SELECT.

You wouldn't even need the SELECT if the original table carries an index on City. Just set order and use the same DO WHILE (doctoring for the different alias).

COPY TO ... FOR, in this context, wouldn't produce the right result unless you first produce a unique list of city values so the code would be written differently. I prefer the WHILE scope because the table (or cursor) is traversed in a single pass rather than requiring the in-memory bitmap construction of the FOR syntax on each COPY.

 

[axc25]

thank u very much both of u!!! it worked

 

[baltman]

See my FAQs for better formatted output, ability to make workbooks quickly and freedom from memo field limitations.