Question 301002 in Mysql Question posted in
The official MySQL documentation can be found here.

Does MySQL have a way to read directly from a csv file instead of importing it?

Postgresql has a concept of foreign tables and can use a csv file as the table source. Is there a way to do this in MySQL? In my case I do not want to import the file as it changes every minute (gets rewritten via crontab).

CREATE FOREIGN TABLE IF NOT EXISTS rawdata.system_ps (
  pid text,
  started text,
  cpu_pcnt text,
  mem_pcnt text,
  command text
) 
SERVER "import" options (
  FILENAME '/var/tmp/system_ps.csv',
  FORMAT 'csv',
  HEADER 'true'
);
Tags:

2

Answers


  1. 0

    yes it does, you have to install MYSQL Workbench and connect to the database. When you open Workbench, click on the database. Manage connection and set up access to the MySQL database

    Login or Signup to reply.
  2. 0

    MySQL supports a CSV storage engine. It’s described in the manual here: https://dev.mysql.com/doc/refman/8.0/en/csv-storage-engine.html

    The syntax is different than the example you show. You can’t specify the filename or path. It must be in the data directory, and must be in a file matching the table name. Your external cron process must be able to access that file.

    But it satisfies your requirement of reading data from the CSV file on every query, so if an external process updates the file, the next SQL query reads the updated data. The CSV file is not imported into another storage engine in MySQL.

    Read the manual page I linked to for more details.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search