I know about LOAD DATA INFILE command — however, this requires that we already have the table with appropriate columns in place. When I have csv with hundreds of columns, it is not feasible to type all the columns definitions in by hand.
There's also a website that allows uploading the .csv file and it will produce the appropriate MySQL CREATE TABLE and INSERT codes. However, 1) this is not easily reproducible, and 2) there are quirks such as BIT-type data has to be modified so that it's enter as b'1' with the b.
Is there any way to import csv into SQL programmatically, for example using R, Python, Java, etc.? I work in social sciences, and very frequently we need to download some csv files that need to be imported.
Best Answer
What you want is nearly impossible, no one can know if you want to import '1' as a characters string, n decimal integer, a base-2 integer, an hex integer, a float, a blob, a "boolean", ...
You can try heuristic programs, or import them all as strings and run an analysis program like
SELECT... ANALYZE TABLE();. It would be easier if the csv had the datatypes as an information.Regarding programs that create the table (with chars) based on the list of columns, that is easier to find (GUI) and program.
Here it is one in bash as an example.