Importing and Splitting 150,000 columns flat file into three tables of 50,000 cloumns each

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Importing and Splitting 150,000 columns flat...

14 posts, Page 1 of 2

1 2 »»

Importing and Splitting 150,000 columns flat file into three tables of 50,000 cloumns each

Rate Topic

Display Mode

Topic Options

Author

Message

LameAss2DataBadAss

Posted Tuesday, September 18, 2012 12:36 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:08 PM
Points: 16, Visits: 128

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

Lynn Pettis

Posted Tuesday, September 18, 2012 12:40 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 21,630, Visits: 27,486

SQL Server only allows 1,024 columns per table.

Cool

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

SQL_By_Chance

Posted Tuesday, September 18, 2012 12:44 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 01, 2013 1:11 PM
Points: 104, Visits: 247

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

LameAss2DataBadAss

Posted Tuesday, September 18, 2012 12:46 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:08 PM
Points: 16, Visits: 128

uh oooh. Any suggestions on how to Import this flat file into Excel?

Post #1360953

Lowell

Posted Tuesday, September 18, 2012 12:48 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 11,648, Visits: 27,762

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

LameAss2DataBadAss

Posted Tuesday, September 18, 2012 12:48 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:08 PM
Points: 16, Visits: 128

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

Lynn Pettis

Posted Tuesday, September 18, 2012 12:48 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 21,630, Visits: 27,486

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1360957

LameAss2DataBadAss

Posted Tuesday, September 18, 2012 12:50 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:08 PM
Points: 16, Visits: 128

Thanks guys. I will keep you all posted.

Post #1360958

Jayanth_Kurup

Posted Tuesday, September 18, 2012 1:00 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008

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

Matt Miller (#4)

Posted Tuesday, September 18, 2012 1:11 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:57 PM
Points: 6,998, Visits: 13,949

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions