CSV Data Import Issue on DBeaver

[kathn57]

Hello everyone,

I am new and a beginner with DBeaver, and I am encountering quite a few problems regarding the import of my CSV data into DBeaver, which I cannot solve. Here is the message I receive with each attempt: "An error occurred during batch insert (you can disable batch insert in order to skip particular rows). Reason: Can't parse numeric value [Z] using formatter."

I consulted an IT service provider who explained to me that the error came from an issue with the data type for certain columns in the CSV and proposed two solutions:

• Create a table based on the definition of another table that follows the same data schema, and then import the data from the CSV file into it.
• Create the table at the time of importing the CSV file with DBeaver, but in this latter case, the data types for the columns will need to be checked during the import, as by default, the types are not correct.

So far, I have used both solutions, and they seemed to work. However, I encountered the problem once again despite using either method, and I have thoroughly checked the data type in each column. I even tried converting all the data to character format to see if the problem persisted, and it did.

Has anyone else encountered this problem? How did you resolve it?

Thank you in advance for your help.

[E1izabeth]

Hi, @kathn57! Could you provide an example CSV file and table DDL so we can reproduce the issue and figure out what's happening?

[kathn57]

Hello! Thank you for your response. Below you will find a CSV file:

FD_INDCVIZC_2021_KA.csv

and here is the DDL of the table obtained directly when I try to import the

CREATE TABLE tests_klervi.fd_indcvizc (
"CANTVILLE" integer NULL,
"NUMMI" integer NULL,
"ACHLR" integer NULL,
"AEMMR" integer NULL,
"AGED" integer NULL,
"AGER20" integer NULL,
"AGEREV" integer NULL,
"AGEREVQ" integer NULL,
"ANAI" integer NULL,
"ANEMR" integer NULL,
"APAF" integer NULL,
"ARM" varchar(8) NULL,
"ASCEN" integer NULL,
"BAIN" varchar(1) NULL,
"BATI" varchar(1) NULL,
"CATIRIS" varchar(1) NULL,
"CATL" integer NULL,
"CATPC" integer NULL,
"CHAU" varchar(1) NULL,
"CHFL" integer NULL,
"CHOS" varchar(1) NULL,
"CLIM" varchar(1) NULL,
"CMBL" integer NULL,
"COUPLE" integer NULL,
"CS1" integer NULL,
"CUIS" varchar(1) NULL,
"DEPT" integer NULL,
"DEROU" varchar(1) NULL,
"DIPL" varchar(2) NULL,
"DNAI" integer NULL,
"EAU" varchar(1) NULL,
"EGOUL" varchar(1) NULL,
"ELEC" varchar(1) NULL,
"EMPL" varchar(2) NULL,
"ETUD" integer NULL,
"GARL" integer NULL,
"HLML" integer NULL,
"ILETUD" varchar(1) NULL,
"ILT" varchar(1) NULL,
"IMMI" integer NULL,
"INAI" integer NULL,
"INATC" integer NULL,
"INFAM" integer NULL,
"INPER" integer NULL,
"INPERF" varchar(1) NULL,
"IPONDI" real NULL,
"IRAN" varchar(1) NULL,
"IRIS" varchar(16) NULL,
"LIENF" integer NULL,
"LPRF" integer NULL,
"LPRM" integer NULL,
"METRODOM" varchar(1) NULL,
"MOCO" integer NULL,
"MOCO_DET" varchar(2) NULL,
"RECOMPOSEE" varchar(1) NULL,
"TAF" varchar(1) NULL,
"MODV" integer NULL,
"NA17" varchar(2) NULL,
"NA5" varchar(2) NULL,
"NAIDT" integer NULL,
"NBPI" integer NULL,
"NE17FR" varchar(1) NULL,
"NE24FR" varchar(1) NULL,
"NE3FR" varchar(1) NULL,
"NE5FR" varchar(1) NULL,
"NENFR" varchar(1) NULL,
"NPERR" integer NULL,
"NUMF" varchar(1) NULL,
"ORIDT" integer NULL,
"RECH" varchar(1) NULL,
"REGION" integer NULL,
"SANI" integer NULL,
"SANIDOM" varchar(2) NULL,
"SEXE" integer NULL,
"SFM" integer NULL,
"STAT_CONJ" integer NULL,
"STATR" varchar(1) NULL,
"STOCD" integer NULL,
"SURF" integer NULL,
"TACT" integer NULL,
"TACTD16" integer NULL,
"TP" varchar(1) NULL,
"TRANS" varchar(1) NULL,
"TRIRIS" varchar(8) NULL,
"TYPC" integer NULL,
"TYPFC" varchar(1) NULL,
"TYPL" integer NULL,
"TYPMC" integer NULL,
"TYPMR" integer NULL,
"TDM8" integer NULL,
"VOIT" integer NULL,
"WC" varchar(1) NULL
);

This CSV file is generally updated over the years, so I also have the DDL of the table for an older version (but when I try to use it for a new table, it doesn't work):

CREATE TABLE population.fd_individus_c_2020 (
id int4 DEFAULT nextval('population.fd_individus_d_2020_id_seq'::regclass) NOT NULL,
cantville int4 NULL,
nummi varchar NULL,
achlr varchar NULL,
aemmr varchar NULL,
aged int4 NULL,
ager20 int4 NULL,
agerev int4 NULL,
agerevq int4 NULL,
anai int4 NULL,
anemr varchar NULL,
apaf varchar NULL,
arm varchar NULL,
ascen varchar NULL,
bain varchar NULL,
bati varchar NULL,
catiris varchar NULL,
catl varchar NULL,
catpc int4 NULL,
chau varchar NULL,
chfl varchar NULL,
chos varchar NULL,
clim varchar NULL,
cmbl varchar NULL,
couple int4 NULL,
cs1 int4 NULL,
cuis varchar NULL,
dept int4 NULL,
derou varchar NULL,
dipl varchar NULL,
dnai varchar NULL,
eau varchar NULL,
egoul varchar NULL,
elec varchar NULL,
empl varchar NULL,
etud int4 NULL,
garl varchar NULL,
hlml varchar NULL,
iletud varchar NULL,
ilt varchar NULL,
immi int4 NULL,
inai int4 NULL,
inatc varchar NULL,
infam varchar NULL,
inper varchar NULL,
inperf varchar NULL,
ipondi float8 NULL,
iran varchar NULL,
iris varchar NULL,
lienf varchar NULL,
lprf varchar NULL,
lprm varchar NULL,
metrodom varchar NULL,
moco int4 NULL,
modv int4 NULL,
na17 varchar NULL,
na5 varchar NULL,
naidt int4 NULL,
nbpi varchar NULL,
ne17fr varchar NULL,
ne24fr varchar NULL,
ne3fr varchar NULL,
ne5fr varchar NULL,
nenfr varchar NULL,
nperr varchar NULL,
numf varchar NULL,
oridt int4 NULL,
rech varchar NULL,
region int4 NULL,
sani varchar NULL,
sanidom varchar NULL,
sexe int4 NULL,
sfm varchar NULL,
stat_conj int4 NULL,
statr varchar NULL,
stocd varchar NULL,
surf varchar NULL,
tact int4 NULL,
tactd16 int4 NULL,
tp varchar NULL,
trans varchar NULL,
triris varchar NULL,
typc varchar NULL,
typfc varchar NULL,
typl varchar NULL,
typmc varchar NULL,
typmr varchar NULL,
voit varchar NULL,
wc varchar NULL,
CONSTRAINT fd_individus_d_2020_pkey PRIMARY KEY (id)
);

Do you need anything else? Thank you for your help

[E1izabeth]

I reproduced the issue on PostgreSQL and created issue #36315 from this discussion. Importing this data into Google Sheets works fine, so it's a DBeaver issue. Thank you

[kathn57]

okay, thank you !