Visual FoxPro SQL Queries - Reference successive field names in FOR... ENDFOR

Author

Reference successive field names in FOR... ENDFOR

Kathy Weise

2006-03-23, 3:33 am

I have a table with 75 fields imported from Excel. The spreadsheet had 35
columns of zipcodes with a region for each column in the top row. To get it
into FoxPro, I transposed the spreadsheet from columns to rows so that my
first field is the region name and all the related zipcodes arein the other
74 columns.Next step is to create a table with two columns - the region name
and zipcode. I envisioned the way to do this is a FOR...ENDFOR loop which
doesn't work because I can't figure out how to get the loop to look at the
data in each of the fields:

FOR n = 2 to 74
SELECT region, column(n) as zip5 from mytable into table
"temp"+ALLTRIM(STR(n))
ENDFOR

*!* Then to get them all in one table

SELECT * from temp2 into table allzips
FOR n=3 to 74
append from "temp"+ALLTRIM(STR(n))
ENDFOR

Obviously, column(n) doesn't work in the first select statement.What does?
FIELD(n) only returns the field name and not the data.

Please help

Kathy

Bernhard Sander

2006-03-23, 7:51 am

Hi Kathy

> FOR n = 2 to 74
> SELECT region, column(n) as zip5 from mytable into table
> "temp"+ALLTRIM(STR(n))
> ENDFOR
you could use macro expansion:
FOR n = 2 TO 74
lcField = field(n)
SELECT region, &lcField AS zip5 FROM mytable INTO TABLE "temp"+alltrim(str(n))
ENDFOR

or do it all without temp tables:
USE mytable
SCAN ALL
FOR i = 2 TO 74
INSERT INTO allzips (region, zip5) VALUES (region, eval(field(i)))
ENDFOR
ENDSCAN

Regards
Bernhard Sander

Kathy Weise

2006-03-23, 1:35 pm

"Bernhard Sander" <fuchs@individsoft.de> wrote in message
news:e2JnyGnTGHA.736@TK2MSFTNGP12.phx.gbl...
> Hi Kathy
>
> you could use macro expansion:
> FOR n = 2 TO 74
> lcField = field(n)
> SELECT region, &lcField AS zip5 FROM mytable INTO TABLE
> "temp"+alltrim(str(n))
> ENDFOR
>
> or do it all without temp tables:
> USE mytable
> SCAN ALL
> FOR i = 2 TO 74
> INSERT INTO allzips (region, zip5) VALUES (region, eval(field(i)))
> ENDFOR
> ENDSCAN
>
> Regards
> Bernhard Sander

Bernhard,
I can't get either to work. On the first, I get an error saying "column AS
is not found"

On the second, I get the missing clause message.

Bernhard Sander

2006-03-23, 1:35 pm

Hi Kathy

[color=darkred]
> Bernhard,
> I can't get either to work. On the first, I get an error saying "column AS
> is not found"
>
> On the second, I get the missing clause message.

Hm, you told you get the big table (mytable) as an Import from Excel.
Maybe the field names in this table are invalid foxpro column names?
You can give some examples of these field names?

In the second example, maybe add the table name to the values fields:
INSERT INTO allzips (region, zip5) ;
VALUES (mytable.region, eval("mytable."+field(i)))

Regards
Bernhard Sander

2006-03-24, 3:29 am

Hi Kathy
You can get an array of field names using AFIELDS(aFieldArray)
.

CREATE CURSOR Exceltable (a N(5), b N(5), c N(5))
INSERT INTO Exceltable VALUES (12345, 56789, 98765)
INSERT INTO Exceltable VALUES (45678,93455,98567)
CREATE CURSOR Zipcodes (region c(5), zip n(5))
n=AFIELDS(aFieldArra
y,'excel')
FOR i = 1 TO M.n
x = aFieldArray(i,1)
INSERT INTO Zipcodes SELECT x, &x FROM Exceltable
NEXT

-Anders

"Kathy Weise" <kw@kwmkt.com> skrev i meddelandet
news:FkqUf.41203$_S7.36546@newssvr14.news.prodigy.com...
>I have a table with 75 fields imported from Excel. The spreadsheet had 35
>columns of zipcodes with a region for each column in the top row. To get it
>into FoxPro, I transposed the spreadsheet from columns to rows so that my
>first field is the region name and all the related zipcodes arein the other
>74 columns.Next step is to create a table with two columns - the region
>name and zipcode. I envisioned the way to do this is a FOR...ENDFOR loop
>which doesn't work because I can't figure out how to get the loop to look
>at the data in each of the fields:
>
> FOR n = 2 to 74
> SELECT region, column(n) as zip5 from mytable into table
> "temp"+ALLTRIM(STR(n))
> ENDFOR
>
> *!* Then to get them all in one table
>
> SELECT * from temp2 into table allzips
> FOR n=3 to 74
> append from "temp"+ALLTRIM(STR(n))
> ENDFOR
>
> Obviously, column(n) doesn't work in the first select statement.What does?
> FIELD(n) only returns the field name and not the data.
>
> Please help
>
> Kathy
>

Sponsored Links