how to make field with " into numeric one

In a CSV file, if a column contains numbers with commas (e.g., "1,000"), and the quote marks, I am using emacs to covert it into a recognizable numeric format.

Sample:

2023-12-31,CNY,"-1,200.00",cmbchine,WeChat Pay

copy the contents into emacs
run M-x org-table-convert-region, the text turns into the following:

| 2023-12-31 | CNY | -1,200.00 | cmbchine | WeChat Pay |

replace "," with empty string
replace "|" with comma ","
delete every comma in the end of each line
save the csv file. done.

Then the csv file can be inserted into a sqlite file using the command

sqlite-utils insert cmb.db fund_flow cmb2024.csv --csv

The schema can be checked with the command

sqlite-utils schema cmb.db

The output should be the following, which Amount can be seen in the float format.

CREATE TABLE "fund_flow" (
   [Date] TEXT,
   [Currency] TEXT,
   [Amount] FLOAT,
   [Description] TEXT,
   [Information] TEXT
);

Created 2025-01-06T11:26:47+08:00, updated 2025-01-06T13:24:47+08:00 · History · Edit

how to make field with " into numeric one

Related