My Table looks like this
!mysql praxisDB -e 'desc eCommerce'
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| InvoiceNo | varchar(10) | YES | | NULL | |
| StockCode | varchar(10) | YES | | NULL | |
| Description | varchar(50) | YES | | NULL | |
| Quantity | int(10) | YES | | NULL | |
| InvoiceDate | datetime | YES | | NULL | |
| UnitPrice | float(6,2) | YES | | NULL | |
| customerID | varchar(10) | YES | | NULL | |
| country | varchar(15) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
The input data in a CSV file looks like this
!head eCommerce_02PC_2021.csv
InvoiceNo,StockCode,Description,Quantity,InvoiceDate,UnitPrice,CustomerID,Country
C544414,22960,JAM MAKING SET WITH JARS,-2,2/18/2011 14:54,3.75,13408.0,United Kingdom
555276,48111,DOORMAT 3 SMILEY CATS,1,6/1/2011 17:28,15.79,,United Kingdom
575656,22952,60 CAKE CASES VINTAGE CHRISTMAS,48,11/10/2011 14:29,0.55,13319.0,United Kingdom
571636,20674,GREEN POLKADOT BOWL,16,10/18/2011 11:41,1.25,13509.0,United Kingdom
576657,22556,PLASTERS IN TIN CIRCUS PARADE ,12,11/16/2011 11:03,1.65,12720.0,Germany
569823,23298,SPOTTY BUNTING,1,10/6/2011 12:15,4.95,16895.0,United Kingdom
570185,21090,WET/MOULDY,-192,10/7/2011 14:56,0.0,,United Kingdom
574943,17091J,VANILLA INCENSE IN TIN,36,11/8/2011 7:52,0.38,13026.0,United Kingdom
561902,22909,SET OF 20 VINTAGE CHRISTMAS NAPKINS,96,7/31/2011 15:50,0.85,17404.0,Sweden
My LOAD command looks like this
!mysql praxisDB -e " \
LOAD DATA LOCAL INFILE '/content/eCommerce_02PC_2021.csv' INTO TABLE eCommerce \
FIELDS TERMINATED BY ','LINES TERMINATED BY '\n' IGNORE 1 LINES \
( InvoiceNo, StockCode, Description, Quantity,@InvoiceDate, UnitPrice, customerID,country) \
set InvoiceDate = STR_TO_DATE(@InvoiceDate, '%m/%d/%Y %h:%i') \
"
All the rows are getting loaded but the output looks like this
!mysql praxisDB -e 'select * from eCommerce limit 9'
+-----------+-----------+-------------------------------------+----------+---------------------+-----------+------------+----------------+
| InvoiceNo | StockCode | Description | Quantity | InvoiceDate | UnitPrice | customerID | country |
+-----------+-----------+-------------------------------------+----------+---------------------+-----------+------------+----------------+
| C544414 | 22960 | JAM MAKING SET WITH JARS | -2 | NULL | 3.75 | 13408.0 | United Kingdom |
| 555276 | 48111 | DOORMAT 3 SMILEY CATS | 1 | NULL | 15.79 | | United Kingdom |
| 575656 | 22952 | 60 CAKE CASES VINTAGE CHRISTMAS | 48 | NULL | 0.55 | 13319.0 | United Kingdom |
| 571636 | 20674 | GREEN POLKADOT BOWL | 16 | 2011-10-18 11:41:00 | 1.25 | 13509.0 | United Kingdom |
| 576657 | 22556 | PLASTERS IN TIN CIRCUS PARADE | 12 | 2011-11-16 11:03:00 | 1.65 | 12720.0 | Germany |
| 569823 | 23298 | SPOTTY BUNTING | 1 | 2011-10-06 00:15:00 | 4.95 | 16895.0 | United Kingdom |
| 570185 | 21090 | WET/MOULDY | -192 | NULL | 0.00 | | United Kingdom |
| 574943 | 17091J | VANILLA INCENSE IN TIN | 36 | 2011-11-08 07:52:00 | 0.38 | 13026.0 | United Kingdom |
| 561902 | 22909 | SET OF 20 VINTAGE CHRISTMAS NAPKINS | 96 | NULL | 0.85 | 17404.0 | Sweden |
+-----------+-----------+-------------------------------------+----------+---------------------+-----------+------------+----------------+
The input date data looks identically formatted in all the rows but some of them are getting captured and converted correctly but in others ( in fact in a majority) they are being set to NULL. Would be grateful if someone could help me identify where I am making a mistake.
Could the presence of two commas on the second and seventh row of data have a material impact?
I am running this inside Google Colab, hence all sql commands are prefixed by !
