Help, Converting an Excel File to Foxpro Table

[tgorrie]

Maybe someone can help me. I'm having trouble converting a field from a character to numeric. I cut and pasted some data from a page on the web "HTML Report" to an Excel spreadsheet, then I imported it to a Foxpro table. The field Im having trouble with will not let me change it from a character type to a numeric type without losing the data in the column. The original field I'm having trouble with had a hyperlink attached to every account number but I got rid of them. I cannot change the field in EXCEL either. Can anyone tell me why this is happening and how to correct it?

 

[jimstarr]

To change a column in a .dbf from character to numeric see the help for ALTER TABLE - SQL.

Jim

 

[tgorrie]

I tried "modi stru" on the table and changing the field from character to numeric. It changing "ALL" the daa in that field to "0".

 

[JRudisill]

One thought might be to just create another field as numeric and do replace all newfld with val(otherfld).

 

[tgorrie]

How do I copy all the data from one field to the other.

 

[tgorrie]

All the data comes out 0

 

[DSummZZZ]

To elaborate on jcrudisill's idea, change the column you're having trouble with to character. For demo purposes, we'll call it charcol. Do the append, then add another column numeric type, named something like numcol. Be sure and change the decimals if needed also.
Then, use the command:

REPLACE ALL numcol WITH VAL(charcol)

That will copy all the data from one field to the other.

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[rgbean]

This example might explain what you need to do.

CREATE CURSOR first (cNumbers c(5))
INSERT INTO first VALUES ("1")
INSERT INTO first VALUES ("35")
INSERT INTO first VALUES ("-5")
BROWSE 
ALTER TABLE first ADD COLUMN nNumbers n(5,0)
REPLACE ALL nNumbers WITH VAL(cNumbers)
BROWSE 
ALTER TABLE first Drop COLUMN cNumbers
BROWSE

Rick

 

[tgorrie]

The data is still coming up all 0's

 

[wgcs]

What does the data look like in the column? is the first character a number, or spaces, or quotes, or what?

The conversions using VAL() or MODI STRU or ALTER TABLE will only preserve the apparent numeric value of the string, ie:

'    5' -> 5
'5' -> 5
'"5"' -> 0
'(5)' -> 0
'!5' -> 0

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[baltman]

Is this web page on the Internet? If so what's the address?

The way I'd go is format the data EXACTLY the way you want it in Excel (I recommend left aligning all the data), select the whole range of data you want, and then SAVEAS type DBF 4 (dBASE IV).

You should then be able to close Excel and open the DBF 4 (dBASE IV with VFP.

Brian

 

[wgcs]

in your first post, you say I cannot change the field in EXCEL either.

How would you "change the field" in Excel? What does the data look like?

Try cut-and-paste from excel into Notepad

Or, in Excel, "Save As" a CSV file.

Then, look at the results (the notepad text, or the actual contents of the CSV file) and show us what the problem data looks like as pure text.

- Bill

Get the best answers to your questions -- See FAQ481-4875.