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?

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"

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.

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.