實(shí)現(xiàn)的功能為：

1：從csv文件中的第一行提取列名生成數(shù)據(jù)庫表
2：將原始的csv文件轉(zhuǎn)換成sqlloder能識別的csv(增加列），根據(jù)時(shí)間列生成一個(gè)整形主鍵列
3：生成ctl控制文件
4：啟動Sqlloder并導(dǎo)入

這里做記錄主要是導(dǎo)入時(shí)出現(xiàn)了莫名其妙的問題

第一行第一列總是會報(bào)錯(cuò)誤
第一列為數(shù)字列，則SQLLoader報(bào) ORA-01722
第一列調(diào)整成時(shí)間列并將數(shù)字列放到最后，則SQLLoader報(bào)時(shí)間列轉(zhuǎn)換錯(cuò)誤。
參考各種文章不能解決，參考的解決方案包括
1：在ctl文件中指定字段類型
https://blog.csdn.net/qq_25798961/article/details/88534104

CHAR                         字符型
INTEGER EXTERNAL    整型
DECIMAL EXTERNAL    浮點(diǎn)型
DATE 'YYYYMMDD'      日期型

數(shù)字列增加 選項(xiàng) INTEGER EXTERNAL 沒有效果
2：數(shù)據(jù)列做轉(zhuǎn)換
https://community.oracle.com/tech/developers/discussion/3684710/ora-01722-invalid-number-in-sql-loader

Options(errors=10000,skip=3)
LOAD DATA
INFILE '/home/appltest/Sheet1.csv'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
REPLACE
INTO TABLE sampl_table
fields terminated by ','
optionally enclosed by '"'  
trailing nullcols
(
customer_name  CHAR NULLIF customer_name = BLANKS "RTRIM(:customer_name)",
customer_no  CHAR NULLIF customer_no = BLANKS "RTRIM(:customer_no)",
item_no  CHAR NULLIF item_no = BLANKS "RTRIM(:item_no )",
field1      FILLER,
field2      FILLER,
valid_amount_month1    "to_number(:valid_amount_month1 , '999,999,999.99')"  ,
valid_units_month1      "to_number(substr(:valid_units_month1,instr(:valid_units_month1,'$')+1),'999,999,999.99')",
field3      FILLER,
field4      FILLER,
valid_budget_units_month1    "to_number(:valid_budget_units_month1, '999,999,999.99')"                                      ,
valid_budget_amount_month1  "to_number(trim(substr(:valid_budget_amount_month1,instr(:valid_budget_amount_month1,'$')+1)),'999,999,999.99')"    ,
valid_rate    CHAR NULLIF valid_rate = BLANKS "RTRIM(:valid_rate)"
)

3：ctl文件增加 "str '\r\n'" 項(xiàng)沒有效果
3：ctl文件第一列加上"replace(replace(:CHANNELKEY, CHR(13), ''), CHR(10), '')" 配置項(xiàng)，沒有效果

最終通過以下方案解決

1：設(shè)置生成的csv中包含一行頭信息
2：ctl中包含一個(gè)OPTIONS(skip=1)配置，跳過頭信息行的導(dǎo)入
究其根本仍不知道是 文件編碼 還是 windows下?lián)Q行 等等原因?qū)е碌摹?/p>

核心代碼如下：

  public void Process()
        {
            ClearFiles();
            ReBuildDataFile(); //解析-1：將輸入文件轉(zhuǎn)換成Sqlloder可識別的文件，拼接時(shí)間，生成主鍵
            CreateDataTable(); //解析-2：生成表結(jié)構(gòu)
            BuildCtlFile();    //解析-3：生成Sqlloder控制文件
            StartSqlloader();  //解析-4：啟動Sqlloder，執(zhí)行導(dǎo)入
        }


        //生成sqloader可識別的控制文件
        private string BuildCtlFile()
        {
            string strError = string.Empty;
            string strColumns = AttachKeyColumn();      
            strColumns = strColumns.Replace("DATETIMES", "DATETIMES \"to_timestamp(:DATETIMES,'yyyy-mm-dd hh24:mi:ss.ff')\"");
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("OPTIONS(skip=1)");
            sb.AppendLine("load data");
            sb.AppendLine(string.Format("infile '{0}' \"str '\\r\\n'\"", GetCsvFileFullName()));
            sb.AppendLine(string.Format("append into table {0}", GetTableName()));
            sb.AppendLine(string.Format("fields terminated by '{0}'", this.splitChar.ToString()));
            sb.AppendLine("optionally enclosed by '\"'");
            sb.AppendLine("trailing nullcols");
            sb.AppendLine(string.Format("({0})", strColumns));
            GenerateFile(GetCtlFileFullName(), sb.ToString());
            return strError;
        }

        private string StartSqlloader()
        {
            string strError = string.Empty;
            string output = string.Empty;
            bool processOk = true;
            using (System.Diagnostics.Process p = new System.Diagnostics.Process())
            {
                p.StartInfo.FileName = this.SqlLoaderConfig;
                p.StartInfo.Arguments = string.Format(" {0} control={1} log={2}", this.OracleDBConfig, GetCtlFileFullName(), GetCtlFileFullName().Replace("ctl", "log"));
                p.StartInfo.CreateNoWindow = true; //不再新窗口啟動
                p.StartInfo.UseShellExecute = false; //不使用操作系統(tǒng)的shell啟動
                p.StartInfo.RedirectStandardOutput = true; //輸出重定向
                p.StartInfo.RedirectStandardError = true;
                p.StartInfo.RedirectStandardInput = true;
                //p.Exited += P_Exited;
                p.Start();
                output = p.StandardOutput.ReadToEnd();
                //ExitCode 11g 12c和9i不同
                if (p.ExitCode == 1) //Ex_Fail
                {
                    processOk = false;
                }
                if (p.ExitCode == 2) //Ex_Warn
                {
                    processOk = false;
                }
                if (p.ExitCode == 4) //Ex_FTL
                {
                    processOk = false;
                }
            }
            if (!processOk)
            {
                throw new IOException(output);
            }

            return strError;
        }

原始CSV文件為：

DATETIMES,CD_000_0,CD_000_1,CD_000_2
2022-09-19 16:11:17.720,100,200,300
2022-09-19 16:11:17.721,101,201,301
2022-09-19 16:11:17.722,102,202,302
2022-09-19 16:11:17.723,103,203,303
2022-09-19 16:11:17.724,104,204,304
2022-09-19 16:11:17.725,105,205,305
2022-09-19 16:11:17.726,106,206,306
2022-09-19 16:11:17.727,107,207,307
2022-09-19 16:11:17.728,108,208,308
2022-09-19 16:11:17.729,109,209,309
2022-09-19 16:11:17.730,110,210,310
2022-09-19 16:11:17.731,111,211,311
2022-09-19 16:11:17.732,112,212,312
2022-09-19 16:11:17.733,113,213,313
2022-09-19 16:11:17.734,114,214,314
2022-09-19 16:11:17.735,115,215,315
2022-09-19 16:11:17.736,116,216,316
2022-09-19 16:11:17.737,117,217,317
2022-09-19 16:11:17.738,118,218,318
2022-09-19 16:11:17.739,119,219,319
2022-09-19 16:11:17.740,120,220,320

轉(zhuǎn)換成的CSV文件為：

CHANNELKEY INTEGER EXTERNAL,DATETIMES,CD_000_0,CD_000_1,CD_000_2
4295477720,2022-09-19 17:11:17.720,100,200,300
4295477721,2022-09-19 17:11:17.721,101,201,301
4295477722,2022-09-19 17:11:17.722,102,202,302
4295477723,2022-09-19 17:11:17.723,103,203,303
4295477724,2022-09-19 17:11:17.724,104,204,304
4295477725,2022-09-19 17:11:17.725,105,205,305
4295477726,2022-09-19 17:11:17.726,106,206,306
4295477727,2022-09-19 17:11:17.727,107,207,307
4295477728,2022-09-19 17:11:17.728,108,208,308
4295477729,2022-09-19 17:11:17.729,109,209,309
4295477730,2022-09-19 17:11:17.730,110,210,310
4295477731,2022-09-19 17:11:17.731,111,211,311
4295477732,2022-09-19 17:11:17.732,112,212,312
4295477733,2022-09-19 17:11:17.733,113,213,313
4295477734,2022-09-19 17:11:17.734,114,214,314
4295477735,2022-09-19 17:11:17.735,115,215,315
4295477736,2022-09-19 17:11:17.736,116,216,316
4295477737,2022-09-19 17:11:17.737,117,217,317
4295477738,2022-09-19 17:11:17.738,118,218,318
4295477739,2022-09-19 17:11:17.739,119,219,319
4295477740,2022-09-19 17:11:17.740,120,220,320

生成的ctl文件為：

OPTIONS(skip=1)
load data
infile 'D:\******\TemplateData\1cf37b73-6727-4618-b636-73a9292ef33d.csv' "str '\r\n'"
append into table FZ_41657_01
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(CHANNELKEY INTEGER EXTERNAL,DATETIMES "to_timestamp(:DATETIMES,'yyyy-mm-dd hh24:mi:ss.ff')",CD_000_0,CD_000_1,CD_000_2)

附ctl文件說明：

https://blog.csdn.net/demonson/article/details/79712207

OPTIONS (skip=1,rows=128) -- sqlldr 命令顯示的 選項(xiàng)可以寫到這里邊來,skip=1 用來跳過數(shù)據(jù)中的第一行  
LOAD DATA  
INFILE "users_data.csv" --指定外部數(shù)據(jù)文件，可以寫多 個(gè) INFILE "another_data_file.csv" 指定多個(gè)數(shù)據(jù)文件  
--這里還可以使 用 BADFILE、DISCARDFILE 來指定壞數(shù)據(jù)和丟棄數(shù)據(jù)的文件，  
truncate --操作類型，用 truncate table 來清除表中原有 記錄  
INTO TABLE users -- 要插入記錄的表  
Fields terminated by "," -- 數(shù)據(jù)中每行記錄用 "," 分隔  
Optionally enclosed by '"' -- 數(shù)據(jù)中每個(gè)字段用 '"' 框起，比如字段中有 "," 分隔符時(shí)  
trailing nullcols --表的字段沒有對應(yīng)的值時(shí)允 許為空  
(  
  virtual_column FILLER, --這是一個(gè)虛擬字段，用來跳 過由 PL/SQL Developer 生成的第一列序號  
  user_id number, --字段可以指定類型，否則認(rèn) 為是 CHARACTER 類型, log 文件中有顯示  
  user_name,  
  login_times,  
  last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相當(dāng)用 to_date() 函數(shù)轉(zhuǎn)換  
)  

主鍵列 CHANNELKEY，生成規(guī)則為：

 private string FixKeyValue(DateTime time)
        {
            TimeSpan toNow = time.Subtract((new DateTime(time.Year, time.Month - 1, 1)));
            //return (toNow.Ticks / 10000).ToString().Substring(3);
            return (toNow.Ticks / 10000).ToString();
        }