Professional Web Applications Themes

Import from exel to sql server - Microsoft SQL / MS SQL Server

Hi I've got exel xls file with a lot of data and i have to import it to Sqlserver. I've used DTS and ok I have a table with data but there is one column PASS/LOGIN and in that column i have both passwords and logins separeted with sign "/", i need separate them and export to another table where there is on column for login and one for password. How to do it? I need to do as automaticly as possible becouse i have a lot of huge xls files. Import from exel is easy but how to separate ...

  1. #1

    Default Import from exel to sql server

    Hi

    I've got exel xls file with a lot of data and i have to import it to
    Sqlserver. I've used DTS and ok I have a table with data but there is one
    column
    PASS/LOGIN and in that column i have both passwords and logins separeted
    with sign "/", i need separate them and export to another table where there
    is on column for login and one for password.
    How to do it?
    I need to do as automaticly as possible becouse i have a lot of huge xls
    files. Import from exel is easy but how to separate logins and passwords and
    export them automaticly to another .
    Please describe what to do step by step becouse i am beginer.

    thanks
    bodzio



    bo Guest

  2. #2

    Default Re: Import from exel to sql server

    Couple of methods in DTS

    1. use a lookup so for each row in the source you will pass the password
    and login and it will be inserted into a different table

    How to Use Lookups in DTS
    http://www.sqldts.com/default.aspx?6,107,277,7,1

    2. Use more than 1 datapump

    Pump 1 - Put the excel spreadsheet into a scratch table
    Pump 2 - take all info except Login/Password to the relevant table
    Pump 3 - take the Login/Password info to another table

    You can easily split the two values up by using

    Substring and Charindex

    --


    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    www.SQLDTS.com
    I support PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org
    Allan Guest

  3. #3

    Default Re: Import from exel to sql server

    You can use an ActiveX script to achieve this. As an example:
    [A] Consider an XL sheet that has these 2 columns
    =====
    User Name, Information
    Kurt, password/kurtj
    Martin, password/martin
    =====
    [B] You can create a stub table like this:
    =====
    CREATE TABLE testTable
    (
    UserName VARCHAR(100),
    Information VARCHAR(100),
    Password VARCHAR(100),
    Login VARCHAR(100)
    )
    =====
    [C] In the DTS designer, you can create an XL source to the above XLS and a
    SQL Server destination.
    [D] Define a transform data task between these two.
    [E] Delete all existing transformations and create a new one (type ActiveX)
    between all the source and the destination columns (you can do this by
    choosing the Select All button)
    [F] Define the following ActiveX script
    =====
    Function Main()
    DTSDestination("UserName") = DTSSource("User Name")
    DTSDestination("Information") = DTSSource("Information")

    ' Search for the seperator
    strSeperator = InStr (DTSSource ("Information"), "/")

    ' If the seperator exists, split the input
    If (strSeperator > 0) Then
    DTSDestination("Password") = Left(DTSSource("Information"),
    strSeperator - 1)
    DTSDestination("Login") = Right(DTSSource("Information"),
    Len(DTSSource("Information")) - strSeperator)
    End If

    Main = DTSTransformStat_OK
    End Function
    =====
    [G] Run the transformation and you should see the input split and stored
    accordingly.

    I've not tested this extensively for performance, but for an XL sheet of
    around 100 or so records, it runs within seconds.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "bo" <pl> wrote in message news:bi5109$ntg$news.tpi.pl... 
    there 
    and 


    SriSamp Guest

  4. #4

    Default Re: Import from exel to sql server

    SriSamp.

    The poster as I recall wanted the Login Password info to go to a different
    table not the same one

    --


    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    www.SQLDTS.com
    I support PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org
    Allan Guest

  5. #5

    Default Re: Import from exel to sql server

    Whoops, I'm sorry, my mistake.
    Bo, please refer to the other posts for the solution, although I guess you
    can extend whatever I posted :-) You need to add your intermediate table as
    the source and not the XL.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Allan Mitchell" <sqldts.com> wrote in message
    news:%phx.gbl... 


    SriSamp Guest

  6. #6

    Default Re: Import from exel to sql server


    Yes i wanted the Login Password info to go to a different
    table, now i know what to do.
    Thank you all for your help.

    bo

    "SriSamp" <co.in> wrote in message
    news:phx.gbl... 
    as [/ref]
    different 
    >
    >[/ref]


    bo Guest

Similar Threads

  1. Export/Import Server Configuration
    By Ann in forum Coldfusion Server Administration
    Replies: 2
    Last Post: October 22nd, 06:31 PM
  2. Export to Exel
    By GGJ@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 2
    Last Post: August 25th, 03:27 PM
  3. Exel converts to multiple files (Acrobat 6.0.0)
    By Matthew_J_Lemin@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 8
    Last Post: June 3rd, 09:19 AM
  4. import into excel file from SQL-Server
    By peteyjr in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 11th, 03:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139