Extracting/Importing Data from an Excel Spreadsheet. 1

[fmasin]

Hi,

I'm very new to the use of FoxPro Databases and commands. I have got a Product table in Fox Pro which is displayed in the following column structure:

SysKey |Product |Product Code |Description | Selling Price |
AAV3 Frame 20/100/95 Green Frames £10.00
AAV4 Beam 30/50B/95 Yellow Beams £10.50

etc.etc.

The Syskey is auto generated by the System. The rest of the information has to come from somewhere.

Now, I have got some of this information (thousands of records) in an excel spreadsheet and would like to extract and add them to the products table in the Fox Pro database.
The problem is, this information is in a completely different structure. How can I unpack it to have it displayed according to the above column structure?

This information is in the following structure for Frames:

Depth 100m 200m 500m 600m
Height
20m £10 £20 £30 £40
50m £15 £30 £50 £70
40m £20 £50 £60 £90

So, the Productcode is worked out by looking at Height/Depth/95. Looking at the Height and Depth gives the
selling price of the product. The description field is an addition.
Is there some form of code that I can use to break down this information into the above required structure.

Please, help me....somebody...if you can.

Thank you, Francis

 

[Steve Bowman]

if this is a 1 time conversion you can first save the excel data as a dbase III file this will give you the excel data in a format that you can easily use. Then whip up come code that will open both tables then in a do while !eof() loop
you can create the values you need.

I will make some assumptions here

that the 20m is how the data looks and it is a text value and the &10 is how the price looks and is a text value

alltrim(chrtran(height,'m',''))+'/'+alltrim(chrtran(depth,'m',''))+'/'+alltrim(chrtran(sellingprice,'&',''))

the chrtran() will strip the data you don't want ie the "m" or "&" as I said this is with some assumptions there are other methods to use depending on the base value types

Steve Bowman
steve.bowman@ultraex.com

 

[fmasin]

Hi Steve,

Many thanks for your prompt response. Yes...this is a 1 time conversion... but as I said, I'm very new to Fox Pro Databases. How can I save the excel data as a dbase III file?
And could you explain to me a little bit more on the "do while !eof() loop"?

Thanks again,
Francis

 

[Steve Bowman]

you just open the excel file with Excel then do a File-->SaveAs option, in the file SaveAs dialoge along the bottom you will find a drop down list of available save file types.
choose the Dbase III type Steve Bowman
steve.bowman@ultraex.com

 

[fmasin]

Yes....Steve,

Thanks again....I have saved the file as a dbase III. What should I do now?

Thank you, Francis

 

[Steve Bowman]

to make this very quick, if you don't mind. Would be to email me both data files your main table and the excel.dbf file. I really need to know the structure of the tables I will return them with some starter code to help you understand how this works. You can email them to steveb@ultraex.com Steve Bowman
steve.bowman@ultraex.com

 

[fmasin]

Hi Steve,

Sorry for failing to get to you any earlier than this...your response came just towards the end of my working day yesterday. I just came in this morning and now we can resume. I shall forward to you both files shortly.

Thank you again for this unbelievable support!

Kind regards, Francis