MariaDB LOAD DATA INFILE csv file not reading correctly

Question

File : /DNC/myphones.csv CONTENTS:

Phone
5555555555
5555555556

SQL - LOAD DATA LOCAL INFILE '/DNC/myphones.csv' INTO TABLE test
LINES TERMINATED BY '\r\n'

What Comes back back is several warnings and a few skips and one Record makes it into the table a record of 2174563487 - Most of these digits don't exist in the data. Where is this coming from?

csv was created in MSOUTLOOK 2010, Also tried the above with a "test" file written in nano of the same contents.

Any help would be GREATLY appreciated.

UPDATE - Changing the Schema to a VARCHAR(255) Makes this work properly. Could it be reading the CSV file contents as a string instead of as integer? That would explain it not reading the values from the CSV but not where its getting the weird 217 #

Final Update. Since ill never perform mathematical operations on the phone number ive just decided to use a string instead. But thanks everyo e for your help

Did you sanitize your data into integer? A space, dash,etc will cause error and give you surprises. Otherwise, just store the field into VARCHAR and give it a unique ID. — mootmoot
– mootmoot, Commented Jun 3, 2016 at 16:41
Yes. The second test file i made with nano was just the phone numbers with no header. Id enter the # press enter, enter the other # then save — Sparky
– Sparky, Commented Jun 3, 2016 at 16:48

mootmoot · Accepted Answer · 2016-06-03 16:23:59Z

2

First, you schema only take integer, the header will cause server to spit error. If header not part of the data, why don't you skip it?

LOAD DATA LOCAL INFILE '/DNC/myphones.csv' INTO TABLE test 
  LINES TERMINATED BY '\r\n' 
  IGNORE 1 LINES;

Seconds, incorrect line terminated. They are different for Windows"\r\n" and Linux "\r". However, "/" vs "\" is only apply for folder , not newline/tab/return character.

edited Jun 3, 2016 at 16:23

answered Jun 3, 2016 at 16:14

mootmoot

13.2k5 gold badges49 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sparky Over a year ago

I will in the final draft. I'm going for a minimal coding approach in my testing so not typing that ignore 1 lines at the end for the 150+ times i've tried this saves a little bit of typing

Sparky Over a year ago

Oops. The machine this is running on has no gui. I typed this in wrong when asking the question. will correct now.,

mootmoot Over a year ago

really, how much typing is omitted from the question? don't tell me you ommit CSV field enclose and delimiter. ;-D

Sparky Over a year ago

None from the question.... I didn't define the field enclose or the delimiter because it isn't technically delimited, there is only one field.

Collectives™ on Stack Overflow

MariaDB LOAD DATA INFILE csv file not reading correctly

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related