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

Question

Post reply

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

DataEnthusiast

Right there with Babe

Points: 732
More actions
September 18, 2012 at 12:36 pm

#393204

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.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

September 18, 2012 at 12:40 pm

#1539230

SQL Server only allows 1,024 columns per table.

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 2

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.”

DataEnthusiast Right there with Babe Points: 732 More actions · Answer 3

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

Lowell SSC Guru Points: 323504 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DataEnthusiast Right there with Babe Points: 732 More actions · Answer 5

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

DataEnthusiast Right there with Babe Points: 732 More actions · Answer 7

DataEnthusiast

Right there with Babe

Points: 732

September 18, 2012 at 12:50 pm

#1539241

Thanks guys. I will keep you all posted.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 8

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[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

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?

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 10

I don't know your structure, but on a guess, I'd assume that you're actually looking at denormalized data. Can't you normalize the structure and then import the information into that structure?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

aaron.reese SSChampion Points: 13494 More actions · Answer 11

aaron.reese

SSChampion

Points: 13494

September 19, 2012 at 5:33 am

#1539497

if you have 150K columns, how many rows?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 12

aaron.reese (9/19/2012)
if you have 150K columns, how many rows?

Wouldn't be amazing if it is just one row? This would push the limits of the infamous single-table database to something beyond my wildest imagination, the single-row database 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

DataEnthusiast Right there with Babe Points: 732 More actions · Answer 13

I met with the business and re-organized their requirement. All good. Thank you all for responding.