javascript - How to properly store $ dollar values from a given excel spreadsheet into a postgresql database

Question

I'm trying to store dollar values in a postgresql database. I want to later be able to filter and manipulate the data.

I'm given monthly excel spreadsheet reports and my goal is to store all that data for later use. I've downloaded the csv. However, one field is dollar amounts with a $ symbol. I believe storing as money data type is not recommended and I want to store as a numeric data type. How would I go about removing the $ from the csv? would it be better to store as text? remove $ from file before anything? Just don't know which way to go with this.

标签： javascriptsqlpostgresqlcsv

Answer 1

你有一些选择。

在 Excel 中，您可以将格式从货币更改为数字，但这可能会引入舍入问题。

您可以将 CSV 原样导入临时表，并将字段作为字符串。然后，去掉 $ 符号并转换为十进制。

根据您用于导入文件的内容，大多数 ETL 工具将允许您即时转换该字段并去除 $ 符号并转换为十进制。

同样，你有选择。最好的取决于您的环境和业务规则。