0

I'm relatively new to MySQL/SQL and this is my first post on this site, so I apologize if I phrase this question poorly.

I saved a copy of my .xlsx file as a .csv file. I am trying to load data from that .csv file into a table. I get the following error message:

0 row(s) affected, 64 warning(s): 1265 Data truncated for column 'ptsg' at row 1

Error log here: http://pastie.org/private/byzcwjtslxpfjyhoog2g

Query here: http://pastie.org/private/tyzkbyqnuwpsafrjxcetq#6,8,12-13

I've tried playing with the 'ptsg' column data type, changing it from float, to decimal without luck.

The data that is being inserted into that column is 4 significant figures at most, and takes the form of something like, "100" or "100.2", with at most 1 number proceeding the decimal.

I have other float columns that work fine with data like "0.347." Why is my data being truncated?

Here are three lines of the data in .csv form:

2013Nuggets,2013,1,Denver,Nuggets,Denver Nuggets,82,19705,3145,6613,0.476,662,1704,0.388,1859,2429,0.765,791,2652,3443,1813,605,352,1157,1719,8811,107.5
2013Knicks,2013,2,New York,Knicks,New York Knicks,82,19780,3140,6867,0.457,765,2081,0.368,1689,2087,0.809,847,2470,3317,1757,625,475,1123,1743,8734,106.5
2013Rockets,2013,3,Houston,Rockets,Houston Rockets,82,19880,3170,6975,0.454,677,1843,0.367,1668,2083,0.801,962,2549,3511,1955,581,371,1110,1641,8685,105.9
2013Suns,2013,4,Phoenix,Suns,Phoenix Suns,82,20005,3219,6844,0.47,701,1857,0.377,1472,1939,0.759,821,2478,3299,1945,545,357,1169,1666,8611,105

Output from query: SHOW WARNINGS;

Note    1265    Data truncated for column 'ptsg' at row 1
Note    1265    Data truncated for column 'ptsg' at row 2
Note    1265    Data truncated for column 'ptsg' at row 3
Note    1265    Data truncated for column 'ptsg' at row 5
...

UPDATE:

Thanks to peterm, I noticed I was using the "decimal" datatype incorrectly for column 'ptsg'. I used DECIMAL(10,0) instead of (10,1). My 64 warnings has been reduced to 1.

0 row(s) affected, 1 warning(s): 1366 Incorrect decimal value: ' ' for column 'ptsg' at row 1246 Records: 1334 Deleted: 0 Skipped: 1334 Warnings: 1

Row 1246 has a blank for ptsg. My query above (specifically, ptsg = NULLIF(@vptsg,'')) should? insert a "NULL" for row 1246 into the ptsg column. I checked the table, and the ptsg column, DECIMAL(10,1), should allow NULL values since I've left the "NN" or "Not Null" checkbox unchecked.

Row 1246 looks like: 1955Bullets,1955,9,Baltimore,Bullets,Baltimore Bullets,,,,,,,,,,,,,,,,,,,,,

Am I making another silly mistake?

user2205916
  • 3,196
  • 11
  • 54
  • 82
  • Read this and paste the output to your question: http://dev.mysql.com/doc/refman/5.7/en/show-warnings.html – Mosty Mostacho Nov 09 '13 at 04:41
  • Please show a couple of lines that are mentioned in warnings (e.g. 1 and 2) – peterm Nov 09 '13 at 05:15
  • What kind of data is this column 'ptsg'? – bksi Nov 09 '13 at 05:16
  • As mentioned we need to know the data type specifics, but it may also be possible that the values are negative and the col is `unsigned` - though that may generate a slightly different data error. Still worth checking – helion3 Nov 09 '13 at 05:20
  • 1
    [Edit](http://stackoverflow.com/posts/19872396/edit) your question and put data in it or use an external resource like you did with http://pastie.org and post a link in your question – peterm Nov 09 '13 at 05:33
  • And what is the exact datatype for `ptsg` column in `summary2` table? – peterm Nov 09 '13 at 05:41

1 Answers1

0

Try adding a trailing comma, after the last column, on each line in the csv file. (Or just a couple lines as a test.)

I think the issue is that the last field in your csv is not terminated by a comma, and this is causing a NULL is being assigned to @vptsg.

If the file is in DOS format, you might also try specifying

  LINES TERMINATED BY '\r\n'
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • I appreciate your effort and this suggestion. However, my .csv file was generated from a .xlsx file. Is there another solution where I wouldn't have to tinker with the intermediate product, the .csv file, but instead remedy the situation through a better/correct SQL query? – user2205916 Nov 09 '13 at 06:20