Results 1 to 9 of 9
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel (2000)

    What is at the end of the line?

    One speculation might be that the previous method only added a CR (ASCII 13) (or maybe just a LF (ASCII 10)while the new way adds both a CR and LF.

    How you get rid of depends on where it is located. You could do a find/replace in notepad, you could import with a macro to eliminate it.

    I think we need more info for a more definitive solution

    Steve

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Arcadia, California, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel (2000)

    I think your speculation is correct. When I open up the text file in word, there is a paragraph mark at the end of each line (CR?). How do you search and/or replace for a line feed?

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Arcadia, California, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import csv file results in blank rows (Excel 2000)

    Subject edited by HansV to aid future searches - "Excel" was not very informative.

    I'm attempting to import a txt file (delimited with commas) into Excel (I've done this many times without a problem). I'm now using an FTP program to transfer the data file to my local PC. When I import the test file, I get a blank line in between each line of data. Here is a sample of the text file:
    ======
    000-BASE ,VACUUM FORMED BASE,$0.00,$0.00

    000-INSERT ,MOLDED INSERT,$0.00,$0.00

    000-LID ,VACUUM FORMED LID,$3.00,$3.00
    =====
    When I view this file in Notepad, the blank lines do not exist. I can download from MS and run a macro that can get rid of every other line, but this macro does not work well on large files. Anyone have any suggestions?

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Excel (2000)

    Could you post a small sample text file? Replace sensitive data with dummy data.

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Arcadia, California, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel (2000)

    Attached is a sample file (test.txt). Thanks for your help so far!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel (2000)

    each line ends in 2 carriage returns (ascii 13) and a line feed (ascii 10)
    When excel imports it, the commas indicate new cells (columns) and the CR indicate a new line. It strips the LF.

    Since there are 2 CRs you get a blank line.

    You can replace them in word, find: ^p^p replace:^p then save it as text and import it (manually or with a macro)
    Get rid of blank rows in excel import (autofilter will work "easily"), manually or with a macro
    write code to import it and strip them out.

    It depends on how often you want to do this and how much manipulation you are going to do to recommend the "best way"

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Excel (2000)

    Instead of the usual Cr or CrLf at the end of each line, the text file contains CrCrLf. A very simple way to get rid of the blank lines is to sort on column A.
    You can also open the file in Word, and replace ^13^13^10 with ^13 (or with ^p), save (ignore the warnings about features being lost) and open in Excel.

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Arcadia, California, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel (2000)

    The easiest method for me was to use Word to replace ^13^13^10 with ^13. It's an extra step, but it works perfectly and only takes a few seconds.

    Thanks Steve and Hans for your help. I really appreciate it!

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Import csv file results in blank rows (2000)

    I reckon the easiest way is to sort the area, which Hans mentioned.
    Note you need to select the area before you sort. (You can't use Excel's auto-area guesser). You can select extra blank area below the list as well. It doesn't matter if you sort Ascending or Descending - Excel always puts the Null rows at the bottom. A macro to do this sort is very simple.
    This will work as long as the rows are truly blank. I'm not sure how your data will go.

Posting Permissions

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