Using SQL * Loader to export data to Excel Oracle

2010-03-30  来源:本站原创  分类:Database  人气:477 

Achieve the purpose: to oracle database, insert data in excel file to achieve these steps:
1, open MicroSoft Excel 2000
2, file (F) → New (N) → → Workbook
3, input data, save the file as test.xls,
4, file (F) → Save As (A) →
Save type: tab-delimited, named text.txt, saved to C:

5, must first create the table structure:
Connected to the SQL * Plus, to system / manager user login
SQL> conn system / manager
Create table structure
SQL> create table test
(

id number, - number
username varchar2 (10), - user name
password varchar2 (10), - password
sj varchar2 (20) - build date
);

6, SQL * Loader creates the required input data files are saved to C:, use Notepad to edit:
Control file: input.ctl, reads as follows:
load data - 1, control file identifies infile 'test.txt' - 2, to the input data file named test.txt append into table test - 3, append records to table test fields terminated by X'09 ' - 4, field termination at X'09 ', is a tab (TAB)
(Id, username, password, sj) ----- define the column corresponding to the order of a, insert, the default mode, the start of the data loading requirements table is empty b, append, append a new record in the table c, replace, Remove the old record, replaced with a new load of records d, truncate, ibid

7, in the DOS window using the SQL * Loader command for data input C:> sqlldr userid = system / manager control = input.ctl
The default log file name: input.log
The default log file is bad: input.bad
If it is remote to the database import operation, the input string should read:
C:> sqlldr userid = system / manager @ serviceName_192.168.1.248 control = input.ctl
8, to connect to SQL * Plus, look at the success of input, comparable input.log original test.xls file to see whether all the imported data, whether imported successfully.

相关文章
  • Using SQL * Loader to export data to Excel Oracle 2010-03-30

    Achieve the purpose: to oracle database, insert data in excel file to achieve these steps: 1, open MicroSoft Excel 2000 2, file (F) → New (N) → → Workbook 3, input data, save the file as test.xls, 4, file (F) → Save As (A) → Save type: tab-delimited,

  • sqlserver using BCP to export data to EXCEL, CSV file 2010-06-10

    Whether using a batch, or. Net programs, stored procedures can use this command. When we encounter a large number of database import, export, if excruciatingly slow, consider using it. In Sqlserver, you can also import Excel, than you write a program

  • jqgrid demo Export Data to Excel 2010-06-19

    Export Data to Excel jqgrid with functional demo: http://www.trirand.net/demophp.aspx

  • PL / SQL Developer tool to export data to excel, import excel data into a table 2011-04-15

    Use PL / SQL Developer tool. Export: 1 execute a select statement to query the need to export data. 2. Right in the data list, select the save results. Save. Csv file, and then have excel open on OK. You can save as xsl. Import: In the case of small

  • Using SQL * LOADER, the text data into the database 2010-05-11

    Using SQL * LOADER ... First, write a CTL file, result.ctl content: load data infile 'D: DATAFILE.TXT' into table TAB (Col1 char terminated by ',', col2 char terminated by ',', col3 char terminated by ',', col4 char terminated by whitespace) Descript

  • Export data to Excel to DataGridView 2010-06-25

    /// <summary> /// Export data to the DataGridView Excel( When the page ) /// </summary> /// <param name="dataGridView1"></param> public void print(DataGridView dataGridView1) { // Export to execl try { // No data, no furt

  • How to Export Data to Excel from an ASP.NET Application + Avoid the File Format 2011-09-29

    Original link: http://blogs.msdn.com/b/erikaehrli/archive/2009/01/30/how-to-export-data-to-excel-from-an-asp-net-application-avoid-the -file-format-differ-prompt.aspx 30 Jan 2009 5:25 PM This is a common task for ASP.NET developers. You have a Web ap

  • sql loader csv files uploaded to the Oracle database 2010-09-17

    sql loader can store some data in text format smoothly into oracle database, is a kind of data migration between different databases very convenient and versatile tool. Defects on more slowly, while other types of data on the blob a little trouble. T

  • sql loader csv file upload to an Oracle database 2010-09-17

    sql loader can store some data in text format smoothly into the oracle database, is a kind of data migration between different databases is very convenient and versatile tool. Drawback is slow, the other types of data on the blob so little trouble. s

  • SQL SERVER, and import and export data from EXCEL 2011-09-02

    1. In SQL SERVER query Excel Data : -- ====================================================== SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User')...[Sheet1$] The following is an example of a query, it is u

  • PL SQL developer to export data 2010-04-15

    If only export the table structure (built table statement), does not export the table data can be used Tools - Export User Objects Select to export the table, set up to export the path and all parameters, click Export on the OK PS: this approach is t

  • Will export data to excel flex page 2010-04-20

    [Size = medium] to achieve the flex in this case the data using as3xls-1 [1] .0. Swc export to excel file flash.filesystem .* Need to flex AIR project environment in the flex web application engineering environment, there is no flash.filesystem .* <?

  • mysql export data to excel 2011-04-28

    1 export select * from test outfile '/ tmp / reg.xls' Will export the data in the / tmp directory 2. Transcoding because the excel default encoding is GB3212, Chinese data, there is need for conversion, the conversion command iconv-futf8-tgb2312-oreg

  • javascript to export data to excel in the table (to support export form lines) 2010-06-16

    <HTML> <HEAD> <META http-equiv=Content-Type content="text/html; charset=utf-8"> <title>WEB Page of the export to EXCEL document </title> </HEAD> <body> <BR> <table id = "PrintA" width=

  • Export Data to Excel JS 2010-09-13

    function method2 (tableid) / / read the table to EXCEL in each unit ( var curTbl = document.getElementById (tableid); try ( var oXL = new ActiveXObject ("Excel.Application"); ) catch (e) ( alert ("You must install Excel2000 or more, while t

  • Flex page will export data to excel 2010-11-05

    Operating environment is the RIA <?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute"> <mx:Script> <![CDATA[ import mx.contr

  • SQL Server 2005 export data table of contents 2011-01-09

    SQL SERVER 2005 comes with a stored procedure named UspOutputData, as long as specified in the query exec UspOutputData data table name. If the database does not have this stored procedure, then create the following stored procedure. CREATE PROCEDURE

  • POI Export Data to Excel 2010-06-13

    import java.io.FileOutputStream; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; impo

  • datagrid export data to Excel 2010-11-18

    <?xml version="1.0" encoding="utf-8"?> <s:Application xmlns:fx="http://ns.adobe.com/mxml/2009" xmlns:s="library://ns.adobe.com/flex/spark" xmlns:mx="library://ns.adobe.com/flex/mx" minWidth="

  • sql server2005 tables export data - stored procedure 2010-12-21

    Create the stored procedure: The following stored procedure code: CREATE PROCEDURE dbo.UspOutputData @ Tablename sysname AS declare @ column varchar (1000) declare @ columndata varchar (1000) declare @ sql varchar (4000) declare @ xtype tinyint decla