2

Hey I have a large database where customers request data that is specific to them. They usually send me the requests in a text or csv file. I was wondering if there is a way to get sql to read that file and take the content and put them into a sql query. This way I don't have to open up that file and copy and paste everything into a sql query.

atdy17
  • 115
  • 6
  • 1
    not clear, what you want. what customer sending and what they want? and what you will give them? what are they sending in format for text/csv? – Deepak Sharma Oct 14 '15 at 15:25
  • What the customer is sending me are the identification number of the products that are in my database. I put those numbers into a query and get the result. I just want to know is there a way for sql to do this so I don't have to copy and paste. – atdy17 Oct 14 '15 at 15:29

3 Answers3

0

No. MySQL is not designed to do this.

You need an intermediate script that can interpret the files and generate the queries you require.

Steve E.
  • 9,003
  • 6
  • 39
  • 57
0

Steve already answered it.

Let me add few words only.

you can not use the csv, text,excel or anyother format directly in query for DML/DDL.. you can use file directly only for export/import.

Deepak Sharma
  • 4,124
  • 1
  • 14
  • 31
0

Yes there is a way to do it: you can import the csv file to your database and then join it with any query you want.

You can load the csv file with an SQL query such as:

LOAD DATA INFILE "/tmp/test.csv"
INTO TABLE test
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

You can use other ways to import the data, see: How to import CSV file to MySQL table.

I tried this SQL solution in Ubuntu 14.04 with MySQL 5.6. For this to work you will have to put the test.csv file in the /tmp directory and do a chmod 755 test.csv for it to work. Otherwise MySQL is gives "Permission denied" errors. More about this issue on: LOAD DATA INFILE Error Code : 13

Community
  • 1
  • 1
agold
  • 6,140
  • 9
  • 38
  • 54