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)

$\endgroup$
  • $\begingroup$ Is it guaranteed that a file won't have a mix of numbers in both formats? $\endgroup$ – J. M. will be back soon Nov 13 '17 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 '17 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. will be back soon Nov 13 '17 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 '17 at 14:52
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..

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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