MySQL Forums
Forum List  »  MySQL Workbench

Re: I want Null not 0
Posted by: alan scott
Date: April 18, 2022 12:56AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
154
April 17, 2022 03:51PM
75
April 17, 2022 05:00PM
Re: I want Null not 0
87
April 18, 2022 12:56AM
79
April 18, 2022 12:12PM
81
April 18, 2022 03:32PM
68
April 18, 2022 04:46PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.