Importing .xlsx with more than 255 columns to SSMS

Brian Healy 0 Reputation points
2024-03-07T20:46:56.2866667+00:00

I am currently attempting to import an .xlsx spreadsheet to a SQL database using the SQL Server Import and Export Wizard, however my source spreadsheet contains more than 255 columns.

I have attempted to convert the spreadsheet to a .csv and importing that as the 255 column limitation is not applied to .csv imports, however the default datatypes set are not working for me. I'd like to avoid manually changing the datatypes/lengths for every column if possible. Does anyone have any suggestions to either:

A] Work around the 255 column limitation via the import

B] Automatically set datatypes/lengths for destination fields when importing the .csv

Thank you in advance for any assistance and/or suggestions!

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,951 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,949 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 112.6K Reputation points MVP
    2024-03-07T22:18:19.8333333+00:00

    Both Greg and I don't shy for typing CREATE TABLE statements. But, yes, for 255+ columns, it can be a bit daunting.

    I assume that the Excel file has header row. Copy this row into a query window. Press Ctrl-H and click the .* button to replace regular expressions. The enter \t for Find and then for Replace enter nvarchar(200),\n. Slap a CREATE TABLE #Temp around this.

    Then run:

    BULK INSERT #Temp FROM 'C:\temp\YourCSVfile.csv'
    WITH (FORMAT = "CSV", FIELDTERMINATOR=",", FIRSTROW = 2)
    

    Once you have the data in a table, you can check the columns and use ALTER TABLE ALTER COLUMN to change data types etc.

    The advantage with using BULK INSERT is that it saves from you producing a format file.

    1 person found this answer helpful.

  2. Greg Low 1,770 Reputation points Microsoft Regional Director
    2024-03-07T21:33:46.36+00:00

    Instead of using the wizard, try using OPENROWSET (as a T-SQL) command instead. https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16

    Rough concept is like:

    SELECT * FROM OPENROWSET(BULK N'C:\SomePath\SomeFile.csv', FORMATFILE = N'C:\SomePath\SomeFormatFile.fmt', FIRSTROW = 2, FORMAT = 'CSV') AS myrows;
    
    -- or to insert into a table
    
    INSERT TableName (<ColumnList>)
    SELECT <ColumnList> 
    FROM OPENROWSET(BULK N'C:\SomePath\SomeFile.csv', FORMATFILE = N'C:\SomePath\SomeFormatFile.fmt', FIRSTROW = 2, FORMAT = 'CSV') AS myrows;
    

    Format files are pretty simple and basically spell out things like column and row delimiters. That should let you have many columns.

    Another option would be to use no column delimiter (or one that doesn't exist in the data), and only a row delimiter, so that you read every row as a single string, and then use the STRING_SPLIT function to break it apart. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16

    Yet another option is that once you have created a format file, you could also use the bcp command line utility as well, if the table already exists.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.