Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing text file (2000)

    I'm attempting to import a delimited text file in Excel, however, I'm having a bit of a problem.
    This forum is always helpful, so rather than reinventing the wheel, I thought I'd ask here.

    There is an application that I'm exporting data from that has a number of 'columns' and text fields.
    The issue seems to be that when there are carriage returns in one of the fields, that Excel reads them in as as separate line.

    Here is a sample... "this text.. then
    this text"
    where after "then" is a carriage return.
    Has anyone written anything that would scan through a text file and take out carriage returns where they would cause a record to break like this?

    Thank you,
    Lance

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Importing text file (2000)

    Lance,

    Select your range and Edit | Replace (Ctrl + H)

    In the "Find what" box, hit Alt + 0010 (from the numeric keypad).
    You won't see it there, but if you did it correctly, it should work.
    In the "Replace with" box enter a space or nothing or whatever you want.

    You may have to repeat the above process using Alt + 0013

    Regards,
    Jim Cone
    San Francisco, CA

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing text file (2000)

    Jim, Thank you for the information. My question was more about how to handle the carriage returns before they end up on different lines. I'll give more detail here...

    let's say i have 2 records 1 and 2 in a tab delimited text file.
    The records have 2 fields "Number" and "Comment"
    Record 1 has the following values
    Number is "100" and Comment is "Eat at
    Joe's"
    Record 2 has the following values
    Number is "101" and Comment is "All you can eat."
    Notice that there is a line break after the word "at" in record 1.
    As I'm importing this information in to Excel, I would like to be able to recognize that "Joe's" actually belongs to record 1. Currently, Excel is reading that as record two and Record 2 as Record 3.
    Make sense?
    Thank you.
    Lance

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Importing text file (2000)

    Try Steve's approach in <!post=this post,328972>this post<!/post>. Bring all the text in as one column, then run the Find-and-Replace, then use Data | Text to Columns.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Lounger
    Join Date
    Apr 2001
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing text file (2000)

    for what it's worth, i chose an alternate solution... one i actually figured out myself ! but, was based on part of what i read here in certain posts.

    So, I import the text file with the mangled rows and all.
    I start at the bottom of the rows and work my way up.
    for each of the rows i check to see if (using my previous example) column b is empty.
    if it is, then the data in column A must be associated with the record above...
    So, I grab it and store it then check the next record. it the next record has data, then i append what i stored, set the cell value to the new appended value and check the next record.
    seems to work pretty well.

Posting Permissions

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