• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Need help from Excel geeks!

I

Ilmater

Diamond Member
I have a large data source that has 1.5M rows and it needs to be sent to someone in Excel '03 format (so, 65k row limitation). I have to SEND it in Excel, but I can use Access or text files if it helps in any way. How can I split the data up automatically between tabs?

Anyone who can help me with this gets a :cookie:!!!
 
Don't know of any way to do it automatically. I assume its in a comma delimited file or something?

Download TextPad, copy 65k lines, then another 65k - so on. Or do it in Access. Only way I know of to do it unfortunetely.
 
Originally posted by: Tweak155
Uh excel 07 removed the cap. 65k was in 2k3.

EDIT:
How is the data stored currently?
Click to expand...
My bad, I meant 2k3. I have '07, but the people I'm sending the data to do not.
 
You could write code to do it. Or, do it by manual labor. It'll only take a few minutes. I'll assume it is one giant column, in column A.

1) Go to cell C50000. Type something, anything into cell C50000.

2) Go to cell C1. Type '=A1'

3) Go to cell D1. Type '=A50001'

4) Go to cell E1. Type '=A100001'

5) Go to cell F1. Type '=A150001'

6) Repeat as needed, you'll only have to do it 30 times or so.

7) Highlight the row from C1 until the cell you ended (lets say that was cell AD1). Press Copy.

8) Press CTRL-SHIFT-DOWN ARROW.

9) Press Paste.

10) Select this new block and copy it. Your computer will hang for a minute or two.

11) Paste SPECIAL the cell VALUES into a new workbook, not the formulas.
 
Extract all the data into one file.
Split the file by line using "split" Make each file 65K lines
Then Import each of your 65K line files into excel.

split -l 65000 input outputprefix
 
Originally posted by: BurnItDwn
Extract all the data into one file.
Split the file by line using "split" Make each file 65K lines
Then Import each of your 65K line files into excel.

split -l 65000 input outputprefix
Click to expand...
OK, you'll have to explain this more. Is this a function that's native to Windows, or is that some script I'd have to write. I do not write code, so I would have no idea what I'm doing there.
 
Originally posted by: Sasiki
You could have already divided it up by now. It's only like 24 sheets.
Click to expand...
Try pasting about 12 columns of data x 65k rows. While waiting for a response, I imported the text file to access and now I'm cutting and pasting it into Excel, and every time I paste, it takes about 5 minutes. That is NOT quick.
 
Originally posted by: dullard
You could write code to do it. Or, do it by manual labor. It'll only take a few minutes. I'll assume it is one giant column, in column A.

1) Go to cell C50000. Type something, anything into cell C50000.

2) Go to cell C1. Type '=A1'

3) Go to cell D1. Type '=A50001'

4) Go to cell E1. Type '=A100001'

5) Go to cell F1. Type '=A150001'

6) Repeat as needed, you'll only have to do it 30 times or so.

7) Highlight the row from C1 until the cell you ended (lets say that was cell AD1). Press Copy.

8) Press CTRL-SHIFT-DOWN ARROW.

9) Press Paste.

10) Select this new block and copy it. Your computer will hang for a minute or two.

11) Paste SPECIAL the cell VALUES into a new workbook, not the formulas.
Click to expand...

Easiest way.
 
just create an access 03 database and send that over with the data or export in a tab delimited csv and have him import in access 03
 
I just ended up doing "Make Table" queries in Access with queries that would come out to 65k apiece. Then I exported them as excel files. Thanks for all the help, I just don't think there was much better I could have done.
 
Wow, you really shouldn't be using excel for something like that... what's the point? Put everything in a text file and write a real program to read it.
 
Originally posted by: Ilmater
Originally posted by: BurnItDwn
Extract all the data into one file.
Split the file by line using "split" Make each file 65K lines
Then Import each of your 65K line files into excel.

split -l 65000 input outputprefix
Click to expand...
OK, you'll have to explain this more. Is this a function that's native to Windows, or is that some script I'd have to write. I do not write code, so I would have no idea what I'm doing there.
Click to expand...


It's built in to pretty much every distribution of Unix and Linux, I assume Windows should have this functionality by now as it is open source and it's been around for decades.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top