SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Import a 400-columns Excel data file to SQL 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

badua
Starting Member

Vietnam
8 Posts

Posted - 01/21/2013 :  07:14:09  Show Profile  Reply with Quote
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

3712 Posts

Posted - 01/21/2013 :  07:19:18  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/21/2013 :  07:19:23  Show Profile  Reply with Quote
what do you mean by that? did you see if all the 400 columns got mapped correctly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 01/21/2013 :  09:20:35  Show Profile  Reply with Quote

I handled 904-column batches which came with binary flags for each column to include or not
Go to Top of Page

badua
Starting Member

Vietnam
8 Posts

Posted - 01/27/2013 :  11:44:23  Show Profile  Reply with Quote
@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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/27/2013 :  16:56:54  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/27/2013 :  18:44:34  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/28/2013 :  00:06:45  Show Profile  Reply with Quote
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/

Go to Top of Page

wade1982
Starting Member

USA
1 Posts

Posted - 02/11/2014 :  13:24:26  Show Profile  Reply with Quote
try the approach here, it worked for me.

http://waheedrous.wordpress.com/2014/01/14/ssis-importing-an-excel-file-with-over-255-columns/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000