[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

When importing from CSV, why is MySQL raising a 'Data truncated' warning?

When running a simple SQL command to import some CSV data, I'm receiving warnings on a particular column on seemingly random rows:
Warning (Code 1265): Data truncated for column 'SubGroupNum' at row 2
Warning (Code 1265): Data truncated for column 'SubGroupNum' at row 3
Warning (Code 1265): Data truncated for column 'SubGroupNum' at row 5
Warning (Code 1265): Data truncated for column 'SubGroupNum' at row 6
Warning (Code 1265): Data truncated for column 'SubGroupNum' at row 8

Here is the schema of the DB:
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| IdxId       | double       | NO   |     | NULL    |       |
| YrGrpId     | double       | NO   |     | NULL    |       |
| GroupNum    | double       | NO   |     | NULL    |       |
| LineNum     | int(11)      | NO   |     | NULL    |       |
| LogicalMod  | int(11)      | YES  |     | NULL    |       |
| Application | varchar(255) | YES  |     | NULL    |       |
| SubGroupNum | double       | YES  |     | NULL    |       |
| SubCategory | varchar(2)   | YES  |     | NULL    |       |
| PTypeNum    | char(3)      | YES  |     | NULL    |       |
| IntchNum    | char(5)      | YES  |     | NULL    |       |
| AlphaExt    | char(2)      | YES  |     | NULL    |       |
| LRFlag      | char(1)      | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

Any insight as to what I need to change?
load data local infile 'Idx_App.txt' into table `Idx_App`
fields terminated by ','
enclosed by ''''
lines terminated by '\r\n'
(IdxId, YrGrpId, GroupNum, LineNum, LogicalMod, Application, SubGroupNum, SubCategory, PTypeNum, IntchNum, AlphaExt, LRFlag)
;

Open in new window

Idx-App.txt
0
fluffyx
Asked:
fluffyx
1 Solution
 
bull_riderCommented:
What I observed is if teh subgroupname is not provided that row fails. Can you check the datatype or may be the column in the database is accepting NULL or blank values or not?
0
 
BALMUKUND KESHAVCommented:
problem is where is subgroup has no data, and its showing like ,,,
if you have checked your subgroup fields type accepting null or 0,then if its varchar type then try to change your txt fields format like this
fields enclosed with " " sepereated with , and ensure where is no data it shows ""
then use this command

 load data local infile 'Idx_App.txt' into table `Idx_App`
fields terminated by ","
enclosed by ""
lines terminated by '\r\n'
(IdxId, YrGrpId, GroupNum, LineNum, LogicalMod, Application, SubGroupNum, SubCategory, PTypeNum, IntchNum, AlphaExt, LRFlag)

bm keshav

 
0
 
fluffyxAuthor Commented:
That's causing it! Now comes the challenge of modifying the 80MB TXT file :-).
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now