Post reply

Import Excel with dynamic cols in SQL server without using SSIS

  • May 13, 2009 at 3:23 am

    #204925

    Hi,

    I want to import excel into sql server without using ssis. excel may contain a fixed set of mandatory cols and a set of non mandatory cols.Col names of non mandatory column may vary. I want to import all mandatory cols and available non mandatory cols into sql server table.

    Already have ssis solution but need to look for option coz ssis does not allow to change the source and destination mappings runtime.

  • May 13, 2009 at 7:19 am

    #993679

    This could be done dynamically in SSIS if you break away from the standard data flow model.

    First questions though

    Is this excel 2003 or 2007

    How many rows of data are there?

  • May 13, 2009 at 9:56 pm

    #994253

    thanks for reply. excel version is 2003. and data rows will be somewhere around 500 to 30000. can u let me know solution via ssis pls

  • May 14, 2009 at 2:04 am

    #994313

    moggie_davee (5/13/2009)

    thanks for reply. excel version is 2003. and data rows will be somewhere around 500 to 30000. can u let me know solution via ssis pls

    I can show you how to get to the solution...

    The solution needs the following steps

    1. Convert the Excel sheet to a CSV file

    2. Create a Format File for BCP

    3. Create a new table in the database to take the data

    4. Set the connection properties for a BCP task

    5. Run the BCP task

    Step 1.

    There is a script here: http://www.devasp.net/net/articles/display/141.html

    Which will convert excel to CSV.

    If you have a look round, I'm sure there must be methods which use the Excel object to save it straight to CSV with out having to manually take every value out and append to a file.

    Make sure that you delimit the file with a unique character.

    Step 2.

    To create the format file you'll need to understand how format files are structured. Link to that here: http://msdn.microsoft.com/en-us/library/aa173859(SQL.80).aspx

    You can alter the script used in Step 1 to create the format file as the CSV file is being created.

    Step 3.

    You need a create table script, this too can be created at the same time as the Excel to CSV conversion.

    For steps 2 and 3 I recommend that you treat EVERYTHING as a varchar. It makes creating the format file and the create table script a lot easier.

    Save the create table script to a variable and then use an Execute SQL Task to run it.

    Step 4.

    Use a script task to dynamically set the BCP properties. You'll need to set

    - The Destination table (assuming that the destination server and database are hardcoded into the task)

    - The source file, this being the CSV file you created

    - The format file, this being the format file you created in step 2

    Step 5.

    Execute the BCP task

    One thing to note - the BCP task requires that the service account which the SQL Server Database Service runs under has access to the CSV file. Just because you can see it doesn't mean the database can

    There is a blog and an example about dynamically loading CSV files here:

    http://www.proteanit.com/b/2008/02/20/dynamic-table-loading-in-ssis/%5B/url%5D

    Finally - good luck, unless your an SSIS whizz it'll take you a few days to get it working 😀

    I can attest that it is possible, I created something similar a couple of months ago

  • July 10, 2009 at 10:54 am

    #1023215

    i need something similar to this but the files to be imported will be in different folders with a unique 3 character name that is stored in a table. the user wants to be able to select the 3 char name from a drop down and when he clicks execute the files under the folder with the same 3 char name will be imported into the table with the same 3 char name. any help would be greatly appreciated?

    thanks,

    carminenatale@gmail.com

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply