Как импортировать файл .xls в .sql с внешним ключом

Ответ 1

1. Экспортируйте таблицу так, как вы показываете ее в электронной таблице (с прописанными именами).
2. Импортируйте это в MySQL - скажем, в таблицу X
3. Выполните следующий запрос, чтобы выполнить "нормализацию" при создании нужной таблицы (с номерами вместо имен):

Что-то вроде этого:

INSERT INTO users
        (id, name, region_id, zone_id)
    SELECT X.id, X.name, r.id, z.id
        FROM X
        LEFT JOIN region AS r ON r.region_name = X.region
        LEFT JOIN zone   AS z ON z.zone_name   = X.zone;

Если id является AUTO_INCREMENT, то вы можете сделать это немного по-другому. (Оставьте id из INSERT и SELECT.)

Я использовал LEFT на случай, если есть пропущенные регионы или зоны. В этом случае вы получите NULLs или значения по умолчанию для region_id или zone_id, что указывает на необходимость исправления чего-либо.

Ответ 2

Я предлагаю, чтобы в вашем листе .xls заменили имена зон и регионов на фактические идентификаторы в базе данных для этих полей. Затем вы можете экспортировать свой файл xls в файл csv, а затем легко импортировать его в свою базу данных с помощью mysqlimport

mysqlimport --ignore-lines=1 \
        --fields-terminated-by=, \
        --local -u root \
        -p DatabaseName \
         YourExportedFile.csv

Несколько вещей, чтобы рассмотреть здесь:

• Путь к файлу, когда вы выполняете команду.
• Символ fiels-terminate-by
• "- p DatabaseName", DatabaseName не является паролем, является вашим именем базы данных, при выполнении команды будет запрашиваться пароль.

Вы можете импортировать CSV файл с помощью оператора SQL, используя LOAD DATA INFILE

LOAD DATA INFILE "/home/user/YourExportedFile.csv"
INTO TABLE YOUR_TABLE
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Кроме того, если вы используете графический клиент MySQL (mysql workbench, heideSql, mysqlpro и т.д.), вы можете использовать функцию импорта.

Ответ 3

Предполагая, что ни одна из этих таблиц не существует, сначала создайте три CSV файла users.csv, regions.csv, zone.csv.

regions.csv и зоны .csv будут иметь один столбец, который вы можете импортировать в БД, это имя области или имя зоны. Я предполагаю, что таблицы будут настроены с помощью AUTO_INCREMENT, поэтому значения id будут заботиться о себе.

Чтобы создать эти файлы в Excel, получите различные значения, используя Data > Remove Duplicates. Если у вас есть доступ к чему-то вроде файлов импорта phpMyAdmin в базу данных, затем импортируйте эти две таблицы.

Создание пользователей: Чтобы получить внешние ключи в таблице users, я бы создал таблицу users с помощью:

имя, регионы, зона, region_id, zone_id

Вы можете импортировать исходный файл в имя, регионы, поля зон, а затем обновить внешние ключи.

UPDATE users as u SET region_id = (SELECT id FROM regions as r WHERE r.region_name = u.region) 

Затем просто отбросьте регионы области и зоны.

Ответ 4

Во-первых, нам нужно вывести электронную таблицу в формате таблицы в MySQL, используя Export/Import feature

Экспортируйте таблицу так, как вы показываете ее в электронной таблице (с прописанными именами). Импортируйте это в MySQL - в таблицу, скажем, tempUsers.

Выполните следующий запрос, чтобы выполнить нормализацию при создании нужной таблицы (с номерами вместо имен):

Что-то вроде этого:

INSERT INTO users
        (id, name, region_id, zone_id)
    SELECT tempUsers.id, tempUsers.name, region.id, zone.id
        FROM X
        LEFT JOIN region AS region ON region.region_name = tempUsers.region
        LEFT JOIN zone   AS zone ON zone.zone_name   = tempUsers.zone;

Если id - AUTO_INCREMENT, вы можете сделать это немного по-другому. (Оставьте id вне INSERT и SELECT, потому что AUTO_INCREMENT будет увеличивать количество строк на 1 каждый раз, когда вводится новая запись).

Счастливое программирование

Ответ 5

Обычно это то, что я делаю.

Создайте формулу excel для создания вставки script. Идентификатор будет автоматически генерироваться, поэтому просто пропустите его. Предполагая, что ваш идентификатор находится в ячейке A, имя в B-ячейке, области и зоне в C и D соответственно.

="INSERT INTO USER_TABLE(NAME, REGION_ID, ZONE_ID) VALUES ('"&B1&"',(select id
from region where region_name = '"&C1&"'), (select id from zone where 
zone_name = '"&D1&"')";

После создания 1 перетащите формулу до конечных строк, скопируйте script и выполните ее.

Что я считаю:

• Необходимо запланировать во многих средах, локальных, SIT, UAT, Production может быть. Я не получил доступ к БД в производственную среду, поэтому лучше всего вставить script.
• Не задействовать так много вещей и легче изменить в случае следующего раза необходимо добавить новый столбец.

Ответ 6

Для повторного требования внешние ключи могут отображаться в Excel. Экспорт таблицы регионов и таблицы зон в файл Excel. например (ex: имя_файла: lookupmaster.xlsx)

region name   region Id
 Polon          1
 yerni          2

В электронной таблице с фактическими данными добавьте два столбца в качестве

region_id, zone_id

Получить идентификатор региона для всех строк с помощью vlookup

VLOOKUP(B2,[lookumaster.xlsx]Sheet1!$A$1:$B$4,2,FALSE)

Чтобы удалить зависимость от основного, ключевого столбца, скопируйте область Id column и вставьте специальные значения только на свое место.

Повторите то же самое для идентификатора зоны

Вы можете удалить столбец области и зоны из электронной таблицы и импортировать ее в базу данных.