2

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.

1 Answer 1

2

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.

2
  • While it's complicated, wouldn't it be possible make an educated guess on the data type of the column? Statistical software (R, Stata) do this all the time, e.g. if it sees a 1 then it assumes it's numeric unless told otherwise.
    – Heisenberg
    Jul 16, 2014 at 20:42
  • Most programs only have one or two types of integers, and one type of strings. MySQL has 10 integers (not counting all the possibilities for fixed point integers) and 8 character types, with variable size. Yes, as I said You can try heuristic programs, I just do not know a good one. ANALYSE TABLE is really bad at guessing, for example.
    – jynus
    Jul 16, 2014 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.