0

I need to import to my database a csv file generated by my bank, I'm working with Laravel 5.6 and Laravel-Excel to read the file, the thing is that the amount field comes in a weird format and I do not know how to import that correctly to my db. According to the bank the field format is numeric(18,2) but the amount field comes with a lot of leading zeroes and not a decimal separator:

This is the real amount 700.00 and this is what it comes in the csv file 000000000000070000

Is there a way to manage or correctly format that number? The bank only exports data in that way.

Douglas Roos
  • 613
  • 10
  • 28

2 Answers2

2

Divide all the amounts to 100 and the result will become the "normal" form. Also all leading zeroes will be eliminated.

Kondybas
  • 686
  • 6
  • 15
  • Be *very* cautious about converting currency to floating point types. If it's not critical in your application then fine, but be aware of the issues: https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency – Travis Britz Nov 04 '18 at 21:00
  • @TravisBritz `mysql` has a numeric type `DECIMAL` intended for currency values and I suppose that Douglas is aware of difference between `DECIMAL` and `FLOAT` or `DOUBLE`. – Kondybas Nov 04 '18 at 21:13
  • @Kondybas thank you, that is a useful point for someone using MySQL. – Travis Britz Nov 04 '18 at 21:37
  • @TravisBritz yes, my DB has decimal fields, I'm aware of the difference between those fields. – Douglas Roos Nov 05 '18 at 16:45
1

It depends on how you are storing monetary values. If you are storing them as decimals the do as @Kondybas suggests. Otherwise, you may consider storing them as their whole, lowest-denomination values; i.e. in cents/pence.

Michael
  • 4,282
  • 9
  • 55
  • 89