如何将.xls中的数据加载到mysql表中 [英] How to load data from .xls into mysql table

问题描述

嗨！在那里我需要从.xls文件输入数据到MySql表，但是得到



我的excel文件：



id fname lname

1 test1 test1

2 test2 test2

Hi! there I need to enter data from .xls file to MySql table, but getting

My excel file:

id fname lname
1 test1 test1
2 test2 test2

Error Code: 1366
Incorrect integer value: '' for column 'id' at row 1

我错了，需要帮助。

提前致谢。



我尝试过：



LOAD DATA INFILE'D：/test.xls'

替换表格tetstbl

终止于'\t'的字段
由'\ r'终止的线路

IGNORE 1 LINES;

Where I am wrong,need help.
Thanks in advance.

What I have tried:

LOAD DATA INFILE 'D:/test.xls'
REPLACE INTO TABLE tetstbl
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r'
IGNORE 1 LINES ;

推荐答案

您无法加载文件.xls（或.xlsx ）直接格式化为MySQL。 LOAD DATA INFILE实用程序用于文本文件。



您可以将.xls文件另存为CSV并使用已有的代码或使用负载的实用程序。有几种可用（大多数是免费的） - 有些在此讨论 post [ ^ ]

You can't load a file in .xls (or .xlsx) format directly into MySQL. The LOAD DATA INFILE utility is meant to be used with text files.

You could save your .xls file as a CSV and use the code you already have, or use a utility to do the load. There are several available (most of them are free) - some are discussed on this post[^]

这篇关于如何将.xls中的数据加载到mysql表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！