How to import 'null' value?

rembo · September 30, 2021, 3:28pm

Hello ,
I am trying import This CVS file to the project :

"id","appoint_type","appoint_date","time","addres_from","adress_to","customer_name","customer_phone","extra_note","driver_name","leader_id","customer_email","aprove_file","status","map","latitude_from","longitude_from","latitude_to","longitude_to","price"
"19","2","2021-03-22","00:30:00","961 Omar Ibn El-Khattab,","XXG3 XV9, First 6th street","CustunerName","1306441031","","7",Null,"Custmer@gmail.com","","1","","29.96","30.942","29.977","30.955","23"

but I got this error :

Imported 1 of 1 records from appointments (3).csv (success: 0, failure: 1)
row 1: Invalid field value (field name leader_id): Null

Null value is Invalid field value.
how can I supposed import Null value ?
Also if I want to export The Current Value of field value not the View Value , how can I do that ?
Thanx …

arbei · October 1, 2021, 1:24am

CSV is text file, your Null in the file will be read as “Null”. If your field allows NULL, you may just use “” for the field.
To export original values, see Tools → Advanced Settings → Export original values.

rembo · October 1, 2021, 7:51pm

arbei wrote:

CSV is text file, your > Null > in the file will be read as > “Null”> .
If your field allows NULL, you may just use > “” > for the field.

My field allows NULL , After I change Null Value to " " ,Now i got this same error but no NULL Value

Imported 1 of 1 records from appointments (3).csv (success: 0, failure: 1)
row 1: Invalid field value (field name leader_id):

Strange , What i supposed to do , Thanx

mobhar · October 2, 2021, 12:25am

Try to replace Null by “” (double quotes) in your .csv file, and then please try again.

rembo · October 2, 2021, 12:44am

I have already replaced Null by “” (double quotes) but still get that error above but without NULL value

Imported 1 of 1 records from appointments (3).csv (success: 0, failure: 1)
row 1: Invalid field value (field name leader_id):

mobhar · October 2, 2021, 12:46am

Is leader_id field setup as allow null in your database? If not, then you should define the value instead of blank string in your csv file.

rembo · October 2, 2021, 12:52am

When i try to define the value instead of blank string it’s work fine , But when using blank string i get this error ,
And I am sure that’ leader_id’ field setup as allow NULL …
but still get same error , I get lost actually with this strange error

arbei · October 2, 2021, 1:49am

Make sure your field is defined with “NULL DEFAULT NULL”, e.g.
```ProductName` VARCHAR(40) NULL DEFAULT NULL``

johnberman · November 5, 2021, 10:19am

HiUsing PHPMaker v2022.4.0I am having similar issues, if a field is empty im getting an error messageMy fields are defined correctly fields in question belowdurationc decimal(6, 2) NULL DEFAULT NULL,
doppler_Hz decimal(6, 2) NULL DEFAULT NULL,
smax decimal(6, 2) NULL DEFAULT NULL,
sigMax_dB decimal(6, 2) NULL DEFAULT NULL,and output from CSV - which just adds commas where there is no data

user_ID,date,time,signal,noise,frequency,durationc,durations,image,lat,long,source,timesync,snratio,adjfreq,doppler_Hz,smax,sigMax_dB
9,01/09/2021,00:25:56,-10.3,-47.5,1089,3,fffff,58.2,-6.4,Graves,Dimension4,37.2,45,Thoughts appreciatedKind Regards
John B

mobhar · November 5, 2021, 11:40am

You may post your table schema (CREATE TABLE …) including some records inside (INSERT INTO), so others could reproduce straightforward.

johnberman · November 5, 2021, 2:03pm

HiAs requestedCREATE TABLE staging (
event_id bigint(255) NOT NULL AUTO_INCREMENT,
user_ID int(11) NULL DEFAULT NULL,
date date NULL DEFAULT NULL,
time time NULL DEFAULT NULL,
signal decimal(6, 2) NULL DEFAULT NULL,
noise decimal(6, 2) NULL DEFAULT NULL,
frequency int(11) NULL DEFAULT NULL,
durationc decimal(6, 2) NULL DEFAULT NULL,
durations decimal(6, 2) NULL DEFAULT NULL,
image varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
lat decimal(11, 1) NULL DEFAULT NULL,
long decimal(11, 1) NULL DEFAULT NULL,
source varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
timesync varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
snratio decimal(6, 2) NULL DEFAULT NULL,
adjfreq decimal(11, 0) NULL DEFAULT NULL,
doppler_Hz decimal(6, 2) NULL DEFAULT NULL,
smax decimal(6, 2) NULL DEFAULT NULL,
sigMax_dB decimal(6, 2) NULL DEFAULT NULL,
PRIMARY KEY (event_id) USING BTREE,
INDEX matched(date, time) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 2149792022 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of staging-- ----------------------------INSERT INTO staging VALUES (2149792020, 1, ‘2021-11-05’, ‘00:25:56’, -10.30, -47.50, 1089, NULL, 444.00, ‘’, 58.2, -6.4, ‘Graves’, ‘Dimension4’, 37.20, 4, NULL, NULL, NULL);
INSERT INTO staging VALUES (2149792021, 1, ‘2021-11-10’, ‘00:25:56’, -10.30, -47.50, 1089, NULL, 444.00, ‘’, 58.2, -6.4, ‘Graves’, ‘Dimension4’, 37.20, 4, NULL, NULL, NULL);as a reminder if i import with no no values for say durationc or durations the import failsKind Regards
John B

mobhar · November 6, 2021, 12:45am

To import NULL or empty string values to numeric fields that allow NULL must be handled specifically in order to avoid error while importing.You may use Row_Import and Page_Imported server events to to that.

The first server event is to assign the field by 0 (zero) for those numeric fields that allow NULL. You may check the condition by using PHP is_null for such fields.The second server event is to update the 0 values back to NULL into database, by using ExecuteStatement (assume v2022).Please try it by yourself, and post your code for more discussion.