Convert Excel Spreadsheet to useable table

[michaelc1970]

Hello! The following issue seems like it should have a simple solution, but I can't seem to figure it out, so help!! (I think the issue could even be solved in Excel, if I could figure it out. I tried using PivotTable to no avail).

I have an excel spreadsheet of price levels and prices which I have imported (see attached sample excel file). I plan to use the resulting table to post prices to another table based on a "price_level" field (1 through X) and a "grade" field (AAAA through XXXX).

The excel file has been given to me with the grades listed as headings (see example file) with the prices listed in columns.

I need to convert the file (in excel or Alpha) so I have field names called price_level, grade, and price. (see example file) This will allow me to post the price to another table if I match the price_level and grade. In other words, I'm converting an array-like spreadsheet to a "flat" table.

Please help me if you can.........the real spreadsheet is quite large so it would be a pain to do manually.

Thanks! Michael

[Doug Page]

Re: Convert Excel Spreadsheet to useable table

Michael,

Is this something that you would be doing on an ongoing basis or just once?

My initial thought is to do this in Excel first and then do a simple import to Alpha. If it is just once that you need, the coding is easier. Otherwise, more questions will need to be asked in order to program around file differences.

[michaelc1970]

Re: Convert Excel Spreadsheet to useable table

Doug,

This is probably a once-a-year update. It does not need to be an ongoing function accessed by the users. It can be doen "manually" once a year.

Thanks,

Michael

[Doug Page]

Re: Convert Excel Spreadsheet to useable table

Do you also require the Price Key column you show in your sample? That was not mentioned above.

Also, how many rows by how many columns?

[michaelc1970]

Re: Convert Excel Spreadsheet to useable table

Doug,

No, I don't require the Price_key column, I'm just showing that in the example as a unique identifier for each record. The spreadsheet is 45 columns by 19 rows.

Thanks....Michael

[Mike Wilson]

Re: Convert Excel Spreadsheet to useable table

michael,
45 columns by 19 rows. This is the incoming (top table in your example)? If it starts with AAAA then BBBB... etc.. what happens to the sequence for columns 27-45? You originally said (AAAA through XXXX). How does that equate to either 19 or 45?

[michaelc1970]

Re: Convert Excel Spreadsheet to useable table

Mike,

Sorry for the confusion!

The example of AAAA to XXXX is only used to simply describe a list of "Grades", to avoid publishing our entire price levels for the public to see. An individual grade might be "AWP5" or "BBR6" which denotes a certain grade of tree in the nursery business. These "Grade" values are shown as column headings in the original Excel file and are capable of any 4-character combination.

Right now there are 45 different "Grades" and 19 different price levels. In the final spreadsheet, I want to post the correct "Price", by simply matching a single "Grade" and "Price_level".

Please let me know if this clears up your question, or if I've muddled the waters even more!

Thanks.....Michael

[Mike Wilson]

Re: Convert Excel Spreadsheet to useable table

Michael
Try this.

Take the excel spread sheet that you are starting with and save it into the folder with your A5 database as a dBase IV file named Pivot_out. Add the Pivot_out table to the database (Control panel '+' icon)

Create a new table named Pivot_in with the three fields (field names and type must be exact):
Price_level - character - 4
Grade - character - 4
Price - numeric - 8 - 2 decimals

Copy and paste the following code into a new User Defined Function (UDF) named Pivot_table, or alternatively make it a script.

Run it. It will take the table with the original data (Pivot_out) and convert it to the new table that you want (Pivot_in). Note the first steps of the script empties the recieving table, which has to have exclusive rights (no one else is in the table) to operate.

Good luck and don't hesitate to ask questions if needed. By the way... any hops on that spreadsheet?

'=======  this code takes the data from the table Pivot_out and reshuffles it to the table Pivot_in

'start message
		ui_modeless_dlg_box("Message",<<%dlg%
		{position=3,3}
		{region}
		{endregion};
		{region}
	The pivot process has started...    ;
		{endregion};
		%dlg%)
		sleep(1)
		ui_modeless_dlg_close("Message")

' delete all records in the receiving table
	dim tblA as P
	tblA = table.open("pivot_in")
	tblA.zap(.t.)
	tblA.close()

' collect the field names (AAA, BBB) into an crlf() list
	DIM SHARED vLgrade as C
	vLgrade= table.external_field_name_get("pivot_out","N")
	vLgrade = *word_remove(vLgrade,1,crlf())
	'msgbox("report",vLgrade)

' cycle through the list and add the values to the emptied table
	dim vLGNow as C
	for each vLGNow in vLgrade
	
		DIM SHARED vLGprices as C
		DIM SHARED varC_result as C
		vLGprices = table.external_record_content_get("pivot_out",vLGNow,"","")
		'msgbox(vLGNow,vLGprices)
		DIM vGradeNow as C
		vGradeNow = alltrim(vLGNow)
				dim vcycle as N=0
				dim tbl as P
				dim vlevel as C
	
			for each vLGPitem in vLGprices
				vcycle = vcycle +1
				vLevel = alltrim(str(vcycle))
				tbl = table.open("pivot_in")
				query.filter = ""
				query.order=""
				query.options=""
				xi = tbl.query_create()
	
				tbl.enter_begin()
					tbl.Price_level = vLevel
					tbl.Grade = vGradeNow
					tbl.Price = val(vLGPitem.value)
				tbl.enter_end(.t.)
				xi.drop()
				tbl.close()
			next
	next
ui_msg_box("Pivot Report", "Pivot Complete")

[michaelc1970]

Re: Convert Excel Spreadsheet to useable table

Mike,

Wow! Thanks, I'll give it a try!

Sorry, we have a huge variety of trees, but no hops!

Thanks again for your time and great help!

Michael