Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data format, need help
I have about 5,000 Internet address + ports in a .txt file, e.g:
"127.0.0.1:80 127.0.0.20:80 172.9.34.6:3128 162.168.0.4:8080 12.5.4.3:80..." As you can see, the delimiter for each entry is a "space(s) and sometimes carriage return." The format I want is for the data to be in columns and as many rows as required to complete the operation. When I try, Excel imports okay, uses the delimiters correctly, fills up sheet #1 with data, down to about row 32, then moves to the next column, until all columns are filled. A message pops up that says that page is filled and to continue, I need to use the Import Wizard, tell it where to start and it will begin a new sheet. I keep doing this until all the data is put on separate sheets. What I want in the formating is this: Option 1 1) have the delimited data go only into the columns that I can see on my screen. That would be about, column "J" or so. I don't want to have to scroll across all the columns to see the data. and 2) rather than manually use the wizard to make a new Sheet after the 32 rows? or whatever, I'd like Excel to continue to put the data in columns (that are on the screen), down to as many rows as each Sheet can hold. (Instead, it looks like it is giving me the #of rows that will fit on a page, regardless if the number of columns it has filled far exceed the size of the page.) ---- Option 2: Using the delimiters, import all the data into only one column of as many rows as can be placed on one sheet. then move to the next sheet, using a single column. _______ Are there ways to easily do either of these? Thaks, Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data format, need help
Essentially this is a job for VBA coding to read the file, parse the entries
and place them in the order and in the ranges you desire and automatically insert new sheets when required. Pretty much any other solution involves a lot of hand-eye coordination. Before even beginning the effort you need to very clearly define exactly what constitutes the area of each worksheet you want data entered into. A generalized statement like "put the data in columns that are on the screen" is undefinable - different screen resolutions display different columns and different rows. I personally don't know of any way of telling if a given column/row is currently being displayed on the video at any given time. "sunslight" wrote: I have about 5,000 Internet address + ports in a .txt file, e.g: "127.0.0.1:80 127.0.0.20:80 172.9.34.6:3128 162.168.0.4:8080 12.5.4.3:80..." As you can see, the delimiter for each entry is a "space(s) and sometimes carriage return." The format I want is for the data to be in columns and as many rows as required to complete the operation. When I try, Excel imports okay, uses the delimiters correctly, fills up sheet #1 with data, down to about row 32, then moves to the next column, until all columns are filled. A message pops up that says that page is filled and to continue, I need to use the Import Wizard, tell it where to start and it will begin a new sheet. I keep doing this until all the data is put on separate sheets. What I want in the formating is this: Option 1 1) have the delimited data go only into the columns that I can see on my screen. That would be about, column "J" or so. I don't want to have to scroll across all the columns to see the data. and 2) rather than manually use the wizard to make a new Sheet after the 32 rows? or whatever, I'd like Excel to continue to put the data in columns (that are on the screen), down to as many rows as each Sheet can hold. (Instead, it looks like it is giving me the #of rows that will fit on a page, regardless if the number of columns it has filled far exceed the size of the page.) ---- Option 2: Using the delimiters, import all the data into only one column of as many rows as can be placed on one sheet. then move to the next sheet, using a single column. _______ Are there ways to easily do either of these? Thaks, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How many data series can i format | Charts and Charting in Excel | |||
Import external data problem | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
How to import data from a password protected Access DB into Excel. | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |