Importing numeric field with a different decimal mark

yongest

Forums

Hi Brian!
I need your help againg! Thank you very much in advance!
I´m importing a plain text file with numeric files where their decimal mark is different from my computer setting´s. My computer´s default decimal mark is a comma, while numeric fields in this file have a point.   So when I try to import, IDEA recognize that fields as a text ones.
I guess I could change the regional settings, restart the computer, try to import the file and change the setting and restart. It seems a little bit of a mess.
When you work with Excel and import a text file, Excel gives you the possibility to select the decimal mark. However I don´t seen anything similar in IDEA.
Hope you can help me with this. I´m sure that if there's a solution for this you will know.
Regards
 
 
 
 
 
 

Brian Element Wed, 03/08/2017 - 07:31

Hi Youngest,

Actually IDEA allows you to do this also, it is just something that not everyone know about.  When you start the import of the text file there is an option button, if you click on that you will be able to change what is used for the decimal separator and the thousands separator.  That should hopefully fix your problem, if not I will show you how to change the text field into the numeric field.

Good luck.

Brian

yongest Wed, 03/08/2017 - 15:58

Hi Brian,
Oops, it was right under my nose but I didn't see it.
 
Thank you very much Brian, you have been really helpful.
Regards

Brian Element Wed, 03/08/2017 - 20:58

No problem, it is one of those features that is kind 0f hidden and most of the courses might just touch on it if then even mention it.  Glad you got it working.

Crypto Mon, 10/30/2017 - 12:01

Hello, it seems that I have a similar problem: I'm importing files from Excel 2010 files, and the numeric values in IDEA are quite strange, only non decimals are showing up, and there is an added ".00,00" after it.
If I change numeric value to characters in IDEA, the number is displayed like in Excel (but some functions in IDEA are reserver for numeric values).
Some examples: In Excel -> In IDEA (as numeric with 2 decimals displayed)

  • 54,22 -> 54 .00,00
  • -965,00 -> -965 .00,00
  • 1,98 -> 1 .00,00
  • There is also a space between the number and ''.00,00'')

I'm using Excel in French, and I'm still using the evaluation version of IDEA (waiting for the full version soon).
Thank you for any tip you can give me to find how to import numeric values.

Brian Element Tue, 10/31/2017 - 07:21

Hi Cyrpto and welcome to the site.

I have seen this once when I was giving some training and some of the software was in French and IDEA was in English, it kind of gets confused.

Can you give me an example of how the field is imported and how it should look. 

You might also want to try changing your regional settings to English as this might fix the problem.

Thanks

Brian

Crypto Fri, 11/03/2017 - 09:27

Hi Brian, thanks for your previous answer.
To fully understand, I created a small Excel file with 4 entries to test it.
I put the shared Excel file there if you want to try it to see if the issue is coming from the file or IDEA : https://drive.google.com/open?id=0B3uFU1dC3lgRcm1WeU9rd2V4UkE
I put as well screenshots from the IDEA importation of the file. But It seems that IDEA is reading well the file when you are selecting the file (see ''Import'' screenshot). IDEA is in English, and Windows is in French on my computer (unfortunately just changing windows language isn't an option as I'm not the only user on this computer.)
Thank you, have a nice day.
Kevin.
 

Brian Element Fri, 11/03/2017 - 10:24

Hi Crypto,

I have seen this before and what the company was going to do was just make sure the apps are all in the same language.  Unfortunately I don't have the same environment as you do to experiment to see how you can get around this so I would recommend that you send an email to IDEA support with your problem and hopefully they should be able to help you out.  If you do that and have an answer I would appreciate it if you could post the answer here for anyone else that might have your problem in the future.

Thanks and good luck.

Brian

Ahmed Sun, 11/05/2017 - 06:03

Hi All,
I think it's related to the configuration of your system because you have (IDEA is in English, and Windows is in French )  the same problem one of my colleagues had,
Thank you.
 

Ahmed Sun, 11/05/2017 - 06:09

So If you whant to change go to "Region & Language " ---"Additional Setting" and you change "Symbol Decimal"
Thank you.

Crypto Mon, 11/27/2017 - 09:09

Hi all and thank you for previous replies !I just had the formation and asked about the problem I encountered but no issue was found throughout the software. The only solution we found was to uninstall and reinstall it (and by the way I put the french version for the formation).
Some of my colleagues also installed IDEA in English with the same parameter and with windows in French and none of them had this issue.
Have a good day !