sql server - Parse fixed length data from text into database -

-

i have text file data need read database. data inside file follows: 

00000a0011nike running shoes00000a0012nike store mo city12345b0001emc truck          12345b0002bh78545789785     12345b0003nh170323032015060012345c0011steve jones shoe's 12345c0012company, tel17545812345c001312,fax:66234544-4812345d0001mrs. mary wilson, off 19 45781b0001emc truck         45781b0002bh78545789785     45781b0003nh1703230320150600 45781c0011steve jones shoe's45781c0012company, tel17545845781c001312,fax:66234544-48 45781d0001mrs. mary wilson, off 19

each line same length 3 sections in it. each section has different kind of data, here interpretation of data: 

transactionid char(5)   record char(2)   record counter number(3)       - a0  data fields       011 sender information  char(18)       012 sender location     char(18)     - b0  data fields       001 vehicle type        char(18)       002 destination         char(05)       002 package weight      number(05)       002 package data        number(03)       002 filler              char(05)     - c0 data fields       011 receiver address    char(18)       012 receiver address    char(18)       013 receiver address    char(18)     - d0 data fields       001 contact person      char(18)

i have above information saved in database, lode on run time , user upload file, trying read line, , read each block , try extract data it.
of now, kind of no able separate transactions, each transaction must separate row in table. also, of data split across multiple blocks, i.e. "c0" field types.

any appreciated. thanks!

sql isn't best place have string processing doesn't difficult. don't think can in set based manner looping on each line on input should doable. here's pseudo code should started;

@transactionid = substring(@input, 1, 5) @currentinputposition = 6  while (@currentinputposition < len(@input) begin     @record = substring(@input, @currentinputposition, 2)      if (@record = 'a0')     begin         @senderinformation = substring(@input, @currentinputposition + 2, 18)         @senderlocation = substring(@input, @currentinputposition + 2 + 18, 18)         @currentinputposition + @currentinputposition + 2 + 18 + 18          -- save a0 record data      end     else if (@record = 'b0')     begin         @vehicletype = substring(@input, @currentinputposition + 2, 18)         @destination = substring(@input, @currentinputposition + 2 + 18, 5)         @package weight = substring(@input, @currentinputposition + 2 + 18 + 5, 5)         @package data = substring(@input, @currentinputposition + 2 + 18 + 5 + 5, 3)         @filler = substring(@input, @currentinputposition + 2 + 18 + 5 + 5 + 3, 5)         @currentinputposition + @currentinputposition + 2 + 18 + 5 + 5 + 3 + 5          -- save b0 record data      end     else if (@record = 'c0')     begin          @receiveraddress1 = (@input, @currentinputposition + 2, 18)    char(18)         @receiveraddress2 = (@input, @currentinputposition + 2 + 18, 18)    char(18)         @receiveraddress3 = (@input, @currentinputposition + 2 + 18 + 18, 18)    char(18)         @currentinputposition + @currentinputposition + 2 + 18 + 18 + 18          -- save c0 record data      end     else if (@record = 'd0')     begin         @contactperson = substring(@input, @currentinputposition + 2, 18)         @currentinputposition + @currentinputposition + 2 + 18          -- save d0 record data      end end

note - it's pseudo code, have flesh out cursor/loop, declares etc. don't add comment , tell me doesn't run ;)


Comments

Post a Comment

Popular posts from this blog

node.js - Mongoose: Cast to ObjectId failed for value on newly created object after setting the value -

-
this question has answer here: what's mongoose error cast objectid failed value xxx @ path “_id”? 7 answers even though has been asked many times because entity has no valid objectid field. i came across situation objectid set value can casted objectid still error. the schema: var serviceschema = parammongoose.schema({ servicename:{ type: parammongoose.schema.types.objectid , ref: 'servicenames',required:true } }); pseudo code: servicefromdb=new service({servicename:'some name'}); servicefromdb.servicename='000000000000000000000001'; servicefromdb.save(function(paramerror,paramdata){ if(paramerror){ console.log('but but...',servicefromdb,paramerror) } }); the output of code is: but but... { servicename: 000000000000000000000001, _id: 55079a90286f49280364f78b } { [casterror: cast objectid faile...
Read more

[C++][SFML 2.2] Strange Performance Issues - Moving Mouse Lowers CPU Usage -

-
i've been working on making 2d map editor past 2 weeks or so, , i've run strange problem. trying optimize code, example, making functions should've been functions begin with, did made cpu usage go through roof. i've tried commenting out different sections of code thought culprits (scrolling, rendering, other calculations) no avail. think issue might trying call things within different functions, sake of making modular possible, i'd functionality. the other major change i've made since file i'm comparing i've ported variables external cpp file, since having variables global makes lot easier use within different functions. simple issue, can't life of me understand why happens. with original code, around 2-3% cpu usage. new code, around 20-35%, but, if move mouse around, it'll drop around 6-8%. tl;dr: after trying optimize code using functions, i've somehow killed performance, , found strange quirk moving mouse reduces cpu usage.. /...
Read more

ios - Possible to get UIButton sizeThatFits to work? -

-
i have been using method of resizing uibuttons depreciated, , not robust. , other reasons, want sizethatfits work uibuttons. i've read online, i'm not sure if should work (seems working some, not others, difference maybe between style, i'm using custom). here simple test code recreate issue (i put in viewdidload test, shouldn't matter, , real code part of large project): uibutton *btn = [uibutton buttonwithtype:uibuttontypecustom]; [btn settitle:@"this test long title need word wrap more single line when displayed on tiny ipod in portrait view." forstate:uicontrolstatenormal]; btn.titlelabel.linebreakmode = uilinebreakmodewordwrap; // depreciated - nothing replace it? cgrect r = btn.frame; r.origin.x = 0; r.origin.y = 0; r.size.width = 320; r.size.height = [btn.titlelabel.text sizewithfont:btn.titlelabel.font constrainedtosize:cgsizemake(r.size.width,100000) linebreakmode:btn.titlelabel.linebreakmode].height; // returns approx 86 , changes correctly ...
Read more