the csv file comma delimited, contains embedded delimiters , quotes. of fields have beginning , ending quote , not.
the first record handled perfectly, not second record. can see field appears have leading quote, embedded. field5 not have trailing quote. result of import puts blanks in fields 5 , 6 , pushes field5 data (in bold) field7 which, later on in process, causes max field length violation.
is there attribute setting in filehelpers use handle record containing field in bold letters below record imports each field properly? csv file received external source cannot control feed.
at2m-2471-3,,"1178",accutemp,48"" solid cutting board (must ordered at2a-2630-3 or at2a-2630-22),,ea,"10.00","0.00000","207.00","93.41","0.00","0.00","0.00","0.00",atcut,"","1",each,"cutting board, equipment mounted",accutemp,"","false",,85,"",,"0","baab3369-bcad-453e-9867-921e4af1203c","",accutemp,,"","e0fb1dfb-c00d-dd11-a23a-00304834a8c9","bcd6e7a0-be0d-dd11-a23a-00304834a8c9"
at2m-2877-1,,"1178",accutemp,""u"" channel connecting 2 29"" depth griddles,,ea,"4.00","0.00000","104.00","46.93","0.00","0.00","0.00","0.00",at2m,"","1",each,,accutemp,"","false",,85,"",,"0","f7d56cb1-b2ab-40c7-b7e5-55ee1b4d1023","",accutemp,,"","e3fb1dfb-c00d-dd11-a23a-00304834a8c9","bcd6e7a0-be0d-dd11-a23a-00304834a8c9"
here sql table structure, no indexes:
create table [dbo].[rawdata]( [model number] [varchar](50) null, [user stock model number] [varchar](50) null, [vendor number] [varchar](50) null, [vendor name] [varchar](50) null, [specification] [varchar](max) null, [vendor pack] [varchar](50) null, [selling unit] [varchar](50) null, [weight] [varchar](50) null, [cube] [varchar](50) null, [list price] [varchar](50) null, [net price] [varchar](50) null, [height] [varchar](50) null, [width] [varchar](50) null, [depth] [varchar](50) null, [deal net] [varchar](50) null, [picture name] [varchar](150) null, [blank column] [varchar](50) null, [vendor stock] [varchar](50) null, [priced by] [varchar](50) null, [category] [varchar](75) null, [vendor nickname] [varchar](50) null, [user vendor name] [varchar](50) null, [configurable?] [varchar](50) null, [category values] [varchar](max) null, [freight class] [varchar](50) null, [vendor fob] [varchar](50) null, [ship zip] [varchar](50) null, [model apply] [varchar](50) null, [picture link] [varchar](50) null, [category code] [varchar](50) null, [vendor short name] [varchar](50) null, [cutsheet name] [varchar](150) null, [cutsheet link] [varchar](50) null, [product id] [varchar](50) null, [vendor id] [varchar](50) null ) on [primary] textimage_on [primary] i created class table attributes: [delimitedrecord(",")] [ignorefirst(1)]
class rawdata { [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string model_number; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string user_stock_model_number; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string vendor_number; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string vendor_name; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string specification; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string vendor_pack; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string selling_unit; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string weight; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string cube; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string list_price; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string net_price; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string height; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string width; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string depth; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string deal_net; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string picture_name; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string blank_column; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string vendor_to_stock; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string priced_by; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string category; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string vendor_nickname; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string user_vendor_name; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string configurable; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string category_values; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string freight_class; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string vendor_fob; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string ship_from_zip; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string model_apply; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string picture_link; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string category_code; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string vendor_short_name; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string cutsheet_name; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string cutsheet_link; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string product_id; [fieldquoted('"', quotemode.optionalforboth)] // optional quoted when read or write public string vendor_id; }
here c# code:
using system; using system.collections.generic; using system.linq; using system.text; using system.text.regularexpressions; using system.threading.tasks; using system.data; using system.data.sqlclient; using system.windows.forms; using filehelpers; namespace xyz { class class1 { static void main(string[] args) { sqlconnection conn1 = new sqlconnection(); datatable temp_rawdata_table = new datatable(); conn1.connectionstring = "data source=abc;initial catalog=xyz;integrated security=true"; system.diagnostics.stopwatch elapsed = new system.diagnostics.stopwatch(); elapsed.start(); int64 rows = 0; // ================ begin bulkcopy ======================== using (sqlbulkcopy bulkcopy = new sqlbulkcopy(conn1.connectionstring, system.data.sqlclient.sqlbulkcopyoptions.tablelock) { destinationtablename = "rawdata", bulkcopytimeout = 0, batchsize = 100000 }) { temp_rawdata_table = new xyzdataset.rawdatadatatable(); // using async engine allows processing record record filehelperasyncengine engine = new filehelperasyncengine(typeof(rawdata)); engine.beginreadfile("c:\\rawdata.csv"); int batchsize = 0; console.writeline("copying data table."); // async engines ienumerable foreach (rawdata aqtext in engine) { //create new update row aq360productsraw table datarow rawdata_update_row = temp_rawdata_table.newrow(); rawdata_update_row["model number"] = aqtext.model_number.trim(); rawdata_update_row["user stock model number"] = aqtext.user_stock_model_number.trim(); rawdata_update_row["vendor number"] = aqtext.vendor_number.trim(); rawdata_update_row["vendor name"] = aqtext.vendor_name.trim(); rawdata_update_row["specification"] = aqtext.specification.trim(); rawdata_update_row["vendor pack"] = aqtext.vendor_pack.trim(); rawdata_update_row["selling unit"] = aqtext.selling_unit.trim(); rawdata_update_row["weight"] = aqtext.weight.trim(); rawdata_update_row["cube"] = aqtext.cube.trim(); rawdata_update_row["list price"] = aqtext.list_price.trim(); rawdata_update_row["net price"] = aqtext.net_price.trim(); rawdata_update_row["height"] = aqtext.height.trim(); rawdata_update_row["width"] = aqtext.width.trim(); rawdata_update_row["depth"] = aqtext.depth.trim(); rawdata_update_row["deal net"] = aqtext.deal_net.trim(); rawdata_update_row["picture name"] = aqtext.picture_name.trim(); rawdata_update_row["blank column"] = aqtext.blank_column.trim(); rawdata_update_row["vendor stock"] = aqtext.vendor_to_stock.trim(); rawdata_update_row["priced by"] = aqtext.priced_by.trim(); rawdata_update_row["category"] = aqtext.category.trim(); rawdata_update_row["vendor nickname"] = aqtext.vendor_nickname.trim(); rawdata_update_row["user vendor name"] = aqtext.user_vendor_name.trim(); rawdata_update_row["configurable?"] = aqtext.configurable.trim(); rawdata_update_row["category values"] = aqtext.category_values.trim(); rawdata_update_row["freight class"] = aqtext.freight_class.trim(); rawdata_update_row["vendor fob"] = aqtext.vendor_fob.trim(); rawdata_update_row["ship zip"] = aqtext.ship_from_zip.trim(); rawdata_update_row["model apply"] = aqtext.model_apply.trim(); rawdata_update_row["picture link"] = aqtext.picture_link.trim(); rawdata_update_row["category code"] = aqtext.category_code.trim(); rawdata_update_row["vendor short name"] = aqtext.vendor_short_name.trim(); rawdata_update_row["cutsheet name"] = aqtext.cutsheet_name.trim(); rawdata_update_row["cutsheet link"] = aqtext.cutsheet_link.trim(); rawdata_update_row["product id"] = aqtext.product_id.trim(); rawdata_update_row["vendor id"] = aqtext.vendor_id.trim(); temp_rawdata_table.rows.add(rawdata_update_row); batchsize += 1; if (batchsize == 100000) { bulkcopy.writetoserver(temp_rawdata_table); temp_rawdata_table.rows.clear(); batchsize = 0; console.writeline("flushing 100,000 rows"); } rows += 1; console.writeline(rows.tostring() + " " + aqtext.model_number.trim() + environment.newline); } bulkcopy.writetoserver(temp_rawdata_table); temp_rawdata_table.rows.clear(); engine.close(); } elapsed.stop(); console.writeline((rows + " records imported in " + elapsed.elapsed.totalseconds + " seconds.")); } } }
the problem, mentioned @marcosmeli, invalid csv file. , not 1 field. row think working isn't working. seems whoever created csv file did backwards in terms fields should text-qualified (i.e. "quoted") , don't need it. have numeric fields being text-qualified , text field non-qualified.
the reason why row 1 worked text-qualification looks @ first , last characters of field. in row 1, escaped-quote (i.e. double-double-quotes) not first character, got imported, suspect, duplicate double-quotes. yet in row 2, beginning text of field quoted, hence first character quote, , escaped duplicating double-quotes. sloppily done, , getting filehelpers work not give confidence in continuing work properly, if non-text-qualified text fields ever have commas in them. in case again cause unexpected shifts in fields. know said csv file comes external source , cannot control it, need attempt fixed wrong. bug in whatever system producing , needs fixed.
for moment can set of text fields non-text-qualified. need add step replace double double-quotes single double-quote.
data format issues aside, , without taking away filehelpers interesting , useful library, don't need filehelpers in order read text file, row row (minimal memory footprint) , batch sql server. in fact, of plus:
- skip step of having separate staging table (i.e.
[rawdata]) , instead send rows directly synchronization stored procedure - do basic datatype validation in app layer , send strongly-typed row of data (rather passing in
varchar/nvarcharfields).
how so? using table-valued parameters and passing in using ienumerable<sqldatarecord> method (and not datatable method). have detailed technique in few answers here:
Comments
Post a Comment