3

In mysql we can import data files -

load data infile 'file.txt'
into table `text`
fields terminated ' '
lines terminated '\n'

My question is can we import a file with unknown columns all into one column? Or possibly can we create columns that we need on the fly?

peterm
  • 91,357
  • 15
  • 148
  • 157
Derek Carr
  • 73
  • 1
  • 7

1 Answers1

1

Can we import a file with unknown columns all into one column?

Yes you can do it a few steps:

First Create a temporary staging table (lets call it import) with one column that will hold lines as rows of the source data file

CREATE TABLE `import` (
  `line` varchar(512) DEFAULT NULL
);

and load data from the file into the table

LOAD DATA INFILE '/path/to/your/file.txt'
INTO TABLE import
FIELDS TERMINATED BY '\n'
LINES  TERMINATED BY '\n'

Second Assuming that you have a target table (lets call it words) that looks something like this

CREATE TABLE `words` (
  `word` varchar(64) DEFAULT NULL
);

we can split each line into words and insert them into words table with a query 

INSERT INTO words (word)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(line, ' ', n.n), ' ', -1) word
  FROM import CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(line) - LENGTH(REPLACE(line, ' ', '')))
 ORDER BY n.n

NOTE:

  • assumption is that your words separated exactly with one space
  • this query will split up to 100 words. If you need more or less you can adjust a limit by editing the inner subquery or if do import regularly than consider to use persisted tally(numbers) table instead.

Third Drop or truncate (if you will use it again to import more files) the staging table

DROP TABLE import;

Here is SQLFiddle demo that shows step two.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • This won't work because I want the delimiter to be a single space, the problem arrises when we don't know how many words belong to each line. This approach will read the whole line in and not word for word into one column. – Derek Carr Sep 01 '13 at 06:25
  • @DerekCarr You should've mentioned that *...word for word into one column...*. Without it your actual intent wasn't clear. – peterm Sep 01 '13 at 06:43