I have a very Simple excel sheet:
我有一个非常简单的excel表格:
I am wanting to put this data into a table in SQL Server. I also wanted to add a field that contains a date.
我想把这些数据放到SQL Server中的一个表中。我还想添加一个包含日期的字段。
what is the best way to do this?
最好的方法是什么?
6 个解决方案
#1
10
Create a table in SQL server with the same number of fields that you have in the spreadsheet.
在SQL server中创建一个具有与电子表格中相同数量字段的表。
In SQL Server Management Studio Object Explorer you can right click the table you created and select "Edit top 200 rows". Highlight the data you want to copy in Excel and right click -> copy. Right click the space to the left of the first row in the SSMS edit tabe widow and paste.
在SQL Server Management Studio Object Explorer中，您可以右键单击创建的表并选择“编辑前200行”。突出显示要在Excel中复制的数据，然后右击->复制。右键单击SSMS编辑列表中的第一行左边的空格并粘贴。
After the import check the SQL table to make sure it has the same amount of rows as the spreadsheet.
在导入之后，检查SQL表，确保它的行数与电子表格相同。
You can add the date column to the SQL table after you do the data import. Or add it in the spreadsheet before you do the import.
在进行数据导入之后，可以将date列添加到SQL表中。或者在导入之前将其添加到电子表格中。
#2
6
You can first create the table in SQL Server with the added field and then use the import wizard in the Mangement Studio for importing the excel file. Or you create the table during the import task, and you add the new field later.
您可以先在SQL Server中使用添加的字段创建表，然后在Mangement Studio中使用导入向导来导入excel文件。或者在导入任务期间创建表，稍后添加新字段。
#3
2
Option 1:
选项1:
Read the data in an IDataReader, and then call a stored procedure to insert the data.
在IDataReader中读取数据，然后调用存储过程来插入数据。
http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/
I use the above when I have ~~alot~~ of rows to import and I want to validate them outside of the database.
当我有~~ ~~行要导入时，我使用上面的方法，并且我想在数据库之外验证它们。
Option 2:
选项2:
http://support.microsoft.com/kb/321686
http://support.microsoft.com/kb/321686
or search for:
或搜索:
Select FROM OPENDATASOURCE Excel
Option N:
N选项:
There are other options out there.
还有其他的选择。
It depends what you like, how much time you want to put into it, is it a "one off" or you gotta do it for 333 files every day.
这取决于你喜欢什么，你想投入多少时间，它是“一个关闭”还是你必须每天为333个文件做。
#4
1
My solution was to convert .xlsx to .csv and then use this site to convert .csv to .sql. I then ran the sql file in sql server and generated my tables.
我的解决方案是将.xlsx转换为.csv，然后使用这个站点将.csv转换为.sql。然后在sql server中运行sql文件并生成表。
#5
0
It can be also be done by creating a batch file.
也可以通过创建批处理文件来完成。
For this you already need to have a table created on server with the same data structure as you have in excel.
为此，您已经需要在服务器上创建一个与excel中的数据结构相同的表。
Now using BCP you can import that data from the excel file and insert it into sql table.
现在可以使用BCP从excel文件导入数据，并将其插入到sql表中。
BCP utility function
BCP实用工具函数
sqlcmd -S IP -d databasename -U username -P passwd -Q "SQL query mostly it should be truncate query to truncate the created table"
bcp databasename.dbo.tablename in "Excel file path from where you need to input the data" -c -t, -S Server_IP -U Username -P passwd -b provide batch size
You can refer to the link below for more options on the bcp utility:
有关bcp实用程序的更多选项，请参阅下面的链接:
https://msdn.microsoft.com/en-IN/library/ms162802.aspx
https://msdn.microsoft.com/en-IN/library/ms162802.aspx
#6
-2
Open your SQL server interface software, add the date field to the table.
打开SQL server接口软件，将日期字段添加到表中。
Go to excel, add the date column, copy the excel data.
转到excel，添加日期列，复制excel数据。
Go to your SQL server interface software and use the functionality to import the data from the clipboard. (SQL server interface software that has this is for example Database4.net, but if you have another package with the functionality then use that.)
转到SQL server接口软件，并使用该功能从剪贴板导入数据。(拥有此功能的SQL server接口软件是Database4.net，但是如果您有具有此功能的另一个包，那么就使用它。)
Alternatively use VBA with DOA or ADO to interact with the SQL server database and use SQL statements to add the field and copy the data into your table
或者使用VBA与DOA或ADO与SQL server数据库交互，并使用SQL语句添加字段并将数据复制到表中
#1
10
Create a table in SQL server with the same number of fields that you have in the spreadsheet.
在SQL server中创建一个具有与电子表格中相同数量字段的表。
In SQL Server Management Studio Object Explorer you can right click the table you created and select "Edit top 200 rows". Highlight the data you want to copy in Excel and right click -> copy. Right click the space to the left of the first row in the SSMS edit tabe widow and paste.
在SQL Server Management Studio Object Explorer中，您可以右键单击创建的表并选择“编辑前200行”。突出显示要在Excel中复制的数据，然后右击->复制。右键单击SSMS编辑列表中的第一行左边的空格并粘贴。
After the import check the SQL table to make sure it has the same amount of rows as the spreadsheet.
在导入之后，检查SQL表，确保它的行数与电子表格相同。
You can add the date column to the SQL table after you do the data import. Or add it in the spreadsheet before you do the import.
在进行数据导入之后，可以将date列添加到SQL表中。或者在导入之前将其添加到电子表格中。
#2
6
You can first create the table in SQL Server with the added field and then use the import wizard in the Mangement Studio for importing the excel file. Or you create the table during the import task, and you add the new field later.
您可以先在SQL Server中使用添加的字段创建表，然后在Mangement Studio中使用导入向导来导入excel文件。或者在导入任务期间创建表，稍后添加新字段。
#3
2
Option 1:
选项1:
Read the data in an IDataReader, and then call a stored procedure to insert the data.
在IDataReader中读取数据，然后调用存储过程来插入数据。
http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/
I use the above when I have ~~alot~~ of rows to import and I want to validate them outside of the database.
当我有~~ ~~行要导入时，我使用上面的方法，并且我想在数据库之外验证它们。
Option 2:
选项2:
http://support.microsoft.com/kb/321686
http://support.microsoft.com/kb/321686
or search for:
或搜索:
Select FROM OPENDATASOURCE Excel
Option N:
N选项:
There are other options out there.
还有其他的选择。
It depends what you like, how much time you want to put into it, is it a "one off" or you gotta do it for 333 files every day.
这取决于你喜欢什么，你想投入多少时间，它是“一个关闭”还是你必须每天为333个文件做。
#4
1
My solution was to convert .xlsx to .csv and then use this site to convert .csv to .sql. I then ran the sql file in sql server and generated my tables.
我的解决方案是将.xlsx转换为.csv，然后使用这个站点将.csv转换为.sql。然后在sql server中运行sql文件并生成表。
#5
0
It can be also be done by creating a batch file.
也可以通过创建批处理文件来完成。
For this you already need to have a table created on server with the same data structure as you have in excel.
为此，您已经需要在服务器上创建一个与excel中的数据结构相同的表。
Now using BCP you can import that data from the excel file and insert it into sql table.
现在可以使用BCP从excel文件导入数据，并将其插入到sql表中。
BCP utility function
BCP实用工具函数
sqlcmd -S IP -d databasename -U username -P passwd -Q "SQL query mostly it should be truncate query to truncate the created table"
bcp databasename.dbo.tablename in "Excel file path from where you need to input the data" -c -t, -S Server_IP -U Username -P passwd -b provide batch size
You can refer to the link below for more options on the bcp utility:
有关bcp实用程序的更多选项，请参阅下面的链接:
https://msdn.microsoft.com/en-IN/library/ms162802.aspx
https://msdn.microsoft.com/en-IN/library/ms162802.aspx
#6
-2
Open your SQL server interface software, add the date field to the table.
打开SQL server接口软件，将日期字段添加到表中。
Go to excel, add the date column, copy the excel data.
转到excel，添加日期列，复制excel数据。
Go to your SQL server interface software and use the functionality to import the data from the clipboard. (SQL server interface software that has this is for example Database4.net, but if you have another package with the functionality then use that.)
转到SQL server接口软件，并使用该功能从剪贴板导入数据。(拥有此功能的SQL server接口软件是Database4.net，但是如果您有具有此功能的另一个包，那么就使用它。)
Alternatively use VBA with DOA or ADO to interact with the SQL server database and use SQL statements to add the field and copy the data into your table
或者使用VBA与DOA或ADO与SQL server数据库交互，并使用SQL语句添加字段并将数据复制到表中