Post reply

SQL Server import export wizard vs bulk insert

  • August 20, 2013 at 8:42 pm

    #297126

    Hi Professionals.

    I have a question regarding importing data from spreadsheets.

    I manually use SQL Servers Import and Export wizard to import a .xls spreadsheet and it is intelligent enough to match the columns correctly. But when I do this using a bulk insert I run into problems for instance

    when I have a column named softwaremanufacturer and I import data that reads "Adobe systems, Inc" the Import wizards populates the column like so

    softwaremanufacturer

    Adobe Systems, Inc

    but when I do a bulk insert of my csv I run into the following problems as it comes to the comma and thinks it is a new table column shifting the "Inc" part into the next column along like so

    computername, softwaremanufacturer,productname, productversion

    "ACTADMINPC002 g00228""Adobe Systems Inc" "Acrobat Reader (redistributable)"

    "ACTADMINPC002 g00228""Adobe Systems Inc" "Air"

    "ACTADMINPC002 g00228""Adobe Systems Inc" "Collaboration synchronizer"

    "ACTADMINPC002 g00228""Adobe Systems Inc" "EULA"

    "ACTADMINPC002 g00228""Adobe Systems Inc" "Flash player"

    Is there a way to get around this

    hope this makes sense and thanks in advance

  • August 20, 2013 at 10:34 pm

    #1643284

    Have you tried creating a format file for the bulk insert?

    http://technet.microsoft.com/en-us/library/ms188365.aspx

    Mike

    How to Post Performance Problems[/url]

    How to Post Best Practices[/url]

  • August 20, 2013 at 11:31 pm

    #1643296

    ive looked into that and I have no idea what to do

  • August 20, 2013 at 11:56 pm

    #1643306

    Oracle765 (8/20/2013)

    Hi Professionals.

    I have a question regarding importing data from spreadsheets.

    I manually use SQL Servers Import and Export wizard to import a .xls spreadsheet and it is intelligent enough to match the columns correctly. But when I do this using a bulk insert I run into problems for instance

    when I have a column named softwaremanufacturer and I import data that reads "Adobe systems, Inc" the Import wizards populates the column like so

    softwaremanufacturer

    Adobe Systems, Inc

    but when I do a bulk insert of my csv I run into the following problems as it comes to the comma and thinks it is a new table column shifting the "Inc" part into the next column along like so

    computername, softwaremanufacturer,productname, productversion

    "ACTADMINPC002 g00228""Adobe Systems Inc" "Acrobat Reader (redistributable)"

    "ACTADMINPC002 g00228""Adobe Systems Inc" "Air"

    "ACTADMINPC002 g00228""Adobe Systems Inc" "Collaboration synchronizer"

    "ACTADMINPC002 g00228""Adobe Systems Inc" "EULA"

    "ACTADMINPC002 g00228""Adobe Systems Inc" "Flash player"

    Is there a way to get around this

    hope this makes sense and thanks in advance

    The problem seems to be that BULK INSERT is not properly interpreting the column delimiters (commas in your case) that are embedded within your data. If it was working for you then why can't you continue to use SSIS (the technology underlying the Import/Export Wizard) to continue to import xls files? At the end of the Wizard you can save the resulting package and then schedule that to run using SQL Agent.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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