Reading a CSV file ... extra rows ??

[MogaRaghu]

 I am trying to read a CSV file created with Excel 2010 on a WIN10 laptop. 

I am getting the rows always wrong... the CSV file was originally having 17 rows and now has data only in 10 rows. I want to be able to read and report only 10 rows. If you the code display  mode of the read CSV data, you will see spaces appearing in the empty file. How to over come this ? I tried deleting the contents of the empty rows in Excel but nothing helps. Any idea how to handle such a situation [or] how to REALLY empty the rows without data in Excel ??

[crossrulz]

Edit the file in a text editor such as Notepad.  Excel can never seem to get a CSV right for me either.

[aputman]

What happens if you delete the rows and not just clear the cell contents?  It looks like you may have some type of formatting rule that is creating the extra spaces.  Clearing cell contents doesn't remove formatting rules. 

[aputman]

As an experiment, you can:

• create a blank workbook
• highlight the first 5 rows and columns
• right click and format
• change the number format to something other than General (I think anything other than General will work.  I didn't try them all.)
• export to a CSV file

When you open the CSV file with a text editor, you'll get 5 rows that each contain 5 commas, even though no data is written in the cells.

[Fred19ni]

Hi Raghunathan,

Try to copy the 10 rows and paste it in the new excel file.

Delete the old file and rename the new file by the name of the old file.

Regards,

Fred.

Kudos are welcome!

[RavensFan]

Those extra rows have spaces in them.  You say you are deleting the data, but are you sure?

Just delete the actual rows.

[billko · Accepted solution]

There are several different flavors of CSV files in Excel.  Which one did you use to save it with?

To t-shoot this issue, I'd open up the original file in something like notepad++ (or even just plain ol' notepad) so you can see exactly what you have in that file.  Odds are there's something wrong with the file.

[RTSLVU]

---

@MogaRaghu wrote:

 I am trying to read a CSV file created with Excel 2010 on a WIN10 laptop. 

I am getting the rows always wrong... the CSV file was originally having 17 rows and now has data only in 10 rows. I want to be able to read and report only 10 rows. If you the code display  mode of the read CSV data, you will see spaces appearing in the empty file. How to over come this ? I tried deleting the contents of the empty rows in Excel but nothing helps. Any idea how to handle such a situation [or] how to REALLY empty the rows without data in Excel ??

---

This won't fix what is wrong with your file but of you only need the first 10 rows why don't you:

1. Only read the first 10 rows instead of the entire file
2. Or split the array at row 10 and forget about the rest?

[crossrulz]

---

@RTSLVU wrote:

This won't fix what is wrong with your file but of you only need the first 10 rows why don't you:

1. Only read the first 10 rows instead of the entire file
2. Or split the array at row 10 and forget about the rest?

---

Another option:

1. Read the entire file as a string (Read From Text File).

2. Use Trim Whitespace.  This will clear out all of the spaces, tabs, end of lines, etc at each end of the file.

3. Use Spreadsheet String To Array to convert to a 2D array.

[RTSLVU]

---

@crossrulz wrote:

---

@RTSLVU wrote:

This won't fix what is wrong with your file but of you only need the first 10 rows why don't you:

1. Only read the first 10 rows instead of the entire file
2. Or split the array at row 10 and forget about the rest?

---

Another option:

1. Read the entire file as a string (Read From Text File).

2. Use Trim Whitespace.  This will clear out all of the spaces, tabs, end of lines, etc at each end of the file.

3. Use Spreadsheet String To Array to convert to a 2D array.

---

Exactly!

I think what has been missed here is the csv file in question is NOT the problem, well it is but... Your program should be robust enough to deal with a few extra blank lines and/or display an error message that the incoming file is invalid.

I rwrote a program that end users create "test scripts" using Excel. But I also provide a detailed Excel template (.xltx) that tells then exactly how to enter their parameters and exactly what format to save the file in.

Upon loading the test scrip (a .csv file) first all of the "extra stuff" from the template (directions, notes, etc) are removed, then it is checked for basic errors (formatting, columns all filled in, etc), and finally all of the entered parameters are checked against a config file to ensure test parameters are within the range of the equipment and instruments.

If any of this fails I popup an error dialog telling the user that the test script has errors.