Rows to Columns

[Mwhite]

I have two columns of data, column one has the field name, column two has the data in relation to the field name. Each record is 200 rows and I have more than 16,000 rows. I want to make column one the field header and column two the data listed in rows beneath the field or column header.

My question is can this be done in alpha 5 and how?

Thanks

Melvin

[Stan Mathews]

Re: Rows to Columns

It can be done but there may be alternatives. What type of file is the data presently saved as?

[Mwhite]

Re: Rows to Columns

thanks Stan

I opened it with Excel 2003, but the file came to me as a type (File)

Melvin

[Stan Mathews]

Re: Rows to Columns

In Excel. Hilight the data only. Copy it. Move to an open area of the spreadsheet. Click a cell. Choose Paste Special. Select the Transpose checkbox. Click ok.

You can then hilight the new area and save as dbf to create a table.

[Mwhite]

Re: Rows to Columns

Hello Stan,

I played with the transpose in Excel last night and it gave me one long row of all my headers and one long row of data. Can I format the spreadsheet to give me one row of headers and all the data below in a column?

Thanks

Melvin

[Stan Mathews]

Re: Rows to Columns

Must be like?

field1	abc
field1	def
field1	ghi
field1	jkl
field1	mno
field1	pqr
field1	stu
field1	vwx
field2	123
field2	234
field2	345
field2	456
field2	567
field2	678
field2	789

[Mwhite]

Re: Rows to Columns

Correct, and i want it the other way headers with the data below.

[Stan Mathews]

Re: Rows to Columns

I have the outline of what you need but it will take some more details. Do all the "groups" of fields have the same number of entries? IOW same number of field1's and field2's and field3's, etc?

If you want to just send to me/attach the file here I could process it for you.

What I've done is practice with a simple file here. (three groups of 8 values)

FIELDNAME	DATA
field1	abc
field1	def
field1	ghi
field1	jkl
field1	mno
field1	pqr
field1	stu
field1	vwx
field2	123
field2	234
field2	345
field2	456
field2	567
field2	678
field2	789
field2	890
field3	111
field3	222
field3	333
field3	444
field3	555
field3	666
field3	777
field3	888

I saved this as an Alpha table ("orig"). I created another table ("dest", clever names no?) with fields field1, field2, and field3.

This script reads orig and creates 8 records in dest. It then cycles through the next 8 records and changes the value in the next field for all eight records, and repeats until the orig table end is reached.

dim org_tbl as p
dim dest_tbl as p
org_tbl = table.open("orig")
dest_tbl = table.open("dest")
while .not. org_tbl.fetch_eof()
	fld_name = org_tbl.Fieldname
	for i = 1 to 8
	dest_tbl.enter_begin()
	eval("dest_tbl."+fld_name) = org_tbl.Data
	dest_tbl.enter_end(.t.)
	org_tbl.fetch_next()
	next i
	if i = 8
		exit while
	end if
end while

fld_name = org_tbl.Fieldname
while .not. org_tbl.fetch_eof()
	dest_tbl.fetch_first()
	for x = 1 to 8
	dest_tbl.change_begin()
	eval("dest_tbl."+fld_name) = org_tbl.data
	dest_tbl.change_end(.t.)
	org_tbl.fetch_next()
	dest_tbl.fetch_next()
	next x
	fld_name = org_tbl.Fieldname
end while

org_tbl.close()
dest_tbl.close()

[Mwhite]

Re: Rows to Columns

Hello Stan

The header column has 200 rows and it repeats all the way down the spreadsheet. The Data column has 200 rows and some are blank. i have been trying to use transpose in Excel, one at a time. I attached the file and i have 16 more to do.

Thanks for your help

Melvin

[Stan Mathews]

Re: Rows to Columns

Ooh, that's ugly.

The first column doesn't have usable field names so my plan won't work. There are apostrophes, spaces, #, and blank lines. Some field names are repeated two or more times.

[Mwhite]

Re: Rows to Columns

Thanks for your help