Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Importing and Splitting 150,000 columns flat file into three tables of 50,000 cloumns each Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 12:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:41 AM
Points: 21, Visits: 155
Hi guys,

I need help. I have a task to split a flat file with 150,000 columns into 3 50,000 columns excel spreadsheets. For this my solution is to import the flat file into SQL Server and then have 3 tables of 50,000 columns and export the result set to excel spreadsheets. But the SSIS flat file connection is taking in only 30 columns. How I can work around this.
Thanks.
Post #1360942
Posted Tuesday, September 18, 2012 12:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 23,290, Visits: 32,017
SQL Server only allows 1,024 columns per table.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1360947
Posted Tuesday, September 18, 2012 12:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:05 AM
Points: 122, Visits: 292
Even excel wont allow that many columns .. are you sure you are talking abt coln and not rows ?

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
Post #1360951
Posted Tuesday, September 18, 2012 12:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:41 AM
Points: 21, Visits: 155
uh oooh. Any suggestions on how to Import this flat file into Excel?
Post #1360953
Posted Tuesday, September 18, 2012 12:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
just to pile on the bad news; Excel 2010 is limited to a max of 16384 columns.

so you need a few more(10+?) worksheets or so.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1360955
Posted Tuesday, September 18, 2012 12:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:41 AM
Points: 21, Visits: 155
Yes, the I am talking about columns. I have to help the user get the data split into multiple(3 in this case) spread sheets like a horizontal split from the flat file.
Post #1360956
Posted Tuesday, September 18, 2012 12:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 23,290, Visits: 32,017
Only thing that comes to my mind is a custom written application. Even then it may not doable due to restrictions in Excel and the number of columns it can have.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1360957
Posted Tuesday, September 18, 2012 12:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:41 AM
Points: 21, Visits: 155
Thanks guys. I will keep you all posted.
Post #1360958
Posted Tuesday, September 18, 2012 1:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
Since u already have a file, its better to use a command line utility like sygwin etc to do this work for you. no sense trying to import it into sql table only to export it back as an excel sheet.

I would probably use cygwin to split the files into the number of columns required and then simply change the format to csv in order to opn it in excel.
You cud try another database like filemaker
http://help.filemaker.com/app/answers/detail/a_id/7541/~/technical-specifications-of-filemaker-pro-11-and-filemaker-pro-11-advanced
which support upto 256 mil columns per file.
Its also pretty easy to import data and export data from it , but the upload takes a long time to complete


Jayanth Kurup
Post #1360969
Posted Tuesday, September 18, 2012 1:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 7,139, Visits: 15,190
Lynn Pettis (9/18/2012)
SQL Server only allows 1,024 columns per table.


For what it's worth - as of 2008 it's 1024 of non-sparse columns. With sparse columns your column limit per table can be increased to 30,000. See the section on "wide tables" in the BOL entry below.

http://msdn.microsoft.com/en-us/library/ms186986(v=SQL.105).aspx

This still won't help this particular situation unfortunately.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1360976
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse