Post reply

Import formatted excel spreadsheet in SQL server 2008 database?

  • October 17, 2009 at 11:47 pm

    #385601

    Hi All,

    I have planned to create an SSIS package and automate the process of importing excel spreadsheet data into SQL database but excel spreadsheet is a form that is used in the department. Since there are lot of entries in that form with empty rows and zeros, how could I get rid of those empty rows, rows filled with zeros so that data imported is clean in the database?

    Any suggestion and advice is really appreciated.

    Thank you all in advance for your time and effort for this post.

  • October 17, 2009 at 11:54 pm

    #1067383

    You can use the Data Flow complonents inside of SSIS to clean up your data or ignore invalid data.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • October 19, 2009 at 4:57 am

    #1067569

    There are quirks with Excel, particularly that the Excel data source takes the data type of the columns from the first eight rows so if you have a text column but the first few entries look like numbers it will be treated as numbers and fail when it reaches genuine text.

    If you can't easily alter your spreadsheets and they are single sheet, save them from excel as csv and import using a flat file data source which is far more manoeuvrable than the excel one. Once in the data can be cleaned within the dataflow.

  • October 19, 2009 at 7:38 am

    #1067620

    Thanks a ton,

    John and P. Jones, this has opened a new window for solving the issue.

    Thanks again,

    Harry

  • October 19, 2009 at 8:30 am

    #1067668

    There are quirks with Excel, particularly that the Excel data source takes the data type of the columns from the first eight rows

    When using OPENROWSET to read the EXCEL work book use the setting for IMEX = 1 that solves the problem given in the quote above.

    SELECT * FROM OPENROWSET('Microsoft.jet.OLEDB.4.0','Excel 8.0;DATABASE=c:\temp\items.xls;IMEX=1'

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

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

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