Need help from Excel geeks!

Ilmater

Diamond Member
Jun 13, 2002
7,516
1
0
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:!!!
 

AMDZen

Lifer
Apr 15, 2004
12,589
0
76
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.
 

Tweak155

Lifer
Sep 23, 2003
11,448
262
126
Uh excel 07 removed the cap. 65k was in 2k3.

EDIT:
How is the data stored currently?
 

Ilmater

Diamond Member
Jun 13, 2002
7,516
1
0
Originally posted by: Tweak155
Uh excel 07 removed the cap. 65k was in 2k3.

EDIT:
How is the data stored currently?
My bad, I meant 2k3. I have '07, but the people I'm sending the data to do not.
 

dullard

Elite Member
May 21, 2001
25,655
4,172
126
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.
 

BurnItDwn

Lifer
Oct 10, 1999
26,236
1,722
126
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
 

Ilmater

Diamond Member
Jun 13, 2002
7,516
1
0
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
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.
 

Ilmater

Diamond Member
Jun 13, 2002
7,516
1
0
Originally posted by: Sasiki
You could have already divided it up by now. It's only like 24 sheets.
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.
 

TallBill

Lifer
Apr 29, 2001
46,017
62
91
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.

Easiest way.
 

xSauronx

Lifer
Jul 14, 2000
19,582
4
81
i say tell them to get the excel viewer and change it themselves, this sounds too much like work.
 

Cattlegod

Diamond Member
May 22, 2001
8,687
1
0
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
 

Ilmater

Diamond Member
Jun 13, 2002
7,516
1
0
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.
 

KillerCharlie

Diamond Member
Aug 21, 2005
3,691
68
91
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.
 

BurnItDwn

Lifer
Oct 10, 1999
26,236
1,722
126
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
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.


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.