+ Reply to Thread
Results 1 to 17 of 17

text import wizard

  1. #1
    Registered User
    Join Date
    08-12-2007
    Posts
    12

    text import wizard

    I remember my original Excel, when I opened a txt file, I would be allowed to delineate where columns ended, and move the column markers around. I am using Execl 2000, and I am not allowed to set column separators, other than by specifying the delimiter character.

    My problem is that I am importing an error log that only has spaces to separate columns, but also has spaces in the descriptive section that should be one column.

    What happened to Excel allowing you to combine columns? Is there any way around this problem?

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by basic792
    I remember my original Excel, when I opened a txt file, I would be allowed to delineate where columns ended, and move the column markers around. I am using Execl 2000, and I am not allowed to set column separators, other than by specifying the delimiter character.

    My problem is that I am importing an error log that only has spaces to separate columns, but also has spaces in the descriptive section that should be one column.

    What happened to Excel allowing you to combine columns? Is there any way around this problem?
    I cannot fully remember '2000, but your question reads incorrectly.

    Within the wizard, the first screen asks if you want 'delimited' or 'fixed'

    If you chose 'delimited' you specify a delimiting character.

    If you chose 'Fixed' you select a column fixing position.

    Does this help?
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    08-12-2007
    Posts
    12

    text import wizard

    Yes, I figured out that I had to select delimited. My problem now is that when I import my site log, and the only delimiter is a space, I get some fields broken into 2 columns, i.e., "GET /bls/about-us.htm HTTP/1.1", where "GET" is a field, and the space creates a field separator, then "/bls/about-us.htm HTTP/1.1" becomes the next field.

    Is there a way in the current versions of Excel to move or remove field separators while in the Text Import Wizard? I remember that capability in the older versions of Excel.

  4. #4
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169
    Hi - One idea I've used in these situations is to maybe edit the TEXT file ahead of time with EDIT/REPLACE ALL commands.

    For example, you might take the GET command with a space and change it to "GET-" (with a dash in it) so that fewer field splits occur. This may or may not be a good alternative for you.

  5. #5
    Registered User
    Join Date
    08-12-2007
    Posts
    12

    text import wizard

    Yes, I thought about doing that, but I would have to do so many edits that it becomes burdensome.

    I just noticed that on my Vista computer, when I open the log in Excel, I can't find a way to remove the separator between the Get and rest of the field, but on my XP computer, it seems to work.

    Any idea why this is? Could Vista be causing the problem?

    And I am still wondering why Excel seems to have lost the usability function of allowing me to remove or move field separators before I finish opening the file from the Text Import Wizard.


    Quote Originally Posted by harrywaldron
    Hi - One idea I've used in these situations is to maybe edit the TEXT file ahead of time with EDIT/REPLACE ALL commands.

    For example, you might take the GET command with a space and change it to "GET-" (with a dash in it) so that fewer field splits occur. This may or may not be a good alternative for you.

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by basic792
    Yes, I figured out that I had to select delimited. My problem now is that when I import my site log, and the only delimiter is a space, I get some fields broken into 2 columns, i.e., "GET /bls/about-us.htm HTTP/1.1", where "GET" is a field, and the space creates a field separator, then "/bls/about-us.htm HTTP/1.1" becomes the next field.

    Is there a way in the current versions of Excel to move or remove field separators while in the Text Import Wizard? I remember that capability in the older versions of Excel.
    Hi,

    I cannot say that I recall any such option in the early versions, however,

    for the fields, is the log a format such that it is only one column that may become 2 columns?

    There is not much information to go on here, but if it is always the 4th of 4 columns that splits you could concatenate into a helper column with =D1&E1 and then remove the original split column pair.

    any further clues on the data?
    ---

  7. #7
    Registered User
    Join Date
    08-12-2007
    Posts
    12

    Text Import Wizard

    The file I am trying to open in Excel is a site log. I am able to change the file extension to anything like .txt or .csv, but it does not change my problem. Here is an example of one line of the file:

    59.95.231.191 - - [11/Aug/2007:09:27:24 +0000] "GET /images/gloria4.jpg HTTP/1.1" 200 6120 "http://www.basic-learning.com/bls/about-us.htm" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)"

    The file has many spaces, but I want to break it up into fields as follows:

    field 1:59.95.231.191
    field 2: - -
    field 3:[11/Aug/2007:09:27:24 +0000]
    field 4:"GET /images/gloria4.jpg HTTP/1.1"
    field 5:200
    field 6:6120
    field 7:"http://www.basic-learning.com/bls/about-us.htm"
    field 8:"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)"

    Sometimes when I select Open With Excel, it opens with each line in a cell, and other times it opens and asks what the delimiter is. I can't figure out how to always make it ask for the delimiter of fields.

    Do you know of a switch to force Excel to ask for the delimiting character?

  8. #8
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by basic792
    The file I am trying to open in Excel is a site log. I am able to change the file extension to anything like .txt or .csv, but it does not change my problem. Here is an example of one line of the file:

    59.95.231.191 - - [11/Aug/2007:09:27:24 +0000] "GET /images/gloria4.jpg HTTP/1.1" 200 6120 "http://www.basic-learning.com/bls/about-us.htm" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)"

    The file has many spaces, but I want to break it up into fields as follows:

    field 1:59.95.231.191
    field 2: - -
    field 3:[11/Aug/2007:09:27:24 +0000]
    field 4:"GET /images/gloria4.jpg HTTP/1.1"
    field 5:200
    field 6:6120
    field 7:"http://www.basic-learning.com/bls/about-us.htm"
    field 8:"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)"

    Sometimes when I select Open With Excel, it opens with each line in a cell, and other times it opens and asks what the delimiter is. I can't figure out how to always make it ask for the delimiter of fields.

    Do you know of a switch to force Excel to ask for the delimiting character?
    I believe that the filetype of .csv is opened automatically, and .txt will walk you through the wizard.

    If you record a macro whilst you adjust the columns as required after a Delimited = Space you can then assign Shortcut keys to that macro for future use, remember to adjust the last row number to suit.

    After a sample try, if you post the sample workbook as a .zip you will get a better help.

    ---

  9. #9
    Registered User
    Join Date
    08-12-2007
    Posts
    12

    Text Import Wizard

    This is weird! I just tried opening the same log file with both a .0 and .txt extension with Excel on 2 different computers, one running XP, the other running Vista.

    Sometimes the log file opens and asks for field deliniators, and sometimes they do not ask, just put all lines in the first column.

    Do you know of a way that I can force Excel Text Import Wizard to always ask for field delineators when a file other than .ex? or .txt is opened. That might be my problem.

    As for old versions of Excel, and allowing you to add or remove column delineators, I am sure I remember it, but do not have an old version to check. I will ask on the forum if anyone remembers this function.

  10. #10
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by basic792
    This is weird! I just tried opening the same log file with both a .0 and .txt extension with Excel on 2 different computers, one running XP, the other running Vista.

    Sometimes the log file opens and asks for field deliniators, and sometimes they do not ask, just put all lines in the first column.

    Do you know of a way that I can force Excel Text Import Wizard to always ask for field delineators when a file other than .ex? or .txt is opened. That might be my problem.

    As for old versions of Excel, and allowing you to add or remove column delineators, I am sure I remember it, but do not have an old version to check. I will ask on the forum if anyone remembers this function.
    'Sometimes' ?

    you will need to be more specific,.

    The .txt file should ask for the Wizard. Other files should not.

    The way to force Excel to open and ask for Wizard would thus be to rename the file to a .txt.

    any other clues?
    ---

  11. #11
    Registered User
    Join Date
    08-12-2007
    Posts
    12

    Text Import Wizard

    Thanks for your reply.

    You may think that Excel automatically opens the Text Import Wizard when it opens a .txt file, but not on my computer. I am attaching a 2 line part of my site log. When I try to open it from Windows Explorer, Excel does not show the Text Import Wizard, it just opens with each line in the first column.

    I have tried changing the extension to others, but either I do not get the option to Open With, or it opens like this.

    I am using Excel 2000, on a Vista computer.

    I sure could use a solution to this problem as I need to view, sort and analyze my site log.

    I must add that I just looked for the attachment, but can not find it. I will try to attach it again. The filename is access_log-test.txt.
    Last edited by basic792; 08-17-2007 at 08:37 AM.

  12. #12
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by basic792
    Thanks for your reply.

    You may think that Excel automatically opens the Text Import Wizard when it opens a .txt file, but not on my computer. I am attaching a 2 line part of my site log. When I try to open it from Windows Explorer, Excel does not show the Text Import Wizard, it just opens with each line in the first column.

    I have tried changing the extension to others, but either I do not get the option to Open With, or it opens like this.

    I am using Excel 2000, on a Vista computer.

    I sure could use a solution to this problem as I need to view, sort and analyze my site log.

    I must add that I just looked for the attachment, but can not find it. I will try to attach it again. The filename is access_log-test.txt.
    I see no attachment, however, are you opening the file as 'Open With' from an explore listing (opens with all data in column A) or in Excel.exe as File, Open (opens Wizard)?

    ---

  13. #13
    Registered User
    Join Date
    08-12-2007
    Posts
    12

    Wow!

    You know, that is the difference!

    When I go from Explorer, and use Open With Excel, the Text Import Wizard does not come up, but if I open a txt file from Open in Excel, it does. That solves my one problem.

    I talked to someone else who has been using Excel for a long time, and he agrees that the Text Import Wizard used to let you move/remove/add separators between fields during the Text Import Wizard Step 3. That was a great way to eliminate columns that you did not want, or add new ones. That feature seems to be out of the 2000 version. I am checking with another computer that is running the 2004 version, and will see how it handles the text delimiters. I will put my results out on the forum after I check it out.

    Thanks again.

  14. #14
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by basic792
    You know, that is the difference!

    When I go from Explorer, and use Open With Excel, the Text Import Wizard does not come up, but if I open a txt file from Open in Excel, it does. That solves my one problem.

    I talked to someone else who has been using Excel for a long time, and he agrees that the Text Import Wizard used to let you move/remove/add separators between fields during the Text Import Wizard Step 3. That was a great way to eliminate columns that you did not want, or add new ones. That feature seems to be out of the 2000 version. I am checking with another computer that is running the 2004 version, and will see how it handles the text delimiters. I will put my results out on the forum after I check it out.

    Thanks again.
    The Wizard has the feature at screen three, 'Skip this column - do not import' at the point where mostly just the default 'General' is used for each column as selected. However, this is not what your original post asked.

    Do I take it that you are running the Mac version?
    ---
    Last edited by Bryan Hessey; 08-18-2007 at 12:26 AM.

  15. #15
    Registered User
    Join Date
    08-12-2007
    Posts
    12

    text import wizard

    First of all, I am using MS Office 2000 on a Vista and also XP computer.

    Next, step 3 of the text import wizard does allow you to exclude a column, but the original version of Excel allowed you to move, add or eliminate a column delineator. This functionality allowed you to consider a space to be a delineator, but you could make a sentence with spaces between words into on column by removing the delineators in the sentence.

    How long have you been working with Excel? I am sure that this functionality was included in the '98 version of Excel.

  16. #16
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by basic792
    First of all, I am using MS Office 2000 on a Vista and also XP computer.

    Next, step 3 of the text import wizard does allow you to exclude a column, but the original version of Excel allowed you to move, add or eliminate a column delineator. This functionality allowed you to consider a space to be a delineator, but you could make a sentence with spaces between words into on column by removing the delineators in the sentence.

    How long have you been working with Excel? I am sure that this functionality was included in the '98 version of Excel.
    The functionality was not available in '97 and I still consider the '98 version to be well unloaded and not used, but that's just personal preference after testing the Network capabilities of the early releases. As for how long I have worked with Windows etc, I still have a boxed copy of Windows 2, which of course pre-dates Excel, I didn't care much for the original Version 1.

    ---

  17. #17
    Registered User
    Join Date
    08-12-2007
    Posts
    12

    text import wizard

    Maybe it was '97, but I am sure it had that functionality.

    In any case, thanks for the help.

    Dan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1