Reading a CSV file ... extra rows ??

[billko]

---

@RTSLVU wrote:

---

@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.

---

My interpretation of what was missed here is that there are supposed to be 17 lines of meaningful data, but it is only displaying ten lines and the other seven are missing data.  That's where I went wrong.  Upon more careful reading, I see that the file has 17 lines total, of which only the first ten are of interest.  This "new" understanding of mine makes the whole problem a lot simpler.  Kind of like what you guys suggested.

[MogaRaghu · Accepted solution]

---

@billko wrote:

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.

---

This post has been a great learning. I will try to answer all of the suggestions  :

1. Excel edited CSV files do have some formatting data that is not required. After all Excel does warn about this when saving. But i never listen !!

2. Deleting unwanted rows or clearing contents in Excel still leaves some formatting data behind. 

3. This CSV file is created by user and the number of rows is not known before i read it and so i cannot read "fixed"number of rows. 

4. The suggestion to read it as a Text string and trimming white char is an option i can try. 

5. For now i opened the CSV file in a Notepad and could immediately see the spaces after the 10th row. Just deleted them and used back space till i reached the last comma and then ran the VI. It brought up an exact 10 rows. 

SO now i will ask the user to create the CSV in Notepad. 

Thanks to all of the folks who posted. 

@ps : The idea that worked is @Bilko and that should have been marked as solution but i thought of summarizing all other posts so that it will be of use to someone in future and marked this as solution.... the rest of posts are kudoed !!

[crossrulz]

---

@MogaRaghu wrote:

PS : The idea that worked is @bilko and that should have been marked as solution but i thought of summarizing all other posts so that it will be of use to someone in future and marked this as solution.... the rest of posts are kudoed !!

---

You can mark more than one solution...

[RTSLVU]

---

wrote:

This post has been a great learning. I will try to answer all of the suggestions  :

1. Excel edited CSV files do have some formatting data that is not required. After all Excel does warn about this when saving. But i never listen !!

2. Deleting unwanted rows or clearing contents in Excel still leaves some formatting data behind. 

3. This CSV file is created by user and the number of rows is not known before i read it and so i cannot read "fixed"number of rows. 

4. The suggestion to read it as a Text string and trimming white char is an option i can try. 

5. For now i opened the CSV file in a Notepad and could immediately see the spaces after the 10th row. Just deleted them and used back space till i reached the last comma and then ran the VI. It brought up an exact 10 rows. 

SO now i will ask the user to create the CSV in Notepad. 

Thanks to all of the folks who posted. 

PS : The idea that worked is @bilko and that should have been marked as solution but i thought of summarizing all other posts so that it will be of use to someone in future and marked this as solution.... the rest of posts are kudoed !!

Actually more modern versions of Excel do not leave such things behind, as I have Office 2014 and tried to recreate your issue yesterday. I tried both cut, delete, and clear contents but my version of Excel did not leave empty cells or cells containing spaces behind

Once you have the file in an array check each cell for invalid entries, empty strings, spaces, and delete them or pop-up an error message.

As much as you can stress to end users to only use Notepad, you know someone along the way is going to use Excel or some other program to make the .csv files.

[billko]

I wonder that the user is using a template where they inadvertently added all those extra lines and spaces themselves and when they edit and "save as..." the extra lines get saved along with everything else.  I cannot imagine that even ancient versions of Excel would put such stuff in something clearly saved with the CSV extension selected.

Another possibility is that they cut and paste from data to file without realizing they are cutting and pasting a bunch of invisible stuff along with the data.

Since this is a human-generated file, I would do a whole bunch of format checking, just as many people here had suggested.