| Author |
Topic |
|
badua
Starting Member
8 Posts |
 Posted - 2013-01-21 : 07:14:09
|
| I am using the Import data wizard to import data from an Excel 2010 file, which has 400 columns, into a table in Ms SQL Server 2008 but it does not import enough the number of columns:(Any one could give me a solution? Thanks in advance |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-21 : 07:19:18
|
| If you choose Excel 97-2003 as Excel version, the maximum number of columns is 256. If you have the option in the data source dialog of the Import/Export Wizard to choose Excel 2007 as the Excel version, select that, and you should be able to import up to 16,000 columns. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-21 : 07:19:23
|
| what do you mean by that? did you see if all the 400 columns got mapped correctly?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2013-01-21 : 09:20:35
|
| I handled 904-column batches which came with binary flags for each column to include or not |
|
|
|
badua
Starting Member
8 Posts |
Posted - 2013-01-27 : 11:44:23
|
| @James K: It only imports 256 columns when I choose "Excel 97-2003" or "Excel 2007".So I have to save the Excel data file as a Text delimited file, then choose "Flat file". It's successful!@mikebird: I would be happy if you could share a little bit in detail how to use flags to handle the importation. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 16:56:54
|
| I have not tried to import something that wide recently, but will give it a try next time I am on a computer that has SQL Server installed. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 18:44:34
|
| I tried this, and what you found is what I am finding as well - with Excel 2007 or Excel 97-2003, the wizard limits you to 255 columns. Not sure if you used BIDS and did a full-fledged SSIS package, that would allow you to pick up more than 255 columns or not. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-28 : 00:06:45
|
| one work around would be to select 256 columns in data flow. Then follow it up with update logic to update rest of columns by means of PKcol value------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
wade1982
Starting Member
1 Post |
Posted - 2014-02-11 : 13:24:26
|
| try the approach here, it worked for me.[url]http://waheedrous.wordpress.com/2014/01/14/ssis-importing-an-excel-file-with-over-255-columns/[/url] |
|
|
|
|
Import a 400-columns Excel data file to SQL 2008