1

I have more than 40 txt files needed to be loaded into a table in Mysql. Each file contains 3 columns of data, each column lists one specific type of data, but in general the format of each txt file is exactly the same, but these file names are various, first I tried LOAD DATA LOCAL INFILE 'path/*.txt' INTO TABLE xxx"

Cause I think maybe use *.txt can let Mysql load all the txt file in this folder. But it turned out no.

So how can I let Mysql or python do this? Or do I need to merge them into one file manually first, then use LOAD DATA LOCAL INFILE command?

Many thanks!

manxing
  • 3,165
  • 12
  • 45
  • 56
  • How do you exactly want to do this? Everything in one string of one column? every text of every file in one different column? Or somehow different? – jonathan.hepp Nov 23 '11 at 13:39
  • 1
    Could you clarify what is the difference of this question with [your other one](http://stackoverflow.com/q/8239554/146792)? It seems to me you are basically asking the same thing... (but I might be wrong of course!) – mac Nov 23 '11 at 13:39
  • 1
    same question, same user, no good – juliomalegria Nov 23 '11 at 13:41
  • 1
    What did you try? What does your textual files contain? What is your database schema (i.e. what are your tables?)? – Basile Starynkevitch Nov 23 '11 at 13:42
  • Sorry, I didn't notice infile command only supports one file as input, so this question I am asking about merging txt files or is there any other command in Mysql can be used – manxing Nov 23 '11 at 14:02

2 Answers2

2

If you want to avoid merging your text files, you can easily "scan" the folder and run the SQL import query for each file:

import os 

for dirpath, dirsInDirpath, filesInDirPath in os.walk("yourFolderContainingTxtFiles"):
    for myfile in filesInDirPath:
        sqlQuery = "LOAD DATA INFILE %s INTO TABLE xxxx (col1,col2,...);" % os.path.join(dirpath, myfile)
        # execute the query here using your mysql connector.
        # I used string formatting to build the query, but you should use the safe placeholders provided by the mysql api instead of %s, to protect against SQL injections
Sébastien
  • 13,831
  • 10
  • 55
  • 70
0

The only and best way is to merge your data into 1 file. That's fairly easy using Python : 

fout=open("out.txt","a")
# first file:
for line in open("file1.txt"):
    fout.write(line)
# now the rest:    
for num in range(2,NB_FILES):
    f = open("file"+str(num)+".txt")
    for line in f:
         fout.write(line)
    f.close() # not really needed
fout.close()

Then run the command you know (... INFILE ...) to load the one file to MySql. Works fine as long as your separation between columns are strictly the same. Tabs are best in my opinion ;)

BuZz
  • 16,318
  • 31
  • 86
  • 141
  • Is this really the best option if you have, say, 25 files, each with 500k+ rows? What are the performance impacts of loading a single huge file into MySQL, I'd say that the InnoDB will definitely bottleneck and choke itself at some point.. – Milan Velebit Sep 25 '18 at 10:06