Nightly Excel Spreadheet import into mssql database

Question

I am currently working with a nightly import that I need to create, but am not sure what the best route would be to update/insert into the current table. This is all done in MS SQL Server 2012 and pulling the Excel file from another server. I am trying to figure out how I can loop through the columns and pull out the data I need. If I could rearrange the data, I would but am currently stuck with what I have.

In my current table tblHW I have Columns such as PmpCount, , NumberStages, Pmpmodel_pmp1, serialnum_Pmp1, pmpModel_pmp2, Pmpmodel_pmp2, serialnum_pmp2, partnum_motor1, serialnumberMotor1, etc…. I apologize in advance for not being able to post a real table or a 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 I want to move into tblHW comes from the tbl Pull_Down. Here is the 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. I am receiving this information in the form of five excel spreadhsheets each with over 400 columns. The columns giving me the biggest headache are the 20 part columns that I need to place into the SQL table.

I need to somehow move each row into the tblHW but need to do something like this:

The first row AN 21-919G needs to have SN1 to be inserted into sn_mtr1 since it is a BOD, SN2 into SN_pmp1 since it is a PMP, and SN3 into sn_pmp2 since it is the second PMP here. I also need to get the pmp count, in this case 2 and then add the attribute1_7 and attribute2_7 to put into numstages when the prts are PMP.

Answer 1

I think SSIS is really what you want to use here. To just do a very basic import of Excel into SQL Server this is a great link to use - Simple Talk 10 Simple Steps

From there, you will then need to use a combination of Conditional Splits as well as MultiCast in order to move the data to different fields/locations depending on your various rules. Lastly for your PMP count, it seems like using the RowCount task should cover what you are looking to do with that.

There are dozens of ways to do these things even if you decide to go about this using SSIS. You can load your data into Staging tables so that everything is an insert and then you run Execute SQL Tasks to do all of the individual updates. Or you could use that MultiCast/Conditional Split approach and then use SQL Server Destinations or you could use OLE DB Commands in order to get the information either loaded or update individual records based on the data that you have available to you within the data flow. There are certainly performance issues to deal with when using those OLE DB Commands, since they are working each thing row by row, but they are fairly clear to understand within the Data flow task.

You will have to work through what works best for you. I have tried to include links of what I feel are good examples to show you how to do these kinds of things (Excel import, Multicast, Conditional Split, RowCount and OLE DB Command), so hopefully between that this will get you where you need to go.