Re: I want Null not 0

[alan scott]

The file is entered from Notepad although it originates in Excel.
There are 132 columns and 110 rows. I've just copied the first two rows here to try and minimise the amount of space I take up in this message.
When I copy and past the first two rows into the message the spaces are visible. When I press preview they disappear.
1 102 108 95 123 187 120 98 90 100 110 100
2 200 140 125 140 120 120 140 120 170 160 130 155 130 125 125 130 200 165 145 150 260 190 125 127 148 130 130 120 155 130 130 160 160 130



mysql> show create table swa_averages;
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| swa_averages | CREATE TABLE `swa_averages` (
`bottle_id` int NOT NULL AUTO_INCREMENT,
`month_1` int DEFAULT NULL,
`month_2` int DEFAULT NULL,
`month_3` int DEFAULT NULL,
`month_4` int DEFAULT NULL,
`month_5` int DEFAULT NULL,
`month_6` int DEFAULT NULL,
`month_7` int DEFAULT NULL,
`month_8` int DEFAULT NULL,
`month_9` int DEFAULT NULL,
`month_10` int DEFAULT NULL,
`month_11` int DEFAULT NULL,
`month_12` int DEFAULT NULL,
`month_13` int DEFAULT NULL,
`month_14` int DEFAULT NULL,
`month_15` int DEFAULT NULL,
`month_16` int DEFAULT NULL,
`month_17` int DEFAULT NULL,
`month_18` int DEFAULT NULL,
`month_19` int DEFAULT NULL,
`month_20` int DEFAULT NULL,
`month_21` int DEFAULT NULL,
`month_22` int DEFAULT NULL,
`month_23` int DEFAULT NULL,
`month_24` int DEFAULT NULL,
`month_25` int DEFAULT NULL,
`month_26` int DEFAULT NULL,
`month_27` int DEFAULT NULL,
`month_28` int DEFAULT NULL,
`month_29` int DEFAULT NULL,
`month_30` int DEFAULT NULL,
`month_31` int DEFAULT NULL,
`month_32` int DEFAULT NULL,
`month_33` int DEFAULT NULL,
`month_34` int DEFAULT NULL,
`month_35` int DEFAULT NULL,
`month_36` int DEFAULT NULL,
`month_37` int DEFAULT NULL,
`month_38` int DEFAULT NULL,
`month_39` int DEFAULT NULL,
`month_40` int DEFAULT NULL,
`month_41` int DEFAULT NULL,
`month_42` int DEFAULT NULL,
`month_43` int DEFAULT NULL,
`month_44` int DEFAULT NULL,
`month_45` int DEFAULT NULL,
`month_46` int DEFAULT NULL,
`month_47` int DEFAULT NULL,
`month_48` int DEFAULT NULL,
`month_49` int DEFAULT NULL,
`month_50` int DEFAULT NULL,
`month_51` int DEFAULT NULL,
`month_52` int DEFAULT NULL,
`month_53` int DEFAULT NULL,
`month_54` int DEFAULT NULL,
`month_55` int DEFAULT NULL,
`month_56` int DEFAULT NULL,
`month_57` int DEFAULT NULL,
`month_58` int DEFAULT NULL,
`month_59` int DEFAULT NULL,
`month_60` int DEFAULT NULL,
`month_61` int DEFAULT NULL,
`month_62` int DEFAULT NULL,
`month_63` int DEFAULT NULL,
`month_64` int DEFAULT NULL,
`month_65` int DEFAULT NULL,
`month_66` int DEFAULT NULL,
`month_67` int DEFAULT NULL,
`month_68` int DEFAULT NULL,
`month_69` int DEFAULT NULL,
`month_70` int DEFAULT NULL,
`month_71` int DEFAULT NULL,
`month_72` int DEFAULT NULL,
`month_73` int DEFAULT NULL,
`month_74` int DEFAULT NULL,
`month_75` int DEFAULT NULL,
`month_76` int DEFAULT NULL,
`month_77` int DEFAULT NULL,
`month_78` int DEFAULT NULL,
`month_79` int DEFAULT NULL,
`month_80` int DEFAULT NULL,
`month_81` int DEFAULT NULL,
`month_82` int DEFAULT NULL,
`month_83` int DEFAULT NULL,
`month_84` int DEFAULT NULL,
`month_85` int DEFAULT NULL,
`month_86` int DEFAULT NULL,
`month_87` int DEFAULT NULL,
`month_88` int DEFAULT NULL,
`month_89` int DEFAULT NULL,
`month_90` int DEFAULT NULL,
`month_91` int DEFAULT NULL,
`month_92` int DEFAULT NULL,
`month_93` int DEFAULT NULL,
`month_94` int DEFAULT NULL,
`month_95` int DEFAULT NULL,
`month_96` int DEFAULT NULL,
`month_97` int DEFAULT NULL,
`month_98` int DEFAULT NULL,
`month_99` int DEFAULT NULL,
`month_100` int DEFAULT NULL,
`month_101` int DEFAULT NULL,
`month_102` int DEFAULT NULL,
`month_103` int DEFAULT NULL,
`month_104` int DEFAULT NULL,
`month_105` int DEFAULT NULL,
`month_106` int DEFAULT NULL,
`month_107` int DEFAULT NULL,
`month_108` int DEFAULT NULL,
`month_109` int DEFAULT NULL,
`month_110` int DEFAULT NULL,
`month_111` int DEFAULT NULL,
`month_112` int DEFAULT NULL,
`month_113` int DEFAULT NULL,
`month_114` int DEFAULT NULL,
`month_115` int DEFAULT NULL,
`month_116` int DEFAULT NULL,
`month_117` int DEFAULT NULL,
`month_118` int DEFAULT NULL,
`month_119` int DEFAULT NULL,
`month_120` int DEFAULT NULL,
`month_121` int DEFAULT NULL,
`month_122` int DEFAULT NULL,
`month_123` int DEFAULT NULL,
`month_124` int DEFAULT NULL,
`month_125` int DEFAULT NULL,
`month_126` int DEFAULT NULL,
`month_127` int DEFAULT NULL,
`month_128` int DEFAULT NULL,
`month_129` int DEFAULT NULL,
`month_130` int DEFAULT NULL,
`month_131` int DEFAULT NULL,
PRIMARY KEY (`bottle_id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

LOAD DATA LOCAL INFILE '/Users/alana/OneDrive/Documents/SQL/swaaverages.txt' INTO TABLE swa_averages
LINES TERMINATED BY '\r\n';

Note that this command is copied from workbench where it works perfectly (apart from the 0/null issue).
When I try it in the command line I get
mysql> use whisky;
Database changed
mysql> LOAD DATA LOCAL INFILE '/Users/alana/OneDrive/Documents/SQL/swaaverages.txt' INTO TABLE swa_averages LINES TERMINATED BY '\r\n';
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
mysql>

I think this is due to a problem you helped me with previously and not germane to the issue.