1
$\begingroup$

I'm trying to process my bank's data, which is always formed by lines with 5 fields separated by semicolons. Until recently, it used to write the numbers in the "1234,56" format, and I processed them by 

Import[NotebookDirectory[] <> "cc.wri", "Table", 
  "NumberPoint" -> ",", "FieldSeparators" -> {";"}]

Recently the bank started using the "1,234.56" format, and since I prefer to use a single file containing all my transactions history, I am wondering if it is possible to read from a file both type of number formats.

I tried using ToExpression[], but without success, and it seems that NumberPoint doesnt allow for multiple choices

-> fake cc.wri file: https://pastebin.com/MviN6SBC

update: I checked that this works, but still dont know if it's the best way to do it:

a=Import[NotebookDirectory[] <> "cc.wri", "Table", 
      "NumberPoint" -> ",", "FieldSeparators" -> {";"}]
a4 = 
 Map[If[NumberQ[#], #, Internal`StringToDouble[#]] &, 
  a\[Transpose][[4]]]

(Internal``StringToDouble idea from Faster way to convert real strings into numbers)

Improve this question
$\endgroup$
4
  • $\begingroup$ Is it guaranteed that a file won't have a mix of numbers in both formats? $\endgroup$
    – J. M.'s lack of A.I. ♦
    Nov 13, 2017 at 14:26
  • $\begingroup$ No - maybe I didnt explain me well - the file will use the first format until a given line, then switch to the second format from then on $\endgroup$
    – alessandro
    Nov 13, 2017 at 14:37
  • $\begingroup$ "until a given line" - would you happen to know how to detect that line? It's hard to discuss possible solutions since you didn't furnish a sample file. $\endgroup$
    – J. M.'s lack of A.I. ♦
    Nov 13, 2017 at 14:42
  • $\begingroup$ I added a simple fake file - in any case I wonder if it is possible to have both number formats detected in general, wherever they appear in the file $\endgroup$
    – alessandro
    Nov 13, 2017 at 14:52

1 Answer 1

Reset to default
1
$\begingroup$

You might Import the whole file as text, do a string replacement like this,

StringReplace["1,234.56  1.234,56" ,
  d : ((DigitCharacter | "." | "," ) ..) ~~ ("." | ",") ~~
   c : (DigitCharacter ~~ DigitCharacter) ~~ (EndOfString | 
     Except[DigitCharacter]) :>
  StringReplace[d, {"." -> "", "," -> ""}] <> "." <> c]

1234.56 1234.56

then ImportString the result..

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.