精华内容
下载资源
问答
  • excel导入sql数据库

    2011-05-15 18:04:31
    这里面给出了在web开发中从数据库导出excel的资料,希望能给你帮助
  • Excel导入SQL数据库

    2011-03-16 13:09:16
    服务器端的环境配置参考资料上看,微软系列的配置应该都行,即: 1.Win9x+PWS+Office 2.Win2000 Professional+PWS+Office 3.Win2000 Server+IIS+Office 目前笔者测试成功的环境是后二者。Office的版本没有特殊...
  • Scheduling Data Imports in SQL ServerImporting data into a SQL Server database isn't really that tricky: there's a straightforward wizard that you can follow to get information from Microsoft Excel, A...

    Scheduling Data Imports in SQL Server

    Importing data into a SQL Server database isn't really that tricky: there's a straightforward wizard that you can follow to get information from Microsoft Excel, Access and even text files.  But what if you want to perform the same import of data on a regular basis?  Having to go through the wizard on a weekly, daily, or even hourly basis could become very tedious very quickly!

    Fortunately, there's a way to set up a scheduled data import in SQL Server meaning that you don't have to go through the same process each time you want to import a set of data.  There are two main steps involved in this process:

    Use the SQL Server Import and Export Wizard to create a SQL Server Integration Services (SSIS) package.

    Schedule a job which executes the SSIS package according the schedule you want.

    Launching the Import Wizard

    The first step in this process is launching the wizard that is used to import data.  To do this:

    In SQL Server Management Studio, locate your database in the Object Explorer pane at the left hand side of the screen.

    Right-click on the name of the database and choose: Tasks -> Import Data...

    bed8afed982d77332383bde8bb50c4a8.png

    Choose this option to start the import wizard.

    The next part of this blog series explains the steps to follow in the import wizard in order to create an SSIS package.

    Using the Import Wizard in SQL Server

    Once you have opened the import wizard there are several steps to follow in order to create an SSIS package.  This part of the series explains what you need to do.

    Before we get started, if you've opened the wizard and you see a welcome message, just click Next to get to the useful part!

    ec0adc2f571597dac547a4c8659ba8f0.gif

    If you see this message, just click Next.

    Step 1 - Choosing a Data Source

    The first real step of the wizard asks you where your data comes from.

    24ec929df9a46240c8abfb7cd7d6ffd4.png

    Tell the wizard where your data is stored. The numbered steps are explained below.

    Choose the type of file that contains your data.  Here we've chosen Microsoft Excel.  The option you select here affects the subsequent options on this page of the wizard.

    For an Excel spreadsheet you must say where the file is saved.

    Different versions of Excel store their data in different ways so it's important to say which version your file is saved as.  Excel 2007 and 2010 file types are grouped together as Microsoft Excel 2007.

    Specify whether the first row of your data contains the column headings.

    Click Next at the bottom of the dialog box.

    如果出现如下错误:

    TITLE: SQL Server Import and Export Wizard

    The operation could not be completed.

    ADDITIONAL INFORMATION: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

    Step 2 - Choosing a Destination Database

    The next step is to tell the wizard which database you want the data to be imported to.

    cd994f0fe67725c1603898179670bf27.png

    Specify where the data should go when it is imported. The numbered steps are explained below.

    Choose the type of destination source for your data.  The option you select here affects the other options on this page of the wizard.

    As we selected a SQL Server destination for part 1, we need to say which SQL Server we want to use here.

    This option specifies the authentication to be used to connect to the server you've chosen.

    Choose the database you want to send the data to, or click New... to create a new database.

    Click the Next button at the bottom of the wizard.

    Step 6 - Saving the SSIS Package

    If you chose to save the package in the previous step you can specify the name and location here.

    b6db119f6a343eabfd1b59b74b3a112a.png

    Save the SSIS package according to the options you select here.

    Type in a recognisable name for the package - we'll need this later on when we choose to schedule the import steps.

    You can optionally type in a description here.

    Choose the server or filename to save the package depending on the option you selected in the previous step of the wizard.

    Click Next at the bottom of the dialog box.

    Step 7 - Completing the Wizard

    The final step summarises the choices you have made and simply asks to you to confirm everything you have done by clicking Finish at the bottom of the dialog box.

    9f8e7c82c868b3c022b0512ee1e9aa65.png

    Simply click Finish to confirm the settings you have applied.

    If everything works properly you should see a list of tasks being performed in a separate dialog box.

    9a36579dfe4317c8925e57936c392855.png

    Success! Now all you need to do is click Close.

    Viewing an SSIS Package Saved as a File

    You can view your SSIS package in one of two different ways, depending on whether you saved it as part of your computer's file system or on the SQL Server.

    If the package is saved as a file on your computer you can simply browse for it in a Windows Explorer window.

    4226a357a1494c2f354f46544c23242a.png

    The file will have a dtsx extension.

    Viewing an SSIS Package Saved in SQL Server

    In order to view a package saved in SQL Server you must first connect to an SSIS server:

    fb3dd4347e29a839416964f5fc49cc74.png

    Click Connect at the top of the Object Explorer window and choose Integration Services...

    需要使用Run As管理员才能打开

    You will be able to see your SSIS package by expanding the sequence of folders shown below:

    63b080970bec7f39782c05e41ff57945.png

    The SSIS package that we created earlier is shown here.

    You can even run the package by right-clicking on it and choosing Run Package.

    a3b96b78407dfcfa7fa05ac99c2d1c33.png

    Simply click Execute to run the import steps you saved earlier.

    Step 3 - Choose which Data to Import

    In this step you can specify exactly which data you want to import from the data source you selected in step 1.

    i7.jpg

    Here we're going to select all of the data from the Excel spreadsheet, rather than write a separate query to get the data. Click Next to move on to the next step.

    Step 4 - Configure the Tables to which the Data will be Imported

    This step lets you choose which tables the data you are importing will end up in, and also what should happen to the existing data in those tables.

    i8.jpg

    Choose what will be imported and where it will be imported to. The numbered steps are described below.

    Choose the tables, or in this case worksheets, you want to import the data from.

    Choose which table the selected worksheet will be imported to.

    Click Edit Mappings... for more options, as described below.

    i9.jpg

    Use this dialog box to customise the individual fields in the imported data.

    Choose what to do with data that is already in the table you are importing into.  Here we're creating the destination table each time.

    We've also chosen to drop and re-create the destination table each time we run the import.

    Use the Mappings table to control the names and data types of the imported fields.

    Click OK and then Next.

    Step 5 - Choose When to Run the Import Steps

    In this step you can choose whether to run the import immediately or to save the import steps for use later on.

    i10.jpg

    Choose when you want the import to happen.

    We've chosen to not run our import when the wizard ends.

    We've also chosen to save an SSIS Package that contains all of the information required to run the import at a later time.  We've selected to save the package onto the SQL Server, rather than as a separate file on our computer.

    Choose the level of protection for the package.  Here we've chosen not to save sensitive data in the package, meaning that if other people attempt to use this package they will be prompted to fill in the missing information.

    Click Next at the bottom of the dialog box.

    Step 7 - Completing the Wizard

    The final step summarises the choices you have made and simply asks to you to confirm everything you have done by clicking Finish at the bottom of the dialog box.

    i12.jpg

    Simply click Finish to confirm the settings you have applied.

    If everything works properly you should see a list of tasks being performed in a separate dialog box.

    i13.jpg

    Success! Now all you need to do is click Close.

    Viewing an SSIS Package Saved as a File

    You can view your SSIS package in one of two different ways, depending on whether you saved it as part of your computer's file system or on the SQL Server.

    If the package is saved as a file on your computer you can simply browse for it in a Windows Explorer window.

    i14.jpg

    The file will have a dtsx extension.

    Viewing an SSIS Package Saved in SQL Server

    In order to view a package saved in SQL Server you must first connect to an SSIS server:

    i15.jpg

    Click Connect at the top of the Object Explorer window and choose Integration Services...

    You will be able to see your SSIS package by expanding the sequence of folders shown below:

    i16.jpg

    The SSIS package that we created earlier is shown here.

    You can even run the package by right-clicking on it and choosing Run Package.

    i17.jpg

    Simply click Execute to run the import steps you saved earlier.

    Scheduling a Job in SQL Server

    The final stage of this blog series is to create a scheduled job to execute the SSIS package on a regular basis.

    The SQL Server Agent

    You can schedule jobs using the SQL Server Agent.  You should find this at the bottom of the list of objects in any database server that you've connected to in SQL Server Management Studio:

    i18.jpg

    The SQL Server Agent appears at the bottom of the list of objects in a database server.

    Creating a Job

    To create a new job using SQL Server Agent:

    i19.jpg

    Right-click the Jobs folder and choose New Job...

    You can then use the dialog box to set up the job you want to create.  The steps we need to follow in order to schedule our SSIS package execution are described below.

    Step 1 - Enter a Name for the Job

    The first step is to give the new job a sensible name, as shown below:

    i20.jpg

    In the General category, enter a descriptive name for the job.

    Step 2 - Create the Job Steps

    Next, you can create the steps that will make up the job.  Our job should have only one step and here's how to create it:

    Select the Steps page of the dialog box.

    i21.jpg

    Build a list of steps for the job using this page of the dialog box.

    Click the New... button to add a new step to the job.

    i22.jpg

    Use this dialog box to specify the settings for this job step.

    Enter a sensible name for this step of the job.

    Choose the type of action to perform.  Here we want to execute an SSIS package.

    Choose where the SSIS package is stored.  Here we've selected the package that we saved earlier on our SQL Server.

    Click the ellipsis (...) to choose the package you want to execute.

    Click OK to return to the New Job dialog box.

    The job should now consist of a single step:

    i23.jpg

    Our entire job consists of a single step, but we could always add more to this by clicking the New...button at the bottom of the dialog box.

    Step 3 - Creating the Job Schedule

    To ensure that the job runs at a specific time you need to specify the schedule for the job.  To do this:

    Select the Schedules page of the dialog box.

    i24.jpg

    Use this page to set up the job schedule.

    Click New... to create a new schedule.

    Complete the dialog box as shown below:

    i25.jpg

    The options in this page of the dialog box are self-explanatory. The options we have selected here ensures the job will be carried out each week at 9am on a Monday morning.

    Click OK to return to the New Job dialog box.

    Step 5 - Creating the Job

    When you have finished applying all of the settings listed above, you can create the job by simply clicking OK on the New Job dialog box.

    i26.jpg

    Your new job will appear in the Jobs folder within SQL Server Agent.

    继续添加第二个Excel,Task--> Import Data之后如下

    612b3aff6ebcd7e2cd3f75bbdef002f4.png

    添加Schedule的步骤,并设置第一个步骤Success之后跑到下一个步骤。

    ba8bce3a00f101f93002021d1aee0496.png

    展开全文
  • 这个实现起来很简单,就两步,先读取excel文件单元格的值,然后插入SQL数据库中。下面我介绍一下如何从excel文件读取数据,并插入到mysql数据库中,主要用到openpyxl和pymysql这两个包,实验环境win7 python3.6 ...

    这个实现起来很简单,就两步,先读取excel文件单元格的值,然后插入SQL数据库中。下面我介绍一下如何从excel文件读取数据,并插入到mysql数据库中,主要用到openpyxl和pymysql这两个包,实验环境win7 python3.6 pycharm5.0 mysql5.5,主要步骤如下:

    1.下载安装openpyxl和pymysql这两个包,这里直接pip install 包名安装就行,如下图所示,我的环境都已经安装:

    2.为了方便演示,我这里新建了一个test数据表,主要有id,university,和name这三个字段,如下图:

    接着新建了一个test.xlsx文件,主要是把这个excel中的数据插入到test数据表中,样本数据如下:

    3.数据准备完毕后,我们就可以读取excel数据了,主要代码如下,也就是一个读取函数,很简单,不到十行代码:

    4.从excel文件中读取完数据后,我们就可以往mysql数据库中插入了,如下图所示,也就是一个插入函数,代码很简单,十来行代码左右:

    程序运行结果如下,已经成功将excel读取的数据插入到mysql数据库中:

    至此,一个完整的从excel文件读取数据并插入到mysql数据库的过程便完毕。就整个过程来看,其实不难,就是从excel读数据,然后插入到SQL数据库中,只要你熟悉xlrd,openpylx,pynysql等这些常用包,很快就能完成数据的读和写,这里需要注意的是,有一些特殊字符需要进行转义才能插入数据库,像'''等,我这里就不做过多介绍,网上也有很多这方面的教程,你可以参考学习一下,对你的提升也有很大的帮助,希望以上分享的内容能对你有所帮助吧。

    展开全文
  • 这次项目是一个经费管理系统,涉及的数据库记录数超出了以前做过的,每次最少都要从EXCEL导入数据库几千上万条数据,第一次使用一般的方法,执行SQL语句,运用for循环一条一条的插入,重复数据也使用程序判断,后来...
    这次项目是一个经费管理系统,涉及的数据库记录数超出了以前做过的,每次最少都要从EXCEL导入数据库几千上万条数据,第一次使用一般的方法,执行SQL语句,运用for循环一条一条的插入,重复数据也使用程序判断,后来交付的时候出问题了,一次超过一定条数的记录,直接失败,这明显是效率的问题。从网络上找了一下,发现了一个从来没用过的类SqlBulkCopy   这里有详细的说明,我就不说了http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlbulkcopy(VS.80).aspx
     
    接着我使用这个方法导入数据
     
    首先从EXCEL读取数据 到dataset 

    public DataSet GetXlsContent(string SheetName, string FileName)
    {

                tbDataBaseModel tbdbModel = new tbDataBaseModel();
                DataSet ds = new DataSet();
                //string Path = File.Replace(@"//", @"/");
                //string FromFile = FileName.Substring(FileName.LastIndexOf('//') + 1);
                try
                {
                    string ExcelCmd = "";
                    string olestr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileName + ";" + "Extended Properties=Excel 8.0;";
                    OleDbConnection ODC = new OleDbConnection(olestr);
                    ODC.Open();
                    OleDbDataAdapter odda = null;
                    ExcelCmd = string.Format(@"SELECT * FROM [{0}$]", SheetName);
                    odda = new OleDbDataAdapter(ExcelCmd, olestr);
                    odda.Fill(ds, SheetName);
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        return ds;
                    }

                    else
                    {
                        Common.JScript.Alert("空数据!");
                        return null;
                    }
                }
                catch
                {
                    return null;
                }

            }

     

    然后从dataset里将数据通过SqlBulkCopy 类提供的方法(请看msdn详述)导入数据库,经过测试目前导入5W条数据只要4秒到6秒,网络上说只要4.5,我想这些和网速以及服务器的性能有很大的关系

    public bool InserModelRows(DataSet ds)
    {
                SqlBulkCopy sbc = new SqlBulkCopy(SQLHelp.ConnectionString, SqlBulkCopyOptions.FireTriggers);第二个参数有很多枚举,具体请看msdn
                sbc.DestinationTableName = "tbStudent";  这里指定了目标数据库的目标表
                sbc.ColumnMappings.Add("学号", "sStudentNumberID_FK");
                sbc.ColumnMappings.Add("姓名", "sStudentName");
                sbc.ColumnMappings.Add("金额", "mMoney");
                sbc.ColumnMappings.Add("项目", "iItemID_FK");
                sbc.ColumnMappings.Add("出帐日期", "dTime");
                sbc.ColumnMappings.Add("银行帐号", "sCreditCard");以上几行前面的汉字是EXCEL的字段名,第二个是目标表的字段名,要一一对应
                try
                {
                    sbc.WriteToServer(ds.Tables[0]);这里是将ds里的指定的表的数据导入目标表,这里的目标表是tbStudent
                    return true;
                }
                catch
                {

                    return false;
                }

    }

    恩,这样就可以了,当然数据库字段类型的定义得确定,以我的测试,EXCEL里的文本类型,在SQLSERVER里相对应的是nvarchar(255)类型,数字式float类型,如果是主键的话,用int型也可以

    展开全文
  • 我这个实例,是将数据从Excel直接导入SQL-Server数据库中。
  • 到目前为止我使用这软件可以将Excel数据导入到远程的SQL Server库里。只要远程能够连接到数据库就能将信息导入。功能比较完善。
  • There are many ways to import data from an Excel file to a ... 有多种方法可以使用以下方法将数据从Excel文件导入SQL Server数据库: SQL Server Integration Services (SSIS) SQL Server集成服务(SSIS) ...

    There are many ways to import data from an Excel file to a SQL Server database using:

    有多种方法可以使用以下方法将数据从Excel文件导入SQL Server数据库:

    In this article, steps for importing data from an Excel file to a SQL Server database will be explained using the SQL Server Import and Export Wizard including some of problems that can occur during the processes.

    在本文中,将使用SQL Server导入和导出向导解释将数据从Excel文件导入SQL Server数据库的步骤,其中包括在处理过程中可能发生的一些问题。

    To start the process of importing data from an Excel file to a SQL Server database using the SQL Server Import and Export Wizard the SQL Server Import and Export Wizard needs to be launched. There are several ways to do that, and this can be seen on the How to import/export data to SQL Server using the SQL Server Import and Export Wizard page

    要开始使用SQL Server导入和导出向导将数据从Excel文件导入SQL Server数据库的过程,需要启动SQL Server导入和导出向导。 有几种方法可以执行此操作,这可以在如何使用“ SQL Server导入和导出向导”页面向SQL Server导入/导出数据中看到。

    The first page that appears when the SQL Server Import and Export Wizard launched is Welcome page:

    启动SQL Server导入和导出向导时出现的第一页是欢迎页面:

    On this page, only a quick introduction of the SQL Server Import and Export Wizard is shown.

    在此页面上,仅显示SQL Server导入和导出向导的快速介绍。

    Click the Next button to continue. The next page in the SQL Server Import and Export Wizard is Choose a Data Source page:

    单击下一步按钮继续。 SQL Server导入和导出向导的下一页是“选择数据源”页面:

    In the Choose a Data Source page, in order to continue with importing data from Excel to SQL Server the data source provider and way of connecting with data source must be provided. In our case, the provider for connecting to the Excel file is the Microsoft Excel provider.

    在“ 选择数据源”页面中,为了继续将数据从Excel导入SQL Server,必须提供数据源提供程序和与数据源的连接方式。 在我们的情况下,用于连接到Excel文件的提供程序是Microsoft Excel提供程序。

    From the Data source drop down box, select the Microsoft Excel provider:

    数据源下拉框中,选择Microsoft Excel提供程序:

    As you can see, there is no Microsoft Excel provider in the list of the Data source drop down box. There are several reasons for this. The first reason could be that Microsoft Office isn’t installed. But there is no need to install Microsoft Office (Microsoft Excel) in order to see the Microsoft Excel provider in the Data source drop down box list.

    如您所见,数据源下拉框列表中没有Microsoft Excel提供程序。 有几个原因。 第一个原因可能是未安装Microsoft Office 。 但是无需安装Microsoft Office(Microsoft Excel)即可在“数据源”下拉框列表中查看Microsoft Excel提供程序。

    To see the Microsoft Excel provider in the list, install Microsoft Access Database Engine 2016 Redistributable. The latest version of Microsoft Access Database Engine can open the earlier version of Excel, so make sure that you have the latest one.

    若要在列表中查看Microsoft Excel提供程序,请安装Microsoft Access数据库引擎2016 Redistributable 。 最新版本的Microsoft Access数据库引擎可以打开早期版本的Excel,因此请确保您拥有最新版本的Excel。

    The Microsoft Access Database Engine 2016 Redistributable comes with two versions:

    Microsoft Access数据库引擎2016 Redistributable具有两个版本:

    • AccessDatabaseEngine.exe is 32-bit version

      AccessDatabaseEngine.exe是32位版本
    • AccessDatabaseEngine_X64.exe is 64-bit version

      AccessDatabaseEngine_X64.exe是64位版本

    Now, when Microsoft Access Database Engine 2016 Redistributable is installed, we should see the Microsoft Excel provider in the list, but unfortunately the Microsoft Excel provider does not show in the list of the Data source drop down box.

    现在,在安装Microsoft Access Database Engine 2016可再发行组件后 ,我们应该在列表中看到Microsoft Excel提供程序,但是不幸的是,Microsoft Excel提供程序没有显示在“数据源”下拉框的列表中。

    This is because you may run a wrong version of the SQL Server Import and Export Wizard. For example, the AccessDatabaseEngine.exe is installed and the SQL Server Import and Export Wizard 64-bit version is launched. In this case, in order to see the Microsoft Excel provider in the list of the Data source drop down box, launch the SQL Server Import and Export Wizard 32-bit version and the Microsoft Excel provider will appear in the list:

    这是因为您可能运行了错误版本SQL Server导入和导出向导。 例如,安装AccessDatabaseEngine.exe并启动SQL Server导入和导出向导64位版本。 在这种情况下,为了在数据源下拉列表的列表中看到Microsoft Excel提供程序,请启动SQL Server导入和导出向导32位版本,并且Microsoft Excel提供程序将出现在列表中:

    Now, when all is installed, from the list, select the Microsoft Excel provider. On the Choose a Data Source page, additional options appear:

    现在,在全部安装后,从列表中选择Microsoft Excel提供程序。 在“ 选择数据源”页面上,出现其他选项:

    In the Excel file path box, type the location to the Excel file or use the Browse button to navigate to the location:

    在“ Excel文件路径”框中,键入Excel文件的位置,或使用“ 浏览”按钮导航到该位置:

    From the Excel version drop down box, choose the version of Excel that uses the source workbook. In our case, that is the Microsoft Excel 2016 version:

    从“ Excel版本”下拉框中,选择使用源工作簿的Excel版本。 在我们的案例中,这是Microsoft Excel 2016版本:

    The latest option on this page is the First row has column names check box:

    此页面上的最新选项是“ 第一行具有列名”复选框:

    By default, this option is checked. It treads the first rows of the data source as the column names:

    默认情况下,此选项处于选中状态。 它将数据源的第一行作为列名称:

    But if this option is enabled and data source does not contain column names, the SQL Server Import and Export Wizard will add the column names, starting with the name F1 for the first column name:

    但是,如果启用此选项,并且数据源不包含列名,则SQL Server导入和导出向导将添加列名,第一个列名以名称F1开始:

    If this option is disabled and data source contain the column names the SQL Server Import and Export Wizard treats these columns as the first row of data:

    如果禁用此选项,并且数据源包含列名,则SQL Server导入和导出向导会将这些列视为数据的第一行:

    Now, when everything is set on the Choose a Data Source page of the SQL Server Import and Export Wizard, click the Next button to continue.

    现在,在“ SQL Server导入和导出向导”的“选择数据源”页面上完成所有设置后,单击“ 下一步”按钮继续。

    The following warning message may appear after clicking the Next button:

    单击下一步按钮后,可能会出现以下警告消息:

    The operation could not be completed.

    操作无法完成。

    Additional information:

    附加信息:

    The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)

    “ Microsoft.ACE.OLEDB.16.0”提供程序未在本地计算机上注册。 (系统数据)

    Typically, this warning message appears when the SQL Server Import and Export Wizard is launched via SQL Server Management Studio (SSMS) which is a 32-bit application and the 32-bit version of the SQL Server Import and Export Wizard is launched, but you have installed the Microsoft Access Database Engine 2016 Redistributable 64 bit version.

    通常,当SQL Server导入和导出向导通过启动出现此警告消息SQL Server Management Studio中(SSMS),这是一个32位应用程序和的32位版本的SQL Server导入和导出向导启动,但你已安装Microsoft Access数据库引擎2016可再发行的 64位版本。

    There are two solutions for resolving this problem:

    有两种解决此问题的方法:

    Note: The Microsoft Access Database Engine 2016 Redistributable can be installed in quiet mode.

    注意 :可以以安静模式安装Microsoft Access Database Engine 2016可再发行组件。

    Open the Command Prompt window and run the following:

    打开命令提示符窗口,然后运行以下命令

    For 32-bit version

    对于32位版本

    C:\Users\<User_Name>\Downloads\AccessDatabaseEngine.exe /quiet

    C:\ Users \ <用户名> \ Downloads \ AccessDatabaseEngine.exe / quiet

    For 64-bit version

    对于64位版本

    C:\Users\<User_Name>\Downloads\AccessDatabaseEngine_X64.exe /quiet

    C:\ Users \ <用户名> \ Downloads \ AccessDatabaseEngine_X64.exe / quiet

    The next page on the SQL Server Import and Export Wizard is the Choose a Destination page:

    SQL Server导入和导出向导的下一页是“ 选择目标”页:

    On this page, determine the destination where data from the data source (Excel file) will be placed. In our case, the destination will be a SQL Server database.

    在此页面上,确定将放置数据源(Excel文件)中的数据的目的地。 在我们的例子中,目标将是SQL Server数据库。

    Under the Destination drop down box, choose a provider that can connect to a SQL Server database.

    在“ 目标”下拉框中,选择可以连接到SQL Server数据库的提供程序。

    One of providers that can connect to SQL Server is:

    可以连接到SQL Server的提供程序之一是:

    In this case, the SQL Server Native Client 11.0 will be selected from the Destination list:

    在这种情况下,将从“ 目标”列表中选择“ SQL Server Native Client 11.0

    From the Server name combo box, select the SQL Server instance:

    服务器名称组合框中,选择SQL Server实例:

    In the Authentication section, determine how it will be connected to SQL Server by using Windows or SQL Server Authentication mode.

    在“ 身份验证”部分中,确定如何使用Windows或SQL Server身份验证模式将其连接到SQL Server。

    From the Database drop down box, pick a database in which data from data source (Excel file) will be placed:

    在“ 数据库”下拉框中,选择一个数据库,其中将放置来自数据源(Excel文件)的数据:

    Or, create a new database as a destination for data from data source.

    或者,创建一个新数据库作为数据源中数据的目标。

    To do that, click the New button and in the Create Database dialog, set the parameters for the new SQL Server destination database:

    为此,请单击“ 新建”按钮,然后在“ 创建数据库”对话框中,为新SQL Server目标数据库设置参数:

    When everything is set on the Choose a Destination page, click the Next button to proceed.

    在“ 选择目标”页面上设置完所有内容后,单击“ 下一步”按钮继续。

    On the Specify Table Copy or Query page, determine how data from the data source will be copied to the destination:

    在“ 指定表副本或查询”页面上,确定如何将数据源中的数据复制到目标:

    If the Copy data from one or more tables or views radio button is selected then all data from the chosen worksheets will be copied.

    如果选中“从一个或多个表或视图复制数据”单选按钮,则将复制所选工作表中的所有数据。

    If the Write a query to specify the data to transfer is chosen, then only data that are specified in a SQL query will be copied from an Excel worksheet to a destination SQL Server database.

    如果选择了“ 编写查询以指定要传输的数据” ,则仅将SQL查询中指定的数据从Excel工作表复制到目标SQL Server数据库。

    If the Write a query to specify the data to transfer in the Choose a Destination page is chosen, then the Provide a Source Query page will be shown when the Next button is pressed:

    如果在“ 选择目标”页面中选择了“ 编写查询以指定要传输的数据 ,那么在按下“ 下一步”按钮时将显示“ 提供源查询”页面:

    In the SQL statement text box, type the query that will select the data to copy form the Excel file to the SQL Server database or load a query using the Browse button.

    在“ SQL语句”文本框中,键入查询,该查询将选择要从Excel文件复制到SQL Server数据库的数据,或使用“ 浏览”按钮加载查询。

    To successfully query a worksheet the $ to the end of the sheet name and the brackets around sheet name, ([BusinessEntity$]) must be added, otherwise the following warning messages may appear:

    为了成功地查询工作表中$到片材名称的末尾和周围表名称的括号,必须添加([$的BusinessEntity]),否则以下警告消息可能出现:

    The statement could not be parsed.

    该语句无法解析。

    Additional information:

    附加信息:

    The Microsoft Access database engine could not find the object ‘BusinessEntity’. Make sure the object exists and that you spell its name and the path name correctly. If ‘BusinessEntity’ is not a local object, check your network connection or contact the server administrator. (Microsoft Access Database Engine)

    Microsoft Access数据库引擎找不到对象“ BusinessEntity”。 确保对象存在,并且正确拼写了它的名称和路径名。 如果“ BusinessEntity”不是本地对象,请检查您的网络连接或与服务器管理员联系。 (Microsoft Access数据库引擎)

    Or this:

    或这个:

    The statement could not be parsed.

    该语句无法解析。

    Additional information:

    附加信息:

    Syntax error in FROM clause. (Microsoft Access Database Engine)

    FROM子句中的语法错误。 (Microsoft Access数据库引擎)

    If the Copy data from one or more tables or views radio button is chosen, when the Next button is pressed, the Select Source Tables and Views page will be shown:

    如果选择了“从一个或多个表或视图复制数据”单选按钮,则按下“ 下一步”按钮时,将显示“ 选择源表和视图”页面:

    On this page, all worksheets for the Excel file (ImportData.xlsx) will be listed under the Source column. From the Source list, choose from which worksheets you want to import data to the SQL Server database by clicking the check box next to the name of the worksheets. The selected worksheets will appear in the Destination column:

    在此页面上,Excel文件(ImportData.xlsx)的所有工作表将在“ 源”列下列出。 在“源”列表中,通过单击工作表名称旁边的复选框,选择要将数据从哪个工作表导入SQL Server数据库。 所选的工作表将出现在“ 目标”列中:

    The name of the tables in the SQL Server database by default will be the names of the selected worksheets from the Source column, but these names can be changed by clicking on the name in the Destination column:

    默认情况下,SQL Server数据库中表的名称将是“ 源”列中所选工作表的名称,但是可以通过单击“ 目标”列中的名称来更改这些名称:

    As it may be noticed, the icon in the Destination column for the BusinessEntity$ field is different from the Table1 and Table2 fields. This is because the table in the SQL Server database that is chosen as a destination already exists and for the Table1 and Table2 fields, new tables will be created.

    可能已经注意到, BusinessEntity $字段的“目标”列中的图标与Table1和Table2字段不同。 这是因为选择作为目标SQL Server数据库中的表已经存在,并且对于Table1和Table2字段,将创建新表。

    When using an existing table, make sure that the destination table has the same number of columns that have data source, otherwise the columns from the data source that does not have an adequate destination column will be by default ignored (<ignore>) and the data from that columns will not be imported to a destination table:

    使用现有表时,请确保目标表具有相同数目的具有数据源的列,否则,默认情况下将忽略数据源中没有足够目标列的列( <ignore> ),并且该列中的数据不会导入到目标表中:

    Also, make sure that columns in the destination table have data types that are compatible with the data types in the columns of the source data, otherwise the following error may appear:

    另外,请确保目标表中的列具有与源数据的列中的数据类型兼容的数据类型,否则可能会出现以下错误:

    Found 1 unknown column type conversion(s) You are only allowed to save the package

    找到1个未知的列类型转换您只允许保存该程序包

    When the appropriate columns and the data types are set, click the Next button, the Save and Run Package page of the SQL Server Import and Export Wizard will appear:

    设置了适当的列和数据类型后,单击“ 下一步”按钮,将出现“ SQL Server导入和导出向导的“ 保存并运行包”页面:

    Click the Next button if want to import data from an Excel file to a SQL Server database, but if want to save the SSIS package for the later use, check the Save SSIS Package button.

    如果要将数据从Excel文件导入到SQL Server数据库,请单击“ 下一步”按钮,但是如果要保存SSIS包以供以后使用,请选中“ 保存SSIS包”按钮。

    On the Complete the Wizard page, choices that are made in the previous wizard pages are shown:

    在“ 完成向导”页面上,显示在先前的向导页面中做出的选择:

    Click the Next button to import data from an Excel file to a SQL Server database. The Performing Operation page shows the status of the importing process if it is finished successfully or with the errors:

    单击“ 下一步”按钮将数据从Excel文件导入到SQL Server数据库。 “ 执行操作”页面显示导入过程的状态,如果导入过程成功完成或出现错误:

    If the error occurs during the process of importing data from Excel file to the SQL Server database the Messages… word will appear in the Message column for the action that failed:

    如果在将数据从Excel文件导入SQL Server数据库的过程中发生错误,则Messages字样将出现在Message列中,用于失败的操作:

    When click on the Messages…, the View Report dialog will appear with detailed information about the error:

    当单击消息...时,将显示“ 查看报告”对话框其中包含有关错误的详细信息:

    This error:

    这个错误:

    – Validating (Error)

    –验证(错误)

    Messages

    留言内容

    • Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column “numb”.
      (SQL Server Import and Export Wizard)
    • 错误0xc0202049:数据流任务1:无法插入只读列“ numb”。
      (SQL Server导入和导出向导)
    • Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
      (SQL Server Import and Export Wizard)
    • 错误0xc0202045:数据流任务1:列元数据验证失败。
      (SQL Server导入和导出向导)
    • Error 0xc004706b: Data Flow Task 1: “Destination – test1” failed validation and returned validation status “VS_ISBROKEN”.
      (SQL Server Import and Export Wizard)
    • 错误0xc004706b:数据流任务1:“目标– test1”验证失败,并返回验证状态“ VS_ISBROKEN”。
      (SQL Server导入和导出向导)
    • Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
      (SQL Server Import and Export Wizard)
    • 错误0xc004700c:数据流任务1:一个或多个组件验证失败。
      (SQL Server导入和导出向导)
    • Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
      (SQL Server Import and Export Wizard)
    • 错误0xc0024107:数据流任务1:任务验证期间发生错误。
      (SQL Server导入和导出向导)

    Typically, it appears when the destination table has an IDENTITY column. To resolve this, turn back to the Select Source Tables and Views page, select the tables that have identity property and press the Edit Mappings button. From the Transfer Settings dialog, select the Enable identity insert check box:

    通常,它在目标表具有IDENTITY列时显示。 要解决此问题,请返回“ 选择源表和视图”页面,选择具有标识属性的表,然后按“ 编辑映射”按钮。 在“ 传输设置”对话框中,选中“ 启用身份插入”复选框:

    Also, another common problem that may appear when importing data from data source to the destination SQL Server tables is the FOREIGN KEY Constraints problem. In the error below two destination tables test1 and test2 are shown. The test2 table is referenced to the test1 table:

    另外,将数据从数据源导入到目标SQL Server表中时可能出现的另一个常见问题是FOREIGN KEY Constraints问题。 在下面的错误中,显示了两个目标表test1和test2。 test2表引用到test1表:

    – Copying to [dbo].[test1] (Error)

    –复制到[dbo]。[test1](错误)

    Messages

    留言内容

    • Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Destination 1 – test2” (79) failed with error code 0xC0209029 while processing input “Destination Input” (92). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
      (SQL Server Import and Export Wizard)
    • 错误0xc0047022:数据流任务1:SSIS错误代码DTS_E_PROCESSINPUTFAILED。 处理输入“目标输入”(92)时,组件“目标1 – test2”(79)的ProcessInput方法失败,错误代码为0xC0209029。 所标识的组件从ProcessInput方法返回了错误。 该错误特定于组件,但该错误是致命的,将导致数据流任务停止运行。 在此之前可能会发布错误消息,其中包含有关失败的更多信息。
      (SQL Server导入和导出向导)
    • Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in “Destination – test1” has started.
      (SQL Server Import and Export Wizard)
    • 信息0x402090df:数据流任务1:“目标– test1”中数据插入的最后提交已开始。
      (SQL Server导入和导出向导)
    • Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in “Destination – test1” has ended.
      (SQL Server Import and Export Wizard)
    • 信息0x402090e0:数据流任务1:“目标– test1”中数据插入的最后提交已结束。
      (SQL Server导入和导出向导)

    – Copying to [dbo].[test2] (Error)

    –复制到[dbo]。[test2](错误)

    Messages

    留言内容

    • Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
      An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “The statement has been terminated.”.
      An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Cannot insert the value NULL into column ‘No_id’, table ‘ImportData.dbo.test2’; column does not allow nulls. INSERT fails.”.
      (SQL Server Import and Export Wizard)
    • 错误0xc0202009:数据流任务1:SSIS错误代码DTS_E_OLEDBERROR。 发生OLE DB错误。 错误代码:0x80004005。
      OLE DB记录可用。 来源:“ Microsoft SQL Server本机客户端11.0”结果:0x80004005说明:“该语句已终止。”。
      OLE DB记录可用。 来源:“ Microsoft SQL Server Native Client 11.0”结果:0x80004005说明:“无法将值NULL插入表'ImportData.dbo.test2'的列'No_id'中; 列不允许为空。 INSERT失败。”。
      (SQL Server导入和导出向导)
    • Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “Destination 1 – test2.Inputs[Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “Destination 1 – test2.Inputs[Destination Input]” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
      (SQL Server Import and Export Wizard)
    • 错误0xc0209029:数据流任务1:SSIS错误代码DTS_E_INDUCEDTRANSFORMFAILUREONERROR。 “目标1 – test2.Inputs [目标输入]”失败,因为发生了错误代码0xC020907B,并且“目标1 – test2.Inputs [目标输入]”上的错误行配置表示发生错误。 指定组件的指定对象发生错误。 在此之前可能会发布错误消息,其中包含有关失败的更多信息。
      (SQL Server导入和导出向导)

    To resolve this problem, disable constraint for referenced table (test2) by executing the following code in SSMS:

    要解决此问题,请通过在SSMS中执行以下代码来禁用对引用表(test2)的约束:

    ALTER TABLE test2 NOCHECK CONSTRAINT ALL
    

    After importing data from data source to the destination SQL Server database, execute the following code in SSMS to enable constraint for the test2 table:

    将数据从数据源导入到目标SQL Server数据库后,在SSMS中执行以下代码以启用对test2表的约束:

    ALTER TABLE test2 WITH CHECK CHECK CONSTRAINT ALL
    

    In some cases, the warning message like from the image below may appear:

    在某些情况下,可能会出现如下图所示的警告消息:

    – Validating (Warning)

    –验证(警告)

    Messages

    留言内容

    • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “AddressLine1” with a length of 255 to database column “AddressLine1” with a length of 60.
      (SQL Server Import and Export Wizard)
    • 警告0x802092a7:数据流任务1:由于将数据从长度为255的数据流列“ AddressLine1”插入长度为60的数据库列“ AddressLine1”,可能会发生截断。
      (SQL Server导入和导出向导)
    • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “AddressLine2” with a length of 255 to database column “AddressLine2” with a length of 60.
      (SQL Server Import and Export Wizard)
    • 警告0x802092a7:数据流任务1:由于将数据从长度为255的数据流列“ AddressLine2”插入到长度为60的数据库列“ AddressLine2”,可能会发生截断。
      (SQL Server导入和导出向导)
    • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “City” with a length of 255 to database column “City” with a length of 30.
      (SQL Server Import and Export Wizard)
    • 警告0x802092a7:数据流任务1:由于将长度为255的数据流列“ City”中的数据插入长度为30的数据库列“ City”中,因此可能会发生截断。
      (SQL Server导入和导出向导)
    • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “AddressLine1” with a length of 255 to database column “AddressLine1” with a length of 60.
      (SQL Server Import and Export Wizard)
    • 警告0x802092a7:数据流任务1:由于将数据从长度为255的数据流列“ AddressLine1”插入长度为60的数据库列“ AddressLine1”,可能会发生截断。
      (SQL Server导入和导出向导)
    • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “AddressLine2” with a length of 255 to database column “AddressLine2” with a length of 60.
      (SQL Server Import and Export Wizard)
    • 警告0x802092a7:数据流任务1:由于将数据从长度为255的数据流列“ AddressLine2”插入到长度为60的数据库列“ AddressLine2”,可能会发生截断。
      (SQL Server导入和导出向导)
    • Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column “City” with a length of 255 to database column “City” with a length of 30.
      (SQL Server Import and Export Wizard)
    • 警告0x802092a7:数据流任务1:由于将长度为255的数据流列“ City”中的数据插入长度为30的数据库列“ City”中,因此可能会发生截断。
      (SQL Server导入和导出向导)
    • Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system’s console.
      (SQL Server Import and Export Wizard)
    • 警告0x80049304:数据流任务1:警告:无法打开全局共享内存以与性能DLL进行通信;无法执行此操作。 数据流性能计数器不可用。 要解决此问题,请以管理员身份或在系统控制台上运行此程序包。
      (SQL Server导入和导出向导)

    To resolve this, go to the SQL Server destination table and increase the column size for the columns that are listed in the warning message.

    若要解决此问题,请转到SQL Server目标表,并增加警告消息中列出的列的列大小。

    To verify that the data from the Excel file are imported to the SQL Server database, go to SSMS, find the database in which data are imported and list all data from the tables:

    若要验证将Excel文件中的数据导入到SQL Server数据库,请转到SSMS,找到要在其中导入数据的数据库,并列出表中的所有数据:

    参考文献: ( References: )

    翻译自: https://www.sqlshack.com/import-data-excel-file-sql-server-database/

    展开全文
  • 在此我们介绍的是通过SQLServer的Excel导入功能进行上述操作。 OK我们开始吧。 图一: 在数据库中点击右键 任务 - > 导入数据 图二: 我们将得到SQLServer的导入和导出功能的面板。 图三:我们...
  • 把存储在在Excel中的多条User类数据导入到目标数据库 准备阶段: 配置了Mysql数据库 编译器导入了 jxl.jar 包,Excel表格处理包 编译器导入sql.jar 包,Mysql包 全部代码如下: JavaBean类(User类): package...
  • 1.开启Ad Hoc Distributed Queries组件,在sql查询编辑器中执行如下语句: exec sp_configure ‘show advanced options’,1 reconfigure exec sp_configure ‘Ad Hoc Distributed Queries’,1 reconfigure 2.关闭Ad ...
  • 1、在SQL数据库中直接从Excel里面查询数据:select \* fromOPENROWSET('MICROSOFT.JET....DATABASE=c:\\test.xls',sheet1$)2、从Excel文件中,导入数据到SQL数据库中,select\*into表from![](http://images.csdn.net/s...
  • 前端数据: <a class="button_type1 addTabPage" ng-click="importDatas()"><i class="iconfont">...导入</a> <!-- 配置信息表模态框(Modal) --> <div class="modal fade"...
  • 这个是Excel的,比如是test.xls欠费年份 欠费开始月份 欠费结束月份 应缴金额(月租)2001 9 12 94.42008 5 12 88.82010 8 12 ...
  • 从excel工作表中读取数据逐行执行insert语句插入到sqlserver为了简单起见只选取了三个个字段作为参数,日期,字符类型的需要加上''Spoon是作业配置的GUI界面,配置好后可以通过控制台提示符方式执行1:cmd方式运行1....
  • excel导入sql2005数据库

    千次阅读 2007-08-28 10:56:00
    然后就是判断是否是Excel文件(这里需要注意),如果是就可以新建个路径,然后把导入Excel表保存到web站点的相关路径中(这里也需要注意因为我想在建立数据表时这个路径的Excel表格导入)。 2)使用sql语句进行...
  • C#从Excel文件向SQL数据库中大批量导入数据,可以批量向数据库中添加基础数据,WinfForm的,使用起来非常方便。
  • 使用sublime 同步编辑从excel导入数据到SQL Server数据库
  • 数据库提示: Conversion failed when converting date and/or time from character string. The statement has been terminated. 也就是 转换日期和/或时间字符串转换失败。 该声明已终止。 经过调查,错误的原因...
  • 如何将Excel数据批量导入SQL数据库

    千次阅读 2009-07-13 08:53:00
    很多人通过循环来拼接sql,这样做不但容易出错而且效率低下,System.Data.SqlClient.SqlBulkCopy 对于新手来说还是比较陌生的,这个就是传说中效率极高的bcp,6万多数据从excel导入sql只需要4.5秒。Code:using ...
  • 导入" OnClick="btnImport_Click" /> 后台代码: protected void btnImport_Click(object sender, EventArgs e) { if (FileUpload1.HasFile) { string fileName = Server.HtmlEncode...
  • Excel导入sql数据库

    千次阅读 2010-03-31 16:15:00
    // 1、如何从Excel中获得工作单(sheetname): //获得Excel中的所有sheetname。 public ArrayList ExcelSheetName ( string filepath ) { ArrayList al = new ArrayList (); string strConn; strConn =
  • Scheduling Data Imports in SQL ...Importing data into a SQL Server database isn't really that tricky: there's a straightforward wizard that you can follow to get information from Microsoft Excel,...
  • 2、选择数据源 Microsoft Excel 97-2000,文件名 选择要导入的xls文件,按 下一步 , 3、 选择目的 用于SQL Server 的Microsoft OLE DB提供程序,服务器选择本地(如果是本地数据库的话,如 VVV),使用SQL ...
  • excel中数据导入sql数据库  select * into #gaxin from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=c:\Documents and Settings\
  • 可以将excle文件导入SQL SERVER 的工具,一个将excel文件导入到SQLServer表中的程序 一 双击Input.exe运行程序,将弹出一个窗口,这时请你在"数据库名"后面的输入栏中输入数据库名(如果是千方百剂就是输入帐套名)....
  • /*=================== 导入/导出 Excel 的基本方法 ===================*/从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:/*===================================================================...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,016
精华内容 406
关键字:

从excel导入sql数据库