Re: load data infile somewhat flaky

On 8/18/2021 4:10 AM, Johann Klammer wrote:
> On 08/17/2021 07:11 PM, DFS wrote:
>> Finally got a big file (3 columns x 612K rows) of messy data loaded.
>>
>> load data infile 'file.csv'
>> into table
>> fields terminated by ','
>> enclosed by '"';
>>
>> I noticed it tells you an error occurred at a certain line, but the offending data (a text field ending in \ or "\") 5 lines later was the real issue.
>>
>> On one run 5 lines didn't post, and it turns out they were the next 5 lines after a field ending in "\".  Those 5 lines got concatenated with the offending line, so I had one large clump of data in one row.
>>
>> Moral: watch for fields ending in \ or "\".
>>
> I seem to recall it also has problems with whitespace around the commata.


Yes, I saw that too.

It was a fiasco.  Wasted a fair amt of my time.  A couple times it would 
spend 10 minutes reading a file (Stage 1 of 1...) then tell me it bombed 
on row 1 (which was valid data).
Near the end of my db cloning process (15 tables, 30M rows total, SQLite 
to MariaDB) I wrote a little python program to copy the data.

The python code did - no joking - nearly 2700+ inserts/second into 
MariaDB.  I copied 1.5M rows (~4GB) in under 10 minutes.

'load data infile' isn't worthless - it worked decently fast on simple 
data - but there are better ways of getting data into MariaDB.