pavilion pavilion - 6 months ago 11
SQL Question

MYSQL csv filename to table

Is it possible to search a folder and grab a file name to convert into a mysql table? Can it be done in a query or stored procedures?

I'm new to mysql and so far I can read my data and create a table which is manually done, but don't know how to create a table with its file name automatically.

LOAD DATA
LOCAL INFILE 'D:/test.csv'
INTO TABLE test

Answer

Unfortunately it's not possible for LOAD DATA to create the table for you. The syntax for the function goes as ...

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    **INTO TABLE tbl_name**

The table name has to be specified, which means you have to create the table first.

Why is it so? There are so many different data types that are supported in a mysql table that the importer is unable to judge for itself which data type to use. Fro example if the first line in the CSV was

hello, 1

The importer might choose CHAR(5) and INT but the next line might be

Colombo, Galle

This line cannot be imported into CHAR(5) or INT

Comments