Number of rows in a worksheet

I have a very large data file. It has 110,000 records approximately. 
want to import it into an excel worksheet. I did so using the Impor
Text function and defining the field widths with !.

Now the most I can seem to import onto a worrksheet is approximatel
65,500 records. The wizard then advises me to import the rest ont
another data sheet, excluding the data already imported on to the firs
sheet.

When I try to do this, the wizard will only allow me to exclude th
first 32,000 records from the second import and then will only impor
approxiately 32,000 more records. In effect the second impor
duplicates the second half of the first import (roughly) and the net
effect is to leave 44,500 records unimported. (ie 110,000 less th
first import of 65,500. The second import is only duplicating half o
the first import).

So ? What can i do ?

Does anyone know how I can exclude more than 32,000 records from th
second import. The actual error message reads "Invalid Integer" if 
enter any value above 32,000 approx in the wizard field for the record
to be excluded.

OR

Is there anyway of making the first data sheet bigger ie up to 110,00
rows approx so that all of the data comes in at one import ??

OR

Is there something obvious that I have missed.

I am running Windows 98

Tk

--
Message posted from http://www.ExcelForum.com

0
5/15/2004 4:41:33 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
278 Views

Similar Articles

[PageSpeed] 5

I'd use my favorite text editor and split the text file into smaller pieces. 

Then import them separately.

Or you could use a macro that imports each line:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596
XL: Importing Text Files Larger Than 16384 Rows
(written for xl95, but has a note to change stuff for xl97+)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"iorrasnet <" wrote:
> 
> I have a very large data file. It has 110,000 records approximately. I
> want to import it into an excel worksheet. I did so using the Import
> Text function and defining the field widths with !.
> 
> Now the most I can seem to import onto a worrksheet is approximately
> 65,500 records. The wizard then advises me to import the rest onto
> another data sheet, excluding the data already imported on to the first
> sheet.
> 
> When I try to do this, the wizard will only allow me to exclude the
> first 32,000 records from the second import and then will only import
> approxiately 32,000 more records. In effect the second import
> duplicates the second half of the first import (roughly) and the nett
> effect is to leave 44,500 records unimported. (ie 110,000 less the
> first import of 65,500. The second import is only duplicating half of
> the first import).
> 
> So ? What can i do ?
> 
> Does anyone know how I can exclude more than 32,000 records from the
> second import. The actual error message reads "Invalid Integer" if I
> enter any value above 32,000 approx in the wizard field for the records
> to be excluded.
> 
> OR
> 
> Is there anyway of making the first data sheet bigger ie up to 110,000
> rows approx so that all of the data comes in at one import ??
> 
> OR
> 
> Is there something obvious that I have missed.
> 
> I am running Windows 98
> 
> Tks
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/15/2004 5:33:39 PM
Thanks Dave will try that

Iorrasne

--
Message posted from http://www.ExcelForum.com

0
5/17/2004 11:13:14 AM
Reply:

Similar Artilces:

limit number of rows 7 colloms in a worksheet
is there a way to limit or set the number of rows & collums in a worksheet ? thanks david --- Message posted from http://www.ExcelForum.com/ "davidbrowne17" <davidbrowne17.ya1sm@excelforum-nospam.com> wrote in message news:davidbrowne17.ya1sm@excelforum-nospam.com... > is there a way to limit or set the number of rows & collums in a > worksheet ? No. All worksheets have 256 columns by 65536 rows. You can hide unused rows/columns. But why bother? Hi David, Put this in the ThisWorkbook code module. Adjust to suit the area. Private Sub Workbook_Open() Wo...

Insert rows and fill formulas
I have just added this on a work sheet. It works OK when sheet is unprotected but when I protect the sheet it comes up error 400. If I select Insert row on protection box it will insert row but not formulas while protected. How can I get it to work while sheet is protected. I have also setup a botton on the taskbar to do this for me but it will only do it for the day I set it up in. When I save as for the next day it wont work on the new sheet. Your help will be appreciated Regards Chris I'm not an expert but I thought the whole idea of protecting the worksheet is so p...

NUMBER FORMAT #9
CREATE A NEW NUMBER FORMAT SO THAT THE SELECTED DATES WILL APPEAR ONLY AS THE FULL NAME OF THE DAYS OF THE WEEK. ...

Why are my numbers disappearing in excel yet it totals them?
I have a spreadsheet that I have filled out the individual cells with number. These cells are totaling correctly, however when I open the spreadsheet the individual number I entered are showing blank.... I moved my mouse around in the spreadhsheet and all of a sudden the numbers appeared and then disappeared. Check the font color. The default is black. Also check the cell color. Default is "no fill". If the font color was changed to white, you would only see the content after you select the cell. Remember to Click Yes, if this post helps! "Donna S...

No account number shown in printed checks
Using Money 2007 Deluxe. Printed a series of checks from MS Mmoney this morning, but the account numbers didn't print on the checks. Each of the payees has an account number entered in the appropriate place, in the "Go to Payees" detail listing. Can't figure out what's going on here! Thanks for any assistance. Dave On Sat, 17 Mar 2007 08:37:03 -0700, Dave M. <DaveM@discussions.microsoft.com> wrote: >Using Money 2007 Deluxe. > >Printed a series of checks from MS Mmoney this morning, but the account >numbers didn't print on the checks. Each of...

How to remove dashes and slashes form a sequence of numbers & lett
Hi I have a sequence of numbers in column D and I require to extract just the numbers and letters to column E. D 190/0-01 31-0014 pp7/44-1 uf-744-5 E 190001 310014 pp7441 uf7445 Any pointers would be much appreciated. Kind Regards Celticshadow Put this in E1: =3DSUBSTITUTE(SUBSTITUTE(D1,"/",""),"-","") and copy down as required. Hope this helps. Pete On Oct 14, 11:34=A0am, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > > I have a sequence of numbers in column D and I require to extract just th= e > ...

Worksheets Inheriting Contents
Hi All, I'm currently working on creating a set of specifications using Excel These specifications are contained within a single Excel spreadshee which contain multiple worksheets, each relating to a different aspec of the form (say calculations, form rules, dependencies). The proble is that anytime you make a change to one form, say you change text, o add another column, you must make the corresponding changes in all th other tabs. This can be very time consuming - especially if there are tabs. My question is this: Is it possible to create a parent 'Look and Feel worksheet and hav...

Extracting macros from a worksheet #3
Gord, Thanks for the info. I have Excel setup to ask me about enabling and disabling macros when opening a worksheet. I've tried both with no luck. I get the same error when attempting to open this file. The workbook that I'm opening has always worked before. That workbook and macros were designed and written by me and they have been working for more that 5 months now. There are only two worksheets in the workbook and I've never had any reason to group them together, however, when the workbook opens with the error, I can see that the two sheets are, in fact, selected. In add...

Jump to start of next row teaser
Hi, can anyone assist in telling me if there is either :- a) a shortcut key b) a macro c) none of the above but another solution to jumping to the beginning of a new row on pressing a key. For example, after completeing cell m10 can you press enter (or any other key) and the cursor will automatically go to cell a11, then after you get to m11 it jumps to a12 and so on? Just to make things a little trickier, I know you can do this using the protect worksheet etc function, but I also have the data filter running which doesn't work when the protect sheet function is on. If there is a macr...

Conver General number to Currency
I have a column of numbers in Excel 2003 that are of General type. I need to insert a decimal point two positions in from the right of the existing number. When I do that however by using Format > Cells and changing it to Number with 2 decimal places, or to Currency format, it adds a .00 to the existing number instead of inserting a decimal point into the existing number, e.g., 999955 come out as 999955.00 or $999,955.00 when what I really want it to do is 9999.55 or even $9,999.55. Please help! Put 100 in an empty cell, select the numbers, do edit>paste special and select divide, ...

finding differing numbers.
How do I in a column of numbers some in duplication, how can i get a list off the entries which reflects these numbers but not in duplication. ie "numbers" 1, 1, 2, 3, 4, 5, 1, 5, 3, 5, 2, 1, 2. "result" 1, 2, 3, 4, 5 Thanks Chris Hi one way: - select your column - choose 'Data - Filter Advanced Filter' - choose a new range and 'unique entries' -- Regards Frank Kabel Frankfurt, Germany curleyc wrote: > How do I in a column of numbers some in duplication, how can i get a > list off the entries which reflects these numbers but not in > duplicat...

Matching Columns in work sheets and copying both rows to new
I am trying to match up two different spread sheets based on one column but to copy both rows to a new sheet. eg. First sheet has the following headings: "Owner Group " "Owner CC " "Type " "Product Categorization Tier 2 " "Product Categorization Tier 3 " "Device Name " "Status " "Model Number " "Mfg. Name " "CI ID " "Old Asset ID " "Serial Number " "Region " "Country " "Site " "Building " "Floor " "...

Duplicate Row
i dont know how to make excel automatically highlight a duplicate row any tips on this? cos its very tedious to look for duplicates on th worksheet. :confused -- Message posted from http://www.ExcelForum.com Katkat, take a look at this page from Chip Pearson's Website: http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates. -- DDM "DDM's Microsoft Office Tips and Tricks" www.ddmcomputing.com "katkat >" <<katkat.15p2xe@excelforum-nospam.com> wrote in message news:katkat.15p2xe@excelforum-nospam.com... > i dont know how to make excel au...

Last Record Numbering
I need to find a way to display in a form text box the highest numerical value entered in any one of four fields for the last record entered. For example: If Field_1 is 0001 Field_2 is 0008 Field_3 is 0005 Field_4 is 0004 When the form is opened, the textbox would display the number 0008. The textbox should display the highest of the four fields of the last record. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200708/1 Already responded to in another newsgroup. It is rarely necessary to post and re-post the same question in multiple ...

How do I select multiple rows randomly in MS Excel?
How do I select multiple rows randomly in MS Excel? Hi Select your first row and then hold down the Ctrl key while selecting the others. HTH Michael "Varun" wrote: > How do I select multiple rows randomly in MS Excel? "Varun" <Varun@discussions.microsoft.com> wrote > How do I select multiple rows randomly in MS Excel? Just another angle to the post's interp .. (with emphasis on "randomly") Here's an example set-up to play with .. Assume we have 6 rows of data below in Sheet1's A1:C6: Data1 Text1 Desc1 Data2 Text2 Desc2 Data3 Text...

removing spaces between the numbers
Hello all! When copying number from Navision account program to Excel, it copies numbers with spaces as text, but not as numerical values. Such value: 1 109 014,08 is copied as text. One possibility to set the number actually to a numerical value is to delete the spaces within the number, so after doing that it looks as 1109014,08. Then i can add, multiply, divide this number with ease. Is there a automatic possibility how remove those spaces? Is there an alterantive? Use Search and Replace - highlight all the cells, then Edit | Replace (or CTRL-H). In the Find box type a single spac...

Worksheet Menu Bar disappeared
Worksheet Menu Bar on all of my spreadsheets not longer shown on pre-existing worksheets, but does if I create a new one. The option is selected under options, and if I go to Customize/Toolbars, the Worksheet Menu Bar is checked. If I uncheck it, the bar reappears at the bottom of the sheet with the names of my two worksheets, but I am unable to retain the setting. A search on this site recommended entering "Application.Commandbars(1).Enabled = True" on a VB screen that I had no idea that existed. I hit the enter key, the menu on top expanded, but not the Worksheet Menu Ba...

Negative Number Formats
Can anyone help? I have Excel 2003 and despite setting=20 all regional options correctly and specifying number=20 formats as (99), (=A399) etc I still can't get Excel to=20 display them in parentheses. I seem to remember from=20 Excel XP that you have to install/uninstall=20 certain "foreign" language options but can't remember=20 what. Please e-mail if you have the key! Thanks I'd double check that windows setting: Windows Start button|settings|control panel|Regional Settings Currency Tab|Negative Number Format (that was the path I used in Win98.) Paul Handley wro...

[$-409] in Custom Number Formats
Hello All, I've seen [$-409] in some number formats and I was looking through the help files to see if I could find out what it means. I couldn't find anything on it. I've also seen [$-F800]. Does anyone know what these codes are for or where I can find info telling me what they are for and any others that can be used? Thanks for any help anyone can provide, Conan Kelly This was covered in the Daily Dose of Excel blog about a month ago: http://www.dailydoseofexcel.com/archives/2006/02/27/months-of-the-world/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Techn...

count number sunday+saturday in many week
I don't know what function can count number sunday+saturday in range of weeks. Please help me. Thanks Use the function Weekday(A1) to give the day of the week (Sunday=1, Saterday=7) If you haven't this function you have to activate the Add-Inn Analysis Toolpak as follows: Tools - Add-Inns - Analysis Toolpak To calculate the number of Sundays and Saterday: =CountIf(Range,1) + CountIf(Range,7) Anne "chu" <phuongchu@fptnet.com.vn> wrote in message news:%23MeeJn6PDHA.1612@TK2MSFTNGP11.phx.gbl... > I don't know what function can count number sunday+saturday in r...

TTF Number already in use error
Working on a CBM Batch, I'm choosing a specific TTF document number for a telegraphic transfer payment. Receive an error - number alreay in use. The number has never been used and does not reflect in any enquiry or report on TTF document numbers. Dear, First checklinks on (CM Transactions) then check table CB100000, make sure that the number does not exist there, if it exist then it have been used before and deleted. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Software Development Manager +962 - 79 - 999 65 85 Great Package For Business Solutions daoudm@greatpbs.co...

Maximum/Minimum number of records on import
My database is set to import a CSV. Is there a way to not allow a CSV file to be imported, and bring up a message box if the CSV file has less than 30 records or more than 500 records? I think it involves something to do with Select Case, but I'm not quite sure where to take it from there. Thank you for your response. I already have the actual importing process working perfectly the way I want it. But I was wondering whether or no there was some type of code to stick in there, to tell it not to allow importing of a CSV file with less than 30 or more than 500. If there is a code that I ca...

Fax Numbers In Exchange 2003 OWA
Hi there, When a user is using Exchange 2003 OWA and wants to view another users Fax Number they cannot see it. All they can see are the users: Name, Alias, Company, Address, City, State, Postal Code, Country/Region, Title, Company, Department, Office, Phone and Mobile Phone details, no fax number. Can anyone please tell me how to make the fax number visible in Exchange 2003 OWA? thanks Ste ...

How to measure the number of emails sent to external domain?
Can we measure in total for each person or each DL the number of email sent to external people in a month? We are running Exchange 2003 SP2. Count in mail log. -- Ray MCSE+Internet, MCDBA, MCP "MLi" <lige888@gmail.com> wrote in message news:uca$f5xZHHA.3984@TK2MSFTNGP02.phx.gbl... > Can we measure in total for each person or each DL the number of email > sent to external people in a month? > > We are running Exchange 2003 SP2. > > ...

Count number of records on a report page
Hi All, My report has several grouping levels and works OK. I am OK with creating a text box to count the entire number of records in a report and in a group. But I've been asked to count the number of records printed on any given report page. How can I do this? I'm using version 2003. Thanks in advance TRY this. Add a control to the detail line to number the detail lines Name: txtLineCount Control Source: =1 Running Sum: OverAll Add a control to the page header Name: txtPageStart Control Source: =[txtLineCount] Add a control to the page footer Name: txtRecCount Control Sour...