将XL文件导入到Oracle数据库. [英] Importing XL file to Oracle Database.

问题描述

亲爱的所有人，

我正在进行数据迁移..
这需要将大量数据从excel文件导入数据库中的相应表..
您能帮我简单的数据验证技术吗?
数据导入.. ，请...

解决方案

我建​​议SQLLDR.exe是您的最佳选择.

SQLLDR是Oracle Corporation提供的Oracle Utility.

按照步骤.

1.由于sqlldr doe不接受Excel格式，因此另存为.CSV文件作为excel文件.

2.请参阅此链接以了解 Sqlldr [ Google [ // 打开excel文件，读取记录然后作为数据表返回 // 作者:Algem Mojedo 公共 DataTable xlsInsert(字符串 pth，字符串架构) { 字符串 strcon = 字符串 .Empty; 如果(Path.GetExtension(pth).ToLower().Equals(" )) { strcon = " + pth + " ; } 其他 { strcon = " + pth + " ; } 字符串 strselect = " ; DataTable exDT = 新 DataTable(); 使用(OleDbConnection excelCon = 新 OleDbConnection(strcon)) { 尝试 { excelCon.Open(); 使用(OleDbDataAdapter exDA = 新 OleDbDataAdapter(strselect，excelCon)) { exDA.Fill(exDT); } } 捕获(OleDbException oledb) { 抛出 新异常(oledb.Message.ToString()); } 最终 { excelCon.Close(); } for ( int i = 0 ; i < exDT.Rows.Count; i ++) { 如果(exDT.Rows [i] [" ].ToString()== 字符串 .Empty)// 如果(exDT.Rows.Count == 0 ) { 抛出 新异常(" 上传的文件未找到任何记录."); } 返回 exDT; }

编码愉快...

Dear All,

I am working with data migration..
this needs to import bulk data from excel file to respective tables in the database..
could you please help me on the easy techniqes for data validation...
data importing.. please...

解决方案

I am Suggesting SQLLDR.exe is the best option for you.

SQLLDR is the Oracle Utility provided by Oracle Corporation.

Follow the steps.

1. Save as your excel file as .CSV file, because sqlldr doe''s not accept the Excel formats.

2. see this links for understand the Sqlldr[^]
Google[^]

3. create a control file. upload the data to the corresponding tables.

Advantages :
1. SQLLDER.exe will automatically generate a log file of the upload process.
2. SQLLDER.exe will generates a BAD file, which records is not upload Successfully.
3. It''s very fast.


Thanks
--RA

Hi,

I thing you need this example function...
Subsequent code is pretty simple…

// Open excel file, read records then return as datatable
 // Author: Algem Mojedo  
 public DataTable xlsInsert(String pth, String Schema)
 {
     string strcon = string.Empty;
     if (Path.GetExtension(pth).ToLower().Equals(".xls"))
     {
                strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                                + pth +
                                ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
     }
     else
     {
        strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                  + pth +
                 ";Extended Properties=\"Excel 12.0;HDR=YES;\"";

     }
     string strselect = "Select * from [Sheet1

"; DataTable exDT = new DataTable(); using (OleDbConnection excelCon = new OleDbConnection(strcon)) { try { excelCon.Open(); using (OleDbDataAdapter exDA = new OleDbDataAdapter(strselect, excelCon)) { exDA.Fill(exDT); } } catch (OleDbException oledb) { throw new Exception(oledb.Message.ToString()); } finally { excelCon.Close(); } for (int i = 0; i < exDT.Rows.Count; i++) { if (exDT.Rows[i]["CARDNO"].ToString() == string.Empty) // colummn CARDNO must not emplty { exDT.Rows[i].Delete(); } } exDT.AcceptChanges(); if (exDT.Rows.Count == 0) { throw new Exception("File uploaded has no record found."); } return exDT; }

Happy coding...

这篇关于将XL文件导入到Oracle数据库.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！