Calling Microsoft Office Suite Guru's

[M5Man]

Quick question why do you get numbers saving in cells in excel with a green triangle within the cell ?

These numbers dont import into a database untill you left click on the cell and select convert to number, why when this is showing as a number is it being stored as text in the back end?

Ok two questions ;)

I look forward to your answers. :)

[eddarr]

I can't answer why other than to say that cells will allow you to type in anything you want. That doesn't mean excel understands the meaning of what you typed until you format it.

The green triangle is a warning that numbers are in cells formatted for text. You can turn off the warning in the tools/options menu.

I would start your entry by formating the areas to what you want them to be, general, text, currency, percent etc. This will make life easier and excel will handle things like negative numbers and decimel points better anyway.

[M5Man]

Hi Ed,
I take this workbooks off people before importing them and there was a problem on import, I found the above was the problem.

I was just trying to find out what the dat inptta was doing to create this triangle :confused:

Thanks for your input ;)

[eddarr]

I don't know much about other database software but Access is much more picky about formating than Excel is. It is under options as "Numbers stored as text".

[M5Man]

Eric,

Im actually inputting it into an xml first then putting it into an oracle based database

[tiler65]

http://www.mrexcel.com/forum/index.php

Try the above forum, absolute excel androids who know everything.

[M5Man]

Cheers but i was after a quick reply... from some normal people ;)

I dont want to get talking to some anoraks :lol:

[12mnkys]

Is you spreadsheet All numbers?

If so, then just highlight the whole sheet, right click, format cells, pick number and you should be set.

[M5Man]

Is you spreadsheet All numbers?

If so, then just highlight the whole sheet, right click, format cells, pick number and you should be set.

I know that thanks, but I want to know what caused it..... ;)

[arrgeebee]

It's a number stored as text. This should help:
http://office.microsoft.com/en-us/excel/HP030559001033.aspx

[M5Man]

Thanks Bob ;)

[OdiN1701]

Well if you're importing something from a CSV file or some system is exporting to an excel sheet with everything as text, even numbers, you'll run into this.

It might be easier to do the conversion in your database system - ie import it to a temporary table, and convert while moving to the actual table, then remove the temp table. But that gets more into needing code and such.

[M5Man]

Well if you're importing something from a CSV file or some system is exporting to an excel sheet with everything as text, even numbers, you'll run into this.

It might be easier to do the conversion in your database system - ie import it to a temporary table, and convert while moving to the actual table, then remove the temp table. But that gets more into needing code and such.

Its missing data cos of this on the import :(

[OdiN1701]

Its missing data cos of this on the import :(

What that usually means is the field type is wrong in the table you're trying to put it in.

i.e. a Number field, but the data in excel is being treated as text. You would have to import as a text field, then convert to number field. Ideally you would want the data in excel to be converted to a number so you don't have to bother with it.

[M5Man]

It's a number stored as text. This should help:
http://office.microsoft.com/en-us/excel/HP030559001033.aspx

OdiN1701
That was quoted a fe threads back by arrgeebee

What I want to know is why it happens and how to prevent it happend as when the excel is exported to .xml file then imported into oracle it misses all data off with the green triangle :(

[OdiN1701]

It happens because you are trying to import a text field into a number field in oracle, as I said above.

You need to convert that text field to a number field before your export - this is why Excel is giving you the warning that the numbers are stored as text. You need to convert it to a number before exporting, or figure out why it's being treated as text in the first place. Where are you getting your original data and how are you getting it into Excel that Excel is treating it as text?

I can't help you unless I know exactly what process you are doing, what programs you're exporting, importing, etc. with. You need to outline exactly what you are doing, step by step, in this process. Otherwise I'd just be guessing at what's going on and what is causing it.