sql server - Nightly Excel Spreadheet import into mssql database -

-

i working nightly import need create, not sure best route update/insert current table. done in ms sql server 2012 , pulling excel file server. trying figure out how can loop through columns , pull out data need. if rearrange data, stuck have.

in current table tblhw have columns such pmpcount, , numberstages, pmpmodel_pmp1, serialnum_pmp1, pmpmodel_pmp2, pmpmodel_pmp2, serialnum_pmp2, partnum_motor1, serialnumbermotor1, etc…. apologize in advance not being able post real table or picture.

example:

|name      | pmpcount| numstages| pmpmodel_pmp1| stages_pmp1| sn_pmp1| |an 91-23g |        4|       500|        fx2347|         250|  354197| |br dn 895r|        5|       521|         d2442|          45|  875164| |aln 1-60j |        5|       521|        h21342|          95|  594126|  |pmpmodel_pmp2| stages_pmp2| sn_pmp2| partnum_mtr1| sn_mtr1| |fx2347       |         250|  354198|         null|    null| |fx17500      |         143|  102547|        m7544| 4512241| |fx17500      |         143|  458790|        m7544| 4512364|

the information want move tblhw comes tbl pull_down. here setup:

|name      | run_id               | part1| sn1     |    attribute1_7| |an 21-919g| oct 08, 2013 / 100845|   bod|       na|           3rd u| |fr 55-013a| oct 17, 2013 / 100853|   pmp| 2ea3a022|              78| |fr 55-013a| oct 01, 2014 / 101383|   cbl|      n/a|        redalead| |fr 43-223j| apr 03, 2013 / 100594|   bod|       na|           3rd u| |vh 204    | may 17, 2014 / 101145|   bod|    3rd u|    |part2| sn2      | attribute2_7| part3   | sn3     | attribute3_7| |pmp  |  2ea3f379|           78|      pmp| 2ea3n380|          117| |pmp  |  2ea3c020|          117|      pmp| 2ea3y021|          117| |mle  | j14312161|          120|      bod|      n/a|        3rd u| |other|        na|          pmp| 2ea2x774|       78| |bod  |      null|          pmp| 2ea4f075|       38|

a bit more information. receiving information in form of 5 excel spreadhsheets each on 400 columns. columns giving me biggest headache 20 part columns need place sql table.

i need somehow move each row tblhw need this:

the first row 21-919g needs have sn1 inserted sn_mtr1 since bod, sn2 sn_pmp1 since pmp, , sn3 sn_pmp2 since second pmp here. need pmp count, in case 2 , add attribute1_7 , attribute2_7 put numstages when prts pmp.

situations whole purpose ssis exist: integration services!

first 1 question why data need in excel, , if there more direct route, 1 exploit, linked server (if source rdbms).

based in information provide, make following assumptions: a) have no control on source output , must import data excel. b) files have consistent columns (probably created automated process).

in ssis can create source connection excel file. if excel file name dynamic, can create script modify connection string connection before importing data. set destination connection sql server. last step creating data flow task can map source destination columns.

example: enter image description here


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more