Re: load data infile somewhat flaky

I find that using tools like CSVQ helps with checking CSV data
integrity.

Turning off indexes and such during the load and generating them
afterwards speeds loading of data up significantly.

However,

	 https://www.google.com/search?q=sqlite+to+mariadb

has 1.5 Million results :-)-O

I have used

	 https://pypi.org/project/sqlite3-to-mysql/

in the past successfully


el

On 18/08/2021 15:43, DFS wrote:
> 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.

-- 
To email me replace 'nospam' with 'el'