Search code examples
mysqlload-data-infile

"load data infile" with many files

I have to load and append data to a MySQL table from many files. The files are stored in the same directory.

To load data from a single file I use:

load data local infile 'D:\\MyDir\\file.DAT' into table catasto.dm_import fields terminated by '|' lines terminated by '\n';

Now I need to append every file contained in the directory D:\MyDir into table dm_import. How can I do this? I work with MySQL Workbench and Python.

Solution

  • Going off some tips in this thread:

    Is there replacement for cat on Windows

    cd D:\MyDir;
copy /b *.DAT _all.DAT;
load data local infile '_all.DAT' into table catasto.dm_import fields terminated by '|' lines terminated by '\n';
del _all.DAT;

    That should get you what you're after without any python required.

    What copy /b is doing is essentially taking all the files in the directory, joining them up end to end and then saving the output in _all.DAT

    It's a good idea to delete _all.DAT after you're finished or you could end up in a situation where you keep joining _all.DAT on to the end of _all.DAT in addition to all the .DAT files in the directory...messy!