Post reply

Importing spreadsheet in one single column

  • February 13, 2015 at 1:53 pm

    #314587

    I am trying to import 1 spreadsheet to a database table. I am using SSMS export import wizard to import spreadsheet. My goal is to import whole spreadsheet in one single column , can I do that? Please any help will be greatluy appreciated.

  • February 13, 2015 at 2:05 pm

    #1777613

    Well you could use a little excel magic to concat all the data into one column then import that column.

  • February 13, 2015 at 2:34 pm

    #1777616

    SSMS_2007 (2/13/2015)

    I am trying to import 1 spreadsheet to a database table. I am using SSMS export import wizard to import spreadsheet. My goal is to import whole spreadsheet in one single column , can I do that? Please any help will be greatluy appreciated.

    I'm not sure you can do that from the import wizard, but it can be done in the dataflow by using a Derived Column transformation to concatenate the columns together.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • February 13, 2015 at 5:56 pm

    #1777642

    SSMS_2007 (2/13/2015)

    I am trying to import 1 spreadsheet to a database table. I am using SSMS export import wizard to import spreadsheet. My goal is to import whole spreadsheet in one single column , can I do that? Please any help will be greatluy appreciated.

    Do you mean that you want to store the whole spreadsheet file .xls file as it is in the operating system?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 14, 2015 at 4:43 am

    #1777655

    I am pretty confused by your requirement too. Can you, please, maybe describe the scenario? Otherwise, I would agree with ZZartin on his solution. It could get fairly tedious if there is a lot of columns.

    Petr

  • February 17, 2015 at 4:01 pm

    #1778210

    If you're using Excel 2007 or later the workbooks are a collection of xml files under the hood. "In theory" you could import those xml files into a SQL Server column of XML datatype. However you would likely need to use something other than the SSMS import/export wizard.

    To go this route you would need to hit these high level steps:

    Make a copy of the Excel file you're working with.

    Change the file extension to .zip.

    Extract the zip contents.

    Figure out which xml bits you're interested in.

    Import those to SQL Server.

    Clean up your copied and extracted zip and xml files.

    BIG Disclaimer: The effort of doing this may be more trouble than it's worth. What is it you're trying to achieve by importing a spreadsheet into a single column?

    P.S. It's possible that an Excel workbook/spreadsheet could exceed the max size of a single SQL Server XML record.

    P.P.S. There may be APIs out there you could use that would eliminate the need to mess with the zip extraction step to access the XML, not sure though, would need additional research.

  • February 17, 2015 at 5:08 pm

    #1778220

    I'm not saying I recommend this or anything, but something like this is likely to work.

    DECLARE @ImportedXML TABLE

    (

    [FileName] NVARCHAR(100)

    ,Document VARBINARY(MAX)

    );

    DECLARE @FileName NVARCHAR(200) = 'YourExcelFile.xlsx'

    ,@SQL NVARCHAR(MAX)

    ,@SQLParms NVARCHAR(MAX) = N'@FileName NVARCHAR(200)';

    SELECT @SQL =

    N'SELECT @FileName ' + CHAR(10) +

    N' ,* FROM OPENROWSET(BULK N''' + @Filename + ''', SINGLE_BLOB) AS Document';

    -- Retrieve the file

    INSERT INTO @ImportedXML ([FileName], Document)

    EXEC sp_executesql @SQL, @SQLParms, @Filename=@Filename;

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • February 18, 2015 at 12:29 am

    #1778264

    Heh... the OP has left the building! 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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