Importing more than 255 columns data from Excel/Text To SQL

Question

I have a data Excel 2010 file of statistics which I intend to import into a single table. The problem is that the file has 1500 columns. By default, SQL server import restricts the table created after import to 255 columns. Is there any way to extend this to include all 1500 columns?

SQL Server can't have more than 1024 columns per table unless you use sparse columns. — Dan Guzman, Dec 02 '17 at 19:47

score 0 · Answer 1 · answered Dec 02 '17 at 19:57

0

You can use Wide tables by creating sparse columns. Columns per wide table size is 30000. You can look at this page; Maximum Capacity Specifications for SQL Server

You can create wide table like this;

CREATE TABLE SampleWideTable(ID INT IDENTITY(1,1),
Column1 NVARCHAR(100) SPARSE,
Column2 NVARCHAR(100) SPARSE,
Column3 NVARCHAR(100) SPARSE)

But there are some advantages and disadvanteages for sparsed columns. You should read it first before taking action.

SPARSE columns advantages and disadvantages

answered Dec 02 '17 at 19:57

lucky

12,734
4
24
46

Thanks @Rainman Can these tables be created dynamically on import? – J.Smith Dec 02 '17 at 21:32
You can use DTF to perform it. There is a useful article here about importing data and creating table dynamically. https://stackoverflow.com/questions/28738805/dynamic-table-create-and-load-data-in-ssis – lucky Dec 02 '17 at 22:15

score 0 · Answer 2 · answered Aug 19 '20 at 00:34

See this answer regarding importing 255 columns at a time using the range syntax:

myCommand = New OleDbDataAdapter("SELECT * FROM  [" & _TableName & "AAO1:ABG3000]", conn)
myCommand.Fill(thisDS, _TableName)

Then programmatically append each of the datasets of columns back together.

Importing more than 255 columns data from Excel/Text To SQL

2 Answers2