Numbers CSV and decimal comma

I have a similar problem as well. It seems the bug is not just related to CSV import, but even to copy&paste. If I copy&paste a number in a spreadsheet it is not processed correctly - e.g. pasting "0.123" on a German system will convert it to "123".

I found a rather ugly workaround - quit Numbers, switch the system language to English and relaunch numbers. It now can import CSV files properly.

There is another problem, though - the original spreadsheet is still in German although the System language has changed. But at least the import works.


So I have three questions:
- Is there a way to import raw data into Numbers? I can use OpenOffice to import the data and store it in Excel format which can be read by Numbers. But there shouldn't be a need to have several Spreadsheets installed, should there?
- How is copy&paste supposed to work? Shouldn't it at least paste a string "0.123" instead of a wrong number? Or should it convert the numbers between different formats? It's a little ironic that a Spreadsheet can work with graphics and movie files, but not with numbers ...
- Is there a way to change the language of a Spreadsheet and all formatting of numbers and dates that goes with it?

+On my french system, pasting 0.123 or 0,123 give me correct results which are 0.123 or 0,123.+

Just tried it again - my system language is still switched to English. Thus, a new spreadsheet is in English. I enter 0.123 and 0,123 into TextEdit, copy and paste both into the spreadsheet.

0.123 stays 0.123

0,123 becomes 123 - the bug seems to exists in the English version, too.

+When I open a spreadsheet written by an English user it opens in French on my machine.+

If I reopen my old spreadsheet, the format for numbers has changed - it is now English as it should be and pasting "0,123" gives the incorrect "123" again.

BUT the names for the Styles are now still in German. Thus, if I pass my spreadsheet started on a German system to someone using an English system, they still get to see the German names for the styles (and probably other things I have not yet discovered).

Sorry, this fact was a little obscure in my original mail. Please allow me to rephrase my question a little: Is there a way to START and USE an English-speaking spreadsheet on a German system without changing the global language?

Actually, it may be that the bug does not show up in a French system, but only on German systems?

+Here Numbers works for text, pictures AND numbers (as you can check on the numerous screenshots I already posted)+

Text and pictures work very well for me, too. It looks and feels like working with Keynote. Now, with Keynote I have been avid user since the initial release. It was brilliant from the start and had all the features I needed. (And I don't know too many other people who missed crucial features.)

Now, I have been trying out Numbers for a few days only. And I already experienced lots of bugs and severe limitations. Either the features I am looking for don't exist or cannot be found where I would expect it. To me it looks like a piece of Alpha-software that has never been tested under heavy load or by many users. Had Apple released it as a public Beta in this state I would consider it an ingenious invention with lots of potential and would look forward to the first release. But selling it as a version 1.0.1 and as a competitor to Excel - which has been used intensively and developed for years - is not very convincing. I hope Apple will invest the effort to make it usable before too many people try it out and abandon it for a proper Spreadsheet :/

(I shouldn't be complaining here, I know. But it makes me feel better ...)

Yes, it works!

Thanks a lot, this is exactly what I have been looking for! One problem solved! 🙂

When I paste 0,123 in my system, numbers interprets it as 123 (which is the correct value for English systems.)

Regards,

WWJD,

You get the same behavior I get. However, I consider this to be a bug while you consider it to be a feature.

The original post referred to importing CSV files and for them both Yvan and myself seem to agree that the behavior is a bug.

For copy&paste you certainly do have a point - I had not thought about it this way before. Still, I believe this behavior to be incorrect or at least "highly counterintuitive".

Let me illustrate why with two examples: The thing is that if I paste "1,230" it could either be "one thousand two hundred and thirty" or "one point two three zero". In this case there is some ambiguity of what the user means and the system language setting is there to clarify what is what.

However, for "0,123" and "0.123" the interpretation of "zero thousand and ..." is rather far-fetched. I don't know too many cases where one really writes "0,123" instead of "123". In this case I wish for an intelligent copy&paste that recognizes numbers in different formats. The interpretation of "zero point something" is much more sensible than "zero thousand something".

I just tried the same thing out with AppleWorks and with NeoOffice (both latest versions). AppleWorks pastes a number for "0,123" and a string for "0.123". AppleWorks also pastes strings for both "1,230.4" and "1.230,4". NeoOffice does the same as Numbers (but unlike Numbers it is able to correctly import formatted text files with raw data - did anybody figure out how to import numbers with Numbers so far?).

Given what I wrote above I think that AppleWorks does exactly what I expect it to do and how I would want my Spreadsheet to behave. It reads in a number if it recognizes a number unambiguously and it pastes a string in all other cases.

No wonder I had extra wire after my job was finished! (a little Numbers humor there...) Numbers has had issues with several localizations being able to correctly interpret differences in how each language type handles these types of characters.

Regards,

+OK, I agree that it's a bit surprising and will ask Apple about that.+

Thank you for your help.

+I imported more than 20 sheets from AW6.+
+The problems I met where not linked to numbers format.+

Sorry if I have not made my question clear.

I was asking if anybody here knows any way to import data from a text file, let's say a white-space separated lists of elements. In my case I tried to import numbers, but it may also refer to other data types like strings and dates.

One possibility is to use gawk/sed on the command line to produce a CSV-format readable by Numbers. Another possibility is to use NeoOffice's import function and then export to Excel and reimport with Numbers.

Is there any third possibility which does not require the use of Unix textutils or a third-party spreadsheet?

Excel (at least the Windows Versions) can import almost any type of delimited text (and non-delimited for that matter.)

Regards,

Hi Yvan,

I am from an English speaking country but.... have had many trials and tribulations exporting mainframe data to/from Excel.

An Excel feature I like is the ability to CHOOSE my delimiter, be it a comma or semi-colon or tab or whatever.

This doesn't cover all problems as I still need to choose "whatever" as my delimiter if the fields contain certain characters such as double quotes AND commas AND semi-colons.

Ideally Numbers would allow us to choose a delimiter manually PLUS also choose the special character (I always use an ASCII character unable to be normally input from a keyboard) to indicate a "quoted" field.

So the problem of having to massage data to make it work to/from Excel or Numbers is not just confined to non-English or mixed language setups. A good compromise solution for everyone is to allow us to choose the special characters.