| Author |
 Topic  |
|
|
badua
Starting Member
Vietnam
8 Posts |
 Posted - 01/21/2013 : 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
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 01/21/2013 : 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
India
47189 Posts |
Posted - 01/21/2013 : 07:19:23
|
what do you mean by that? did you see if all the 400 columns got mapped correctly?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mikebird
Aged Yak Warrior
United Kingdom
518 Posts |
Posted - 01/21/2013 : 09:20:35
|
I handled 904-column batches which came with binary flags for each column to include or not |
|
|
|
badua
Starting Member
Vietnam
8 Posts |
Posted - 01/27/2013 : 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
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 01/27/2013 : 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
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 01/27/2013 : 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
India
47189 Posts |
Posted - 01/28/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Import a 400-columns Excel data file to SQL 2008
