csv import slow

[Sergey Pashkov]

Hi, Jonathan,
For SQLite the default encoding of CSV import is UTF8, so there should be
no difference.

As for the LOAD DATA INFILE command - do you need it to load CSV file into
MySQL db?

--
Best regards
Sergey Pashkov.



On Mon, Oct 20, 2014 at 12:32 AM, Jonathan Evans <consultjonevans at gmail.com>
wrote:

> Thanks all for your help.
> Have just downloaded version 5.6 and indeed with "Use Transaction" checked
> the CSV import to an SQLite database is also very fast. Fastest maybe when
> character encoding is specified - e.g. UTF8?
> Question: Does Valentina support an SQL equivalent of the CSV import
> wizard - e.g. LOAD DATA INFILE - and if so, what is the syntax?
> Thanks
> Jon
>
> On Mon, Oct 13, 2014 at 7:43 AM, Sergey Pashkov <
> sergey_pashkov at valentina-db.com> wrote:
>
>> Hi Jonathan,
>> As it was said before, we have improved CSV import for SQLite database.
>>
>> You can try it in the latest beta version:
>> http://www.valentina-db.com/download/beta/5.6b26/win_32/vstudio_5_win.exe
>>
>> There was "Use Transaction" option added to speed up the process, so it
>> should be checked:
>>
>> ​
>> As for the error during import into Valentina database, it is not
>> important warning, so will not be shown in the future versions.
>>
>> --
>> Best regards,
>> Sergey Pashkov.
>>
>> On Mon, Oct 13, 2014 at 2:59 PM, Jonathan Evans <
>> consultjonevans at gmail.com> wrote:
>>
>>> Hi again, just spotted one other difference that turned out to be the
>>> solution / cause: I was importing into an SQLite database, not into a
>>> Valentina Database.
>>> I repeated the same import into a new Valentina Database and the import
>>> took a few seconds as predicted. A message box appeared: "IncreaseMapSize"
>>> from 2 to 3, which hopefully means something to you.
>>> Jon
>>>
>>> On Mon, Oct 13, 2014 at 5:36 AM, Jonathan Evans <
>>> consultjonevans at gmail.com> wrote:
>>>
>>>> Hi Sergey
>>>> 1) 25 columns
>>>> 2) I select option to create new table automatically
>>>> Thanks for the video. It's good to see that it can work.
>>>> The only obvious differences between the video and my experience are
>>>> that:
>>>> A) you are using a Mac vs. my 4-year-old laptop PC;
>>>> B) the data imported.
>>>> Perhaps you could send me your mock data to try?
>>>> Thanks,
>>>> Jon
>>>>
>>>> On Fri, Oct 10, 2014 at 10:08 AM, Sergey Pashkov <
>>>> sergey_pashkov at valentina-db.com> wrote:
>>>>
>>>>> Hi Jon,
>>>>>
>>>>> 1) How many columns are there in this file?
>>>>> 2) Do you create a new table yourself with all needed fields,
>>>>> or you select an option to create it automatically?
>>>>>
>>>>> My test file (9.5 Mb, 11 fields, 30,000 records) was imported into
>>>>> prepared table during a few seconds,
>>>>> here's a video of the process:
>>>>> https://www.dropbox.com/s/1vel4mroshjuzam/csv_import.mp4?dl=0
>>>>>
>>>>> --
>>>>> Best regards,
>>>>> Sergey Pashkov
>>>>>
>>>>> On Fri, Oct 10, 2014 at 5:49 PM, Ruslan Zasukhin <
>>>>> ruslan_zasukhin at valentina-db.com> wrote:
>>>>>
>>>>>> On 10/10/14, 3:40 PM, "Jonathan Evans" <consultjonevans at gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>> Hi Jonathan,
>>>>>>
>>>>>> It seems its Vstudio itslf problem.
>>>>>> Valentina DB via API command can do this of course fast.
>>>>>>
>>>>>>
>>>>>> Just month ago was fix for SQLite in this area.
>>>>>>
>>>>>> Sergey, may be was inserted flush() into loop?
>>>>>> Please check let me know in chat ...
>>>>>>
>>>>>>
>>>>>> > Yes that's right.
>>>>>> >
>>>>>> > On Fri, Oct 10, 2014 at 6:37 AM, Sergey Pashkov
>>>>>> > <sergey_pashkov at valentina-db.com> wrote:
>>>>>> > Hi Jon,
>>>>>> > So you have tried to import CSV file into a new table in a local
>>>>>> Valentina
>>>>>> > Database and it was very slow?
>>>>>> >
>>>>>> > --
>>>>>> > Best regards,
>>>>>> > Sergey Pashkov.
>>>>>> >
>>>>>> > On Fri, Oct 10, 2014 at 3:19 PM, Jonathan Evans <
>>>>>> consultjonevans at gmail.com>
>>>>>> > wrote:
>>>>>> > Hi there
>>>>>> > Have just used Valentina Studio's import from csv wizard to import
>>>>>> a 9MB csv
>>>>>> > file with 30,000 rows into a new table. It took about one hour.
>>>>>> > This seems slow to me. Opening the same csv in Excel takes seconds.
>>>>>> Importing
>>>>>> > into MySQL is quick also.
>>>>>> > 1) What are your experiences of importing medium-large csv files
>>>>>> into
>>>>>> > Valentina?
>>>>>> > 2) Any tips for speeding up the process?
>>>>>> > 3) My aspiration is to import much larger csv files with 1 million
>>>>>> + rows. How
>>>>>> > realistic is that?
>>>>>> > Thanks for your help.
>>>>>>
>>>>>> --
>>>>>> Best regards,
>>>>>>
>>>>>> Ruslan Zasukhin
>>>>>> VP Engineering and New Technology
>>>>>> Paradigma Software, Inc
>>>>>>
>>>>>> Valentina - Joining Worlds of Information
>>>>>> http://www.paradigmasoft.com
>>>>>>
>>>>>> [I feel the need: the need for speed]
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Valentina-studio mailing list
>>>>>> Valentina-studio at lists.macserve.net
>>>>>> http://lists.macserve.net/mailman/listinfo/valentina-studio
>>>>>>
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Valentina-studio mailing list
>>>>> Valentina-studio at lists.macserve.net
>>>>> http://lists.macserve.net/mailman/listinfo/valentina-studio
>>>>>
>>>>>
>>>>
>>>
>>> _______________________________________________
>>> Valentina-studio mailing list
>>> Valentina-studio at lists.macserve.net
>>> http://lists.macserve.net/mailman/listinfo/valentina-studio
>>>
>>>
>>
>> _______________________________________________
>> Valentina-studio mailing list
>> Valentina-studio at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina-studio
>>
>>
>
> _______________________________________________
> Valentina-studio mailing list
> Valentina-studio at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina-studio
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina-studio/attachments/20141020/7f5f23e9/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: use_transaction.png
Type: image/png
Size: 2198 bytes
Desc: not available
URL: <http://lists.macserve.net/pipermail/valentina-studio/attachments/20141020/7f5f23e9/attachment-0001.png>