topic Reading a CSV file ... extra rows ?? in LabVIEW

Reading a CSV file ... extra rows ??

MogaRaghu — Mon, 19 Mar 2018 12:19:17 GMT

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 ??

Re: Reading a CSV file ... extra rows ??

crossrulz — Mon, 19 Mar 2018 12:33:47 GMT

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

Re: Reading a CSV file ... extra rows ??

aputman — Mon, 19 Mar 2018 13:31:56 GMT

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.

Re: Reading a CSV file ... extra rows ??

aputman — Mon, 19 Mar 2018 13:42:37 GMT

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.

Re: Reading a CSV file ... extra rows ??

Fred19ni — Mon, 19 Mar 2018 13:45:18 GMT

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!

Re: Reading a CSV file ... extra rows ??

RavensFan — Mon, 19 Mar 2018 13:49:10 GMT

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

Just delete the actual rows.

Re: Reading a CSV file ... extra rows ??

billko — Mon, 19 Mar 2018 14:00:07 GMT

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.

Re: Reading a CSV file ... extra rows ??

RTSLVU — Mon, 19 Mar 2018 14:42:11 GMT

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

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

Re: Reading a CSV file ... extra rows ??

crossrulz — Mon, 19 Mar 2018 17:45:12 GMT

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

Only read the first 10 rows instead of the entire file

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.

Re: Reading a CSV file ... extra rows ??

RTSLVU — Mon, 19 Mar 2018 18:02:20 GMT

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

Only read the first 10 rows instead of the entire file

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.

Re: Reading a CSV file ... extra rows ??

billko — Mon, 19 Mar 2018 20:02:01 GMT

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

Only read the first 10 rows instead of the entire file

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.

Re: Reading a CSV file ... extra rows ??

MogaRaghu — Mon, 19 Mar 2018 22:31:59 GMT

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

Re: Reading a CSV file ... extra rows ??

crossrulz — Tue, 20 Mar 2018 02:36:02 GMT

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

Re: Reading a CSV file ... extra rows ??

RTSLVU — Tue, 20 Mar 2018 14:19:54 GMT

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.

Re: Reading a CSV file ... extra rows ??

billko — Tue, 20 Mar 2018 14:35:57 GMT

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.