I've been asked to create an excel file saved as a .csv file to import into a mysql db. How do you format the excel file if the data goes to multiple tables in the db?
-
I don't think that's possible, at least with the CSV file format. Excel .xls(x) files allows multiple spreadsheets so you can use that.tyteen4a03– tyteen4a032013-04-26 18:00:19 +00:00Commented Apr 26, 2013 at 18:00
-
You don't have to format the Excel file into multiple sheets. You can selectively extract parts of the CSV file and put them into different tables, and link these tables together with a foreign key. This article explores how you can do that: blog.terresquall.com/2022/05/…John Doe– John Doe2022-05-10 01:48:29 +00:00Commented May 10, 2022 at 1:48
Add a comment
|
2 Answers
You need to create separate csv file for each table & then you can use command something like below:
LOAD DATA LOCAL INFILE './csv_data/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(first_name,last_name,email_id );
This sample code is under presumption that csv file is comma separated , escape charater is double quote & first line contain header.
Comments
You need a CSV file for each table, or you need to import all of your data into an intermediate table and then split that out somehow using either a series of queries, a stored procedure, or a TRIGGER on the import table.
This is because the LOAD DATA INFILE system is limited to one table at a time.