我正在通过存储过程运行大容量插入。我得到以下错误。它正在为其他文件运行，但失败会为下面的CSV示例行引发错误。我跳到了BULK INSERT语句的前面。
有人能帮我摆脱痛苦吗？
3行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。Msg 4864，级别16，状态1，第3行
第4行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。消息4864，级别16，状态1，第3行
第5行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。消息4864，级别16，状态1，第3行
第6行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。消息4864，级别16，状态1，第3行
第7行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。消息4864，级别16，状态1，第3行
第8行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。消息4864，级别16，状态1，第3行
第9行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。消息4864，级别16，状态1，第3行
第10行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。消息4864，级别16，状态1，第3行
第11行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。消息4864，级别16，状态1，第3行
第12行第7列(bbg_ticker_tri(的大容量加载数据转换错误(指定代码页的类型不匹配或无效字符(。消息4865，级别16，状态1，第3行
由于超过了最大错误数(10(，因此无法进行大容量加载。Msg7399，级别16，状态1，第3行
OLE DB提供程序"；BULK"；对于链接服务器"；(null("；报告了一个错误。提供程序没有提供有关该错误的任何信息。Msg7330，级别16，状态2，第3行
无法从OLE DB访问接口获取行"；BULK"；对于链接服务器"；(null("；。表定义为
CREATE TABLE [dbo].[tbl_eur_hy_indices] ( [dte] [datetime] NULL, [fixing] [nvarchar](50) NOT NULL, [price_type] [nvarchar](50) NOT NULL, [isin_cpi] [nvarchar](50) NOT NULL, [isin_tri] [nvarchar](50) NOT NULL, [bbg_ticker_cpi] [nvarchar](50) NULL, [bbg_ticker_tri] [nvarchar](50) NULL, [name] [nvarchar](100) NOT NULL, [cpi_today] [float] NOT NULL, [tri_today] [float] NOT NULL, [cpi_previous_eom] [float] NOT NULL, [tri_previous_eom] [float] NOT NULL, [cost_factor_tri] [nvarchar](50) NOT NULL, [cash] [nvarchar](50) NOT NULL, [cost_factor_cpi] [nvarchar](50) NOT NULL, [interest_on_cash] [nvarchar](50) NOT NULL, [simple_margin] [nvarchar](50) NULL, [discount_margin] [nvarchar](50) NULL, [duration] [float] NULL, [duration_to_maturity] [float] NULL, [portfolio_duration] [float] NULL, [portfolio_duration_to_maturity] [float] NULL, [annual_yield] [float] NULL, [annual_yield_to_maturity] [float] NULL, [annual_modified_duration] [float] NULL, [annual_modified_duration_to_maturity] [float] NULL, [annual_convexity] [float] NULL, [annual_convexity_to_maturity] [float] NULL, [annual_portfolio_yield] [float] NULL, [annual_portfolio_yield_to_maturity] [float] NULL, [annual_portfolio_modified_duration] [float] NULL, [annual_portfolio_modified_duration_to_maturity] [float] NULL, [annual_portfolio_convexity] [float] NULL, [annual_portfolio_convexity_to_maturity] [float] NULL, [semi_annual_yield] [float] NULL, [semi_annual_yield_to_maturity] [float] NULL, [semi_annual_modified_duration] [float] NULL, [semi_annual_modified_duration_to_maturity] [float] NULL, [semi_annual_convexity] [float] NULL, [semi_annual_convexity_to_maturity] [float] NULL, [semi_annual_portfolio_yield] [float] NULL, [semi_annual_portfolio_yield_to_maturity] [float] NULL, [semi_annual_portfolio_modified_duration] [float] NULL, [semi_annual_portfolio_modified_duration_to_maturity] [float] NULL, [semi_annual_portfolio_convexity] [float] NULL, [semi_annual_portfolio_convexity_to_maturity] [float] NULL, [oas] [float] NULL, [effective_oa_duration] [float] NULL, [oa_convexity] [float] NULL, [z_spread] [float] NULL, [z_spread_over_libor] [float] NULL, [expected_remaining_life] [float] NULL, [coupon] [float] NULL, [nominal_value] [float] NULL, [market_value] [float] NULL, [base_market_value] [float] NULL, [level_0] [nvarchar](50) NOT NULL, [level_1] [nvarchar](50) NOT NULL, [level_2] [nvarchar](50) NOT NULL, [level_3] [nvarchar](50) NOT NULL, [level_4] [nvarchar](50) NOT NULL, [level_5] [nvarchar](50) NOT NULL, [level_6] [nvarchar](50) NOT NULL, [level_7] [nvarchar](50) NOT NULL, [level_8] [nvarchar](50) NOT NULL, [markit_iboxx_rating] [nvarchar](50) NOT NULL, [seniority_level_1] [nvarchar](50) NULL, [seniority_level_2] [nvarchar](50) NULL, [seniority_level_3] [nvarchar](50) NOT NULL, [paid_cash] [nvarchar](50) NULL, [annual_index_benchmark_spread] [float] NULL, [semi_annual_index_benchmark_spread] [float] NULL, [annual_benchmark_spread_to_bm_curve] [nvarchar](1) NULL, [semi_annual_benchmark_spread_to_bm_curve] [nvarchar](1) NULL, [asset_swap_margin] [float] NULL, [dv_01] [float] NULL, [fx_version] [nvarchar](50) NOT NULL, [index_currency] [nvarchar](50) NOT NULL, [tax_consideration] [nvarchar](50) NOT NULL, [daily_return] [nvarchar](50) NOT NULL, [month_to_date_return] [nvarchar](50) NOT NULL, [quarter_to_date_return] [nvarchar](50) NOT NULL, [year_to_date_return] [nvarchar](50) NOT NULL, [1_3_years] [nvarchar](50) NOT NULL, [1_5_years] [nvarchar](50) NOT NULL, [1_10_years] [nvarchar](50) NOT NULL, [1_15_years] [nvarchar](50) NOT NULL, [1_20_years] [nvarchar](50) NOT NULL, [3_5_years] [nvarchar](50) NOT NULL, [5_7_years] [nvarchar](50) NOT NULL, [5_10_years] [nvarchar](50) NOT NULL, [5_15_years] [nvarchar](50) NOT NULL, [7_10_years] [nvarchar](50) NOT NULL, [10_15_years] [nvarchar](50) NOT NULL, [15_20_years] [nvarchar](50) NOT NULL, [15_25_years] [nvarchar](50) NOT NULL, [20_25_years] [nvarchar](50) NOT NULL, [25_30_years] [nvarchar](50) NOT NULL, [5_years] [nvarchar](50) NOT NULL, [7_years] [nvarchar](50) NOT NULL, [10_years] [nvarchar](50) NOT NULL, [15_years] [nvarchar](50) NOT NULL, [25_years] [nvarchar](50) NOT NULL, [30_years] [nvarchar](50) NOT NULL, [gross_price_index] [nvarchar](50) NOT NULL, [coupon_income_index] [nvarchar](50) NOT NULL, [redemption_income_index] [nvarchar](50) NOT NULL, [income_index] [nvarchar](50) NOT NULL, [number_of_bonds] [nvarchar](50) NOT NULL, [daily_sovereign_curve_swap_return] [float] NULL, [daily_libor_swap_return] [float] NULL, [month_to_date_sovereign_curve_swap_return] [float] NULL, [month_to_date_libor_swap_return] [float] NULL, [duration_weighted_exposure] [nvarchar](255) NULL, [filename] [nvarchar](200) NULL, [upload_datetime] [datetime] NULL ) ON [PRIMARY]视图为
CREATE VIEW [dbo].[vw_eur_hy_indices] AS SELECT [dte] ,[fixing] ,[price_type] ,[isin_cpi] ,[isin_tri] ,[bbg_ticker_cpi] ,[bbg_ticker_tri] ,[name] ,[cpi_today] ,[tri_today] ,[cpi_previous_eom] ,[tri_previous_eom] ,[cost_factor_tri] ,[cash] ,[cost_factor_cpi] ,[interest_on_cash] ,[simple_margin] ,[discount_margin] ,[duration] ,[duration_to_maturity] ,[portfolio_duration] ,[portfolio_duration_to_maturity] ,[annual_yield] ,[annual_yield_to_maturity] ,[annual_modified_duration] ,[annual_modified_duration_to_maturity] ,[annual_convexity] ,[annual_convexity_to_maturity] ,[annual_portfolio_yield] ,[annual_portfolio_yield_to_maturity] ,[annual_portfolio_modified_duration] ,[annual_portfolio_modified_duration_to_maturity] ,[annual_portfolio_convexity] ,[annual_portfolio_convexity_to_maturity] ,[semi_annual_yield] ,[semi_annual_yield_to_maturity] ,[semi_annual_modified_duration] ,[semi_annual_modified_duration_to_maturity] ,[semi_annual_convexity] ,[semi_annual_convexity_to_maturity] ,[semi_annual_portfolio_yield] ,[semi_annual_portfolio_yield_to_maturity] ,[semi_annual_portfolio_modified_duration] ,[semi_annual_portfolio_modified_duration_to_maturity] ,[semi_annual_portfolio_convexity] ,[semi_annual_portfolio_convexity_to_maturity] ,[oas] ,[effective_oa_duration] ,[oa_convexity] ,[z_spread] ,[z_spread_over_libor] ,[expected_remaining_life] ,[coupon] ,[nominal_value] ,[market_value] ,[base_market_value] ,[level_0] ,[level_1] ,[level_2] ,[level_3] ,[level_4] ,[level_5] ,[level_6] ,[level_7] ,[level_8] ,[markit_iboxx_rating] ,[seniority_level_1] ,[seniority_level_2] ,[seniority_level_3] ,[paid_cash] ,[annual_index_benchmark_spread] ,[semi_annual_index_benchmark_spread] ,[annual_benchmark_spread_to_bm_curve] ,[semi_annual_benchmark_spread_to_bm_curve] ,[asset_swap_margin] ,[dv_01] ,[fx_version] ,[index_currency] ,[tax_consideration] ,[daily_return] ,[month_to_date_return] ,[quarter_to_date_return] ,[year_to_date_return] ,[1_3_years] ,[1_5_years] ,[1_10_years] ,[1_15_years] ,[1_20_years] ,[3_5_years] ,[5_7_years] ,[5_10_years] ,[5_15_years] ,[7_10_years] ,[10_15_years] ,[15_20_years] ,[15_25_years] ,[20_25_years] ,[25_30_years] ,[5_years] ,[7_years] ,[10_years] ,[15_years] ,[25_years] ,[30_years] ,[gross_price_index] ,[coupon_income_index] ,[redemption_income_index] ,[income_index] ,[number_of_bonds] ,[daily_sovereign_curve_swap_return] ,[daily_libor_swap_return] ,[month_to_date_sovereign_curve_swap_return] ,[month_to_date_libor_swap_return] ,[duration_weighted_exposure] FROM [dbo].[tbl_eur_hy_indices]存储过程为：
ALTER PROCEDURE [dbo].[usp_import_eur_hy_indices] @Filepath varchar(500) ,@Pattern varchar(100) ,@TableName varchar(128) ,@ViewName varchar(128) ,@ResetTable bit = 0 AS SET QUOTED_IDENTIFIER OFF DECLARE @query varchar(1000) DECLARE @numfiles int DECLARE @filename varchar(100) DECLARE @files TABLE (Name varchar(200) NULL) --Delete the contents of the rawData table and let the user know IF @ResetTable = 1 BEGIN PRINT 'Emptying table [' + @TableName + ']...' EXEC ('DELETE ' + @TableName) END --Pull a list of the CSV file names from the folder that they're stored in SET @query = 'master.dbo.xp_cmdshell "dir ' + @filepath+@pattern + ' /b"' INSERT @files(Name) EXEC (@query) DECLARE curs_files CURSOR FOR SELECT Name FROM @files WHERE Name IS NOT NULL --For each CSV file, execute a query SET @numfiles =0 OPEN curs_files FETCH NEXT FROM curs_files INTO @filename WHILE (@@FETCH_STATUS = 0) BEGIN SET @numfiles+=1 --BULK INSERT each CSV file into the rawData view and update the rawData table with the file name and the upload datetime SET @query = ('BULK INSERT ' + @ViewName + ' FROM ''' + @Filepath+@filename + ''' WITH( CODEPAGE = ''65001'', DATAFILETYPE = ''char'', FIRSTROW = 2, FIELDTERMINATOR='','', ROWTERMINATOR=''0x0a'');' + ' UPDATE ' + @TableName + ' SET [filename] = ' + '''' + @filename + '''' + ' WHERE [filename] Is Null;' + ' UPDATE ' + @TableName + ' SET [upload_datetime] = ' + '''' + CAST(GETDATE() as nvarchar(1000)) + '''' + ' WHERE [upload_datetime] Is Null;' ) PRINT 'Importing [' + @filename + '] into [' + @TableName + ']...' EXEC (@query) FETCH NEXT FROM curs_files INTO @filename END CLOSE curs_files DEALLOCATE curs_filescsv的前10行在下面。行终止符为换行符"0x0a"，字段分隔符为"，">
Date,Fixing,Price Type,ISIN_CPi,ISIN_TRi,BBG_Ticker_CPi,BBG_Ticker_TRi,Name,CPi_Today,TRi_Today,CPi_previous_EOM,TRi_previous_EOM,Cost Factor_TRi,Cash,Cost Factor_CPi,Interest on Cash,Simple Margin,Discount Margin,Duration,Duration to Maturity,Portfolio Duration,Portfolio Duration to Maturity,Annual Yield,Annual Yield to Maturity,Annual Modified Duration,Annual Modified Duration to Maturity,Annual Convexity,Annual Convexity to Maturity,Annual Portfolio Yield,Annual Portfolio Yield to Maturity,Annual Portfolio Modified Duration,Annual Portfolio Modified Duration to Maturity,Annual Portfolio Convexity,Annual Portfolio Convexity to Maturity,Semi-Annual Yield,Semi-Annual Yield to Maturity,Semi-Annual Modified Duration,Semi-Annual Modified Duration to Maturity,Semi-Annual Convexity,Semi-Annual Convexity to Maturity,Semi-Annual Portfolio Yield,Semi-Annual Portfolio Yield to Maturity,Semi-Annual Portfolio Modified Duration,Semi-Annual Portfolio Modified Duration to Maturity,Semi-Annual Portfolio Convexity,Semi-Annual Portfolio Convexity to Maturity,OAS,Effective OA duration,OA Convexity,z-spread,Z-Spread Over Libor,Expected Remaining Life,Coupon,Nominal Value,Market Value,Base Market Value,Level 0,Level 1,Level 2,Level 3,Level 4,Level 5,Level 6,Level 7,Level 8,Markit iBoxx Rating,Seniority Level 1,Seniority Level 2,Seniority Level 3,Paid Cash,Annual Index Benchmark Spread,Semi-annual Index Benchmark Spread,Annual Benchmark Spread to BM-Curve,Semi-Annual Benchmark Spread to BM-Curve,Asset Swap Margin,DV 01,FX Version,Index Currency,Tax Consideration,Daily Return,Month-to-Date Return,Quarter-to-Date Return,Year-to-Date Return,1-3 Years,1-5 Years,1-10 Years,1-15 Years,1-20 Years,3-5 Years,5-7 Years,5-10 Years,5-15 Years,7-10 Years,10-15 Years,15-20 Years,15-25 Years,20-25 Years,25-30 Years,5+ Years,7+ Years,10+ Years,15+ Years,25+ Years,30+ Years,Gross Price Index,Coupon Income Index,Redemption Income Index,Income Index,Number of Bonds,Daily Sovereign Curve Swap Return,Daily Libor Swap Return,Month-to-date Sovereign Curve Swap Return,Month-to-date Libor Swap Return,Duration weighted exposure 2021-01-08,EU_EOD,Nominal,GB00B1KT9M89,GB00B1CTQK32,IBXXHCX2,IBXXHCX1,iBoxx EUR High Yield cum-crossover,122.688755979648,375.674735085899,122.050527013077,373.458235576939,1,0,1,0,,,3.45444819830822,4.14982765499078,3.44891477023608,4.1431803493945,2.71975655259125,2.9367983320229,3.36466570544599,4.0332980138925,19.0823044120007,24.4449677778724,2.7153999733654,2.93209408943486,3.35927609338667,4.02683736861076,19.0517378633501,24.4058111707503,2.68834134825912,2.90341547463045,3.40906583055825,4.09092863712993,17.901742287252,23.1253155079009,2.68403509075285,2.89876470560161,3.40360509718386,4.08437567756523,17.8730667947779,23.0882727552924,339.452364530924,3.42369130628568,-39.1163300238647,348.526987012627,321.479453488336,4.5280230759434,3.64076932223501,432143270500,448982825752.599,447049901445.438,EUR,Corporates,*,*,*,*,*,*,*,*,,,*,720346641.60765,342.399613775266,339.382417745424,,,312.095867966326,0.0360811693466207,Local,EUR,Gross,0.00111131363415717,0.00593506662274046,0.00593506662274046,0.00593506662274046,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,54.2184658868672,0.0317293577429768,0.0552585793774614,0.0869879371204382,782,0.00101358198814322,0.00115665711391235,0.0067345251237787,0.00626360056687547, 2021-01-08,EU_EOD,Nominal,GB00B1KT9N96,GB00B1CTQL49,IBXXHEX2,IBXXHEX1,iBoxx EUR High Yield ex-crossover,114.373650207173,364.293817733864,113.785271279766,362.149644301914,1,0,1,0,,,3.35257163246533,4.16175814591435,3.34657732377441,4.15431703331231,3.07707768591167,3.27287277236213,3.25429964569763,4.03184763890315,18.311658120494,24.4925942353031,3.07157595305182,3.2670209631081,3.24848104469887,4.02463880282391,18.2789173640091,24.448802129844,3.03894658115768,3.23348527902885,3.30285896572084,4.09605425953217,17.2258287015459,23.2484160104455,3.03351302569647,3.22770389356273,3.29695354195259,4.08873062372715,17.1950293791111,23.2068484624795,374.80019216287,3.31461270234828,-49.9747974918777,385.134014495595,358.127515053145,4.56327825098089,3.81475237813724,355613998500,367064889966.953,365558014443.218,EUR,Corporates,*,*,*,*,*,*,*,*,,,*,657477789.15265,378.032324350432,374.343524974372,,,345.775854709102,0.0347311781950243,Local,EUR,Gross,0.00117315090405512,0.00592068352319641,0.00592068352319641,0.00592068352319641,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,43.9751042628257,0.0239462360974358,0.0548209113981555,0.0787671474955913,674,0.00107282934452984,0.00121770470412718,0.00672051106773019,0.00623843862347351, 2021-01-08,EU_EOD,Nominal,GB00B1KTMR47,GB00B1CQNQ99,IEHZKTMR,IEHNCQNQ,iBoxx EUR High Yield Capped cum-crossover 10+,54.5375490059183,198.104393448492,54.3255996094595,197.181589253972,1,0,1,0,,,10.4901713936517,10.9207765753727,10.4901713936517,10.9207765753727,3.33173626296297,3.32950422367245,10.152130573038,10.5690747409849,173.154292841605,180.892779210843,3.33173626296297,3.32950422367245,10.152130573038,10.5690747409849,173.154292841605,180.892779210843,3.30394983247616,3.30178589925606,10.3197421898231,10.7434624991524,174.202757782898,181.972136946542,3.30394983247616,3.30178589925606,10.3197421898231,10.7434624991524,174.202757782898,181.972136946542,372.340371502719,10.3101987525551,162.094230918578,376.193319683536,348.67498467349,16.9601092778577,5.49193548387097,2635000000,3436419809.447,3420412377.41035,EUR,Corporates,*,*,*,*,*,*,*,*,,,*,0,367.528904130438,364.788824263827,,,400.001925742914,0.139057398692438,Local,EUR,Gross,0.00113413114081684,0.00467997138075171,0.00467997138075171,0.00467997138075171,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,67.5504936510861,0,0,0,3,-0.000317281851484679,0.000910890083274132,0.00909492847561366,0.0119987174109089, 2021-01-08,EU_EOD,Nominal,GB00B1KTMS53,GB00B1CQNP82,IEHZKTMS,IEHNCQNP,iBoxx EUR High Yield Capped cum-crossover 1-10,120.442231225853,377.30283288268,119.814492206631,375.073119398183,1,0,1,0,,,3.40018292259339,4.0976045359433,3.39469447799169,4.09099034017584,2.70519423175773,2.9287259042799,3.31231520184908,3.98288873594634,17.8939746980631,23.2383137656718,2.70082761119176,2.92399846747866,3.30696858994537,3.97645971001461,17.8650909317863,23.2008033726596,2.67369267918876,2.89522660724617,3.35576501874409,4.03961883226668,16.6962204858833,21.900158360232,2.66937690721832,2.8905532437228,3.35034827181576,4.03309823479365,16.6692700884251,21.8648079662488,338.676450470778,3.37057690497844,-40.6682311238834,347.874265899717,320.835872659402,4.45169580767204,3.62940401685189,429508270500,445546405943.152,443629489068.029,EUR,Corporates,*,*,*,*,*,*,*,*,,,*,720346641.60765,341.801651486208,338.777861357887,,,310.004105453849,0.0352869320916326,Local,EUR,Gross,0.00111113793491358,0.00594474348914886,0.00594474348914886,0.00594474348914886,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,51.7208927519195,0.0305011948784999,0.053119660093784,0.0836208549722839,779,0.00102388107814451,0.00115856334882811,0.00671632625955021,0.00621938244088893, 2021-01-08,EU_EOD,Nominal,GB00B1KTMT60,GB00B1CQNN68,IEHZKTMT,IEHNCQNN,iBoxx EUR High Yield Capped cum-crossover 5+,130.321758489433,436.495734864776,129.647920694138,433.942599673784,1,0,1,0,,,4.74494527754415,5.8337514984905,4.74396939927502,5.83255168880211,2.5864216704344,2.82340554734993,4.62600640972736,5.67451641599486,32.9020624157439,42.751669201282,2.58588972906198,2.82282486623404,4.62505499324855,5.67334935569505,32.8952955499861,42.7428765972577,2.56609600775713,2.79946509254889,4.68501160720455,5.75346326349844,31.439906906408,41.1125771102824,2.5655682467011,2.79888933519255,4.68404805530865,5.75227996644386,31.4334407576669,41.104121613276,323.281775806835,4.76087733018983,-49.7443700195032,333.249971286634,303.615379514612,6.5387809097932,3.54276085288725,157793471000,166163439849.033,165225504038.693,EUR,Corporates,*,*,*,*,*,*,*,*,,,*,34181352.455,325.998809305485,324.082224943165,,,300.616537501538,0.0508317284285612,Local,EUR,Gross,0.000991904379388231,0.00588357813432316,0.00588357813432316,0.00588357813432316,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,93.3253380574262,0.0191978829760696,0,0.0191978829760696,263,0.000892557309840591,0.00102745323628433,0.00671826839926282,0.00673835742961026, 2021-01-08,EU_EOD,Nominal,GB00B1KTMW99,GB00B1CQNM51,,,iBoxx EUR High Yield Capped cum-crossover 5-10,133.977636468576,448.947615779626,133.281342093393,446.310358371987,1,0,1,0,,,4.62361933652746,5.72632524720721,4.6226483338511,5.72512266612602,2.55071193114792,2.8030229627168,4.5093073972361,5.57115461915007,29.9402583905899,39.8344471761974,2.55017625813259,2.80243430205929,4.5083604011206,5.56998462533097,29.9339706603698,39.8260815751381,2.53074373104555,2.77923465758691,4.56601907732047,5.64808597447566,28.4250843607376,38.1379476870093,2.53021225153628,2.77865099269273,4.56506017122944,5.64689982436287,28.4191148310292,38.1299383664793,320.98157570742,4.64368844502394,-54.2179132853485,331.236495506282,301.496055953189,6.36179927242034,3.5096587275599,155158471000,162727020039.586,161805091661.283,EUR,Corporates,*,*,*,*,*,*,*,*,,,*,34181352.455,324.009006779668,322.13187798424,,,295.854754573145,0.0489686055069789,Local,EUR,Gross,0.00098890194418999,0.00590902128568027,0.00590902128568027,0.00590902128568027,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,94.3425619302346,0.0198169693027043,0,0.0198169693027043,260,0.000918225549439511,0.00102993071846384,0.00666802796826982,0.00662715820710758, 2021-01-08,EU_EOD,Nominal,GB00B1KTMX07,GB00B1CQNH09,IEHUKTMX,IEHNCQNH,iBoxx EUR High Yield Capped cum-crossover 1-5,119.259198853489,343.783167049926,118.636604682551,341.744574259032,1,0,1,0,,,2.69624899242322,3.16048041705031,2.68972329138587,3.15283114192994,2.85761751089283,3.05977041848396,2.62359659670937,3.06904128741107,10.9628520610005,13.6893243092167,2.85070125144958,3.05236489063765,2.61724673544624,3.06161332138523,10.9363187937585,13.6561921919322,2.81473632845403,3.0161474743768,2.6594158777057,3.11414809748293,9.94773306976338,12.5573508976699,2.80792385385317,3.00884752665029,2.65297932344063,3.10661095995323,9.9236566835709,12.5269584828708,356.59802656698,2.63806118146205,-32.872091571517,364.725194327564,340.420751820953,3.37016428530146,3.69720573933174,274349799500,282819385903.567,281824397406.745,EUR,Corporates,*,*,*,*,*,*,*,*,,,*,686165289.15265,359.357146451981,355.201978297006,,,323.96486633297,0.0274148481574949,Local,EUR,Gross,0.00118132739397514,0.00596525283631588,0.00596525283631588,0.00596525283631588,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,35.7406312404901,0.0288849035238741,0.0578276126329609,0.086712516156835,519,0.00108454705372707,0.00123247784771872,0.00674405597180683,0.00598526433103919, 2021-01-08,EU_EOD,Nominal,GB00B1KTMY14,GB00B1CQNL45,,,iBoxx EUR High Yield Capped cum-crossover 7-10,149.665008876674,500.146340112832,148.779321978934,496.87337172777,1,0,1,0,,,5.99553153838748,7.06245276473613,5.99553153838748,7.06245276473613,2.50081111654366,2.63604641347712,5.84974901899678,6.88166850402487,46.5843698441546,58.5830082607,2.50081111654366,2.63604641347712,5.84974901899678,6.88166850402487,46.5843698441546,58.5830082607,2.48321113827053,2.61666825965841,5.9221292110512,6.97139850177188,44.7986640249893,56.6604134903248,2.48321113827053,2.61666825965841,5.9221292110512,6.97139850177188,44.7986640249893,56.6604134903248,311.661922658089,6.06412521443129,-48.332055335298,320.071708800391,288.883492879454,7.96929156220228,3.29379649813373,44476113000,46925585842.2916,46618503801.308,EUR,Corporates,*,*,*,*,*,*,*,*,,,*,0,314.492678161897,312.837016317403,,,286.895873234519,0.0645391381949309,Local,EUR,Gross,0.00114754280142582,0.00658712776996051,0.00658712776996051,0.00658712776996051,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,119.135626395357,0,0,0,67,0.00101647649883609,0.00113662405909579,0.00737718524489495,0.0080243547803962, 2021-01-08,EU_EOD,Nominal,GB00B1KTQK57,GB00B1CQQJ38,IEHUKTN0,IEHNCQNK,iBoxx EUR High Yield Capped Energy cum-crossover 5-7,369.320187556911,833.351263025133,366.059529455024,825.546722701596,1,0,1,0,,,4.82744175096773,5.000718381173,4.82744175096773,5.000718381173,2.10332668584449,2.1330384490561,4.72811338263585,4.89640266905831,28.4929202029868,30.3757706538588,2.10332668584449,2.1330384490561,4.72811338263585,4.89640266905831,28.4929202029868,30.3757706538588,2.09175550158252,2.12114298682928,4.77750471734478,4.94827004934737,26.7200243458836,28.5685765982595,2.09175550158252,2.12114298682928,4.77750471734478,4.94827004934737,26.7200243458836,28.5685765982595,282.548718458616,4.86403991740211,17.0917423994373,284.87468413518,256.42979418156,5.42444824927957,3.24301075268817,6510000000,6967614711.5622,6902361279.5606,EUR,Corporates,Non-Financials,Energy,*,*,*,*,*,*,,,*,0,282.610276170282,281.584308569032,,,258.869936037151,0.0522391650351495,Local,EUR,Gross,0.000883440813008063,0.00945378390940355,0.00945378390940355,0.00945378390940355,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,248.185374864463,0,0,0,9,0.000855259598383011,0.000971047051461869,0.0102399296481475,0.00996434402159574, 2021-01-08,EU_EOD,Nominal,GB00B1KTQM71,GB00B1CQQH14,IEHUKTQM,IEHNCQQH,iBoxx EUR High Yield Capped Energy cum-crossover 3-5,161.219759109027,365.599164341714,160.077876561523,362.781265167013,1,0,1,0,,,3.21534702255882,3.72248721242423,3.21237581231339,3.71904736843027,1.79450024945601,2.02297118667983,3.15931429052933,3.64943732414798,14.4763688159055,17.8041149240089,1.79284200308658,2.0211018168501,3.15639485852939,3.64606498346702,14.4629916174132,17.7876626504901,1.78115407902083,2.00742205987485,3.18712841633795,3.68568779104986,13.1472139777927,16.3345611896968,1.77950816546589,2.0055670585486,3.18418328209965,3.68228195234897,13.1350650132811,16.3194668887641,253.693803177955,3.25552519333661,-0.998637478608128,261.015131727145,235.763686799445,4.01306386224404,3.79757606852054,13062828000,14190322815.8992,14093973047.404,EUR,Corporates,Non-Financials,Energy,*,*,*,*,*,*,,,*,13125000,253.316112565561,252.118411536292,,,232.783142866128,0.0355796079861918,Local,EUR,Gross,0.00142191953266102,0.00776748814028094,0.00776748814028094,0.00776748814028094,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,21,0.00133822741572676,0.00147211238710031,0.00858815469319651,0.00780085631978156,
解决方案是从我正在读取的CSV创建一个表。然后，我将CSV行红色化为字典，并将行字典附加到列表中。然后，我使用SQL炼金术来批量插入我的行dictonaries会话列表。bulk_insert_mapings：
import pyodbc
import sqlalchemy as db
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session,sessionmaker,mapper
from sqlalchemy import MetaData,create_engine
from datetime import datetime
import urllib
import os
import glob
import chardet
import re
import csv
def rtn_date(dtStr):
if dtStr =='':
return None
else:
try:
# rtnDate =datetime.strptime(dtStr,'%d/%m/%Y')
rtnDate =datetime.strftime(dtStr,'%F')
return rtnDate
except:
return None
log_file =r'XXXXXX.txt'
database = 'XXXXXXX'
driver_name ='ODBC Driver 11 for SQL Server'
engine = create_engine('mssql+pyodbc:{0}?driver={1}?Trusted_Connection=yes'.format(database,driver_name))
connection= engine.connect()
Base = automap_base()
Base.prepare(engine, reflect=True)
date_test_table = Base.classes.XXXXXXXXX
Session = sessionmaker(bind=engine)
session = Session()
#define folder where all csv files are
folder =r'XXXXXXX'
os.chdir(folder)
extension ='csv'
skipped =[]
all_filenames =[i for i in glob.glob('*.{}'.format(extension))]
for i in range(len(all_filenames)):
try:
with open(all_filenames[i], 'r') as csv_file:
csv_reader = csv.reader(csv_file)
buffer = []
firstline= True
for row in csv_reader :
if firstline: #skip first line
firstline = False
continue
if len(re.findall(r'd+',str(all_filenames[i]))) > 0:
strType = 'Normal'
else:
strType = 'MonthEnd'
buffer.append({
'Date': row[0],
'ISIN': row[1],
'CUSIP': row[2],
'Ticker': row[3],
'Issuer': row[4],
'Coupon': row[5],
'Final_Maturity': row[6],
'Workout_date': row[7],
'Expected_Remaining_Life': row[8],
'Time_to_Maturity': row[9],
'Coupon_Frequency': row[10],
'Notional_Amount_Unconstrained': row[11],
'Notional_Amount_Constrained': row[12],
'PIK_Original_Amount_Issued': row[13],
'PIK_Factor': row[14],
'Redemption_Factor': row[15],
'Bid_Price': row[16],
'Ask_Price': row[17],
'Accrued_Interest': row[18],
'Coupon_Payment': row[19],
'Coupon_Adjustment': row[20],
'Ex_Dividend_Flag': row[21],
'Dirty_Price': row[22],
'Market_Value_Unconstrained': row[23],
'Market_Value_Constrained': row[24],
'Cash_Payment_Unconstrained': row[25],
'Cash_Payment_Constrained': row[26],
'Street_Yield_to_Maturity': row[27],
'Annual_Yield_to_Maturity': row[28],
'Semi_Annual_Yield_to_Maturity': row[29],
'Street_Yield_to_Worst': row[30],
'Annual_Yield_to_Worst': row[31],
'Semi_Annual_Yield_to_Worst': row[32],
'OAS_Street_Yield': row[33],
'OAS_Annual_Yield': row[34],
'OAS_Semi_Annual_Yield': row[35],
'Annual_Benchmark_Spread': row[36],
'Semi_Annual_Benchmark_Spread': row[37],
'Z_Spread': row[38],
'OAS_Spread': row[39],
'Asset_Swap_Margin': row[40],
'Simple_Margin': row[41],
'Discount_Margin': row[42],
'Duration_to_Maturity': row[43],
'Street_Modified_Duration_to_Maturity': row[44],
'Annual_Modified_Duration_to_Maturity': row[45],
'Semi_Annual_Modified_Duration_to_Maturity': row[46],
'Duration_to_Worst': row[47],
'Street_Modified_Duration_to_Worst': row[48],
'Annual_Modified_Duration_to_Worst': row[49],
'Semi_Annual_Modified_Duration_to_Worst': row[50],
'OAS_Duration': row[51],
'OAS_Modified_Duration': row[52],
'OAS_Annual_Modified_Duration': row[53],
'OAS_Semi_Annual_Modified_Duration': row[54],
'Spread_Duration': row[55],
'Z_Spread_Duration': row[56],
'Street_Convexity_to_Maturity': row[57],
'Annual_Convexity_to_Maturity': row[58],
'Semi_Annual_Convexity_to_Maturity': row[59],
'Street_Convexity_to_Worst': row[60],
'Annual_Convexity_to_Worst': row[61],
'Semi_Annual_Convexity_to_Worst': row[62],
'OAS_Convexity': row[63],
'Benchmark_ISIN': row[64],
'Daily_Return': row[65],
'Month_to_Date_Return': row[66],
'Quarter_to_Date_Return': row[67],
'Year_to_Date_Return': row[68],
'Daily_Excess_Return': row[69],
'Month_to_date_Excess_Return': row[70],
'Level_0': row[71],
'Level_1': row[72],
'Level_2': row[73],
'Level_3': row[74],
'Level_4': row[75],
'Level_5': row[76],
'Debt': row[77],
'Rating': row[78],
'Is_Callable': row[79],
'Is_Core_index': row[80],
'Is_Crossover': row[81],
'Is_Fixed_to_Float': row[82],
'Is_FRN': row[83],
'Is_Hybrid': row[84],
'Is_Perpetual': row[85],
'Is_PIK': row[86],
'Is_Sinking': row[87],
'Is_Zero_Coupon': row[88],
'_1_3_years': row[89],
'_1_5_years': row[90],
'_1_10_years': row[91],
'_3_5_years': row[92],
'_5_7_years': row[93],
'_5_10_years': row[94],
'_7_10_years': row[95],
'_5_years': row[96],
'_10_years': row[97],
'Source':str(all_filenames[i]),
'File_Type': strType
})
session.bulk_insert_mappings(date_test_table,buffer)
session.commit()
except Exception as e:
print(e)
skipped.append('{0} {1}'.format(str(all_filenames[i]),r'n'))
print('processed {0} out of {1} files'.format(str(i+1), str(len(all_filenames)+1)))
session.close()
if len(skipped)>0:
open(log_file, 'w').close()
skip_log = open(log_file,'w')
skip_log.writelines(skipped)
skip_log.close()