I am losing data when I open a CSV in Excel

[mbnumba6]

Hi,

New here and searched all over the internet and could not find an answer.

I have a CSV file with 499,846 rows in it.
Each row has a field or spaces separated by commas. The commas are used as delimiters so when I open this CSV file in Excel, those commas will be used as rows.

Example, each row looks something like this:

Patient ID, First Name, Middle Name, Last Name, DOB, admission date, report type

Each comma should represent it's own column when I open it in excel.

To better explain, I have a list of 499,846 rows opened in notepad++.
When I save this file as a CSV and open it in Excel I now have 499,579 rows.
But, when I go back and open this same exact file in notepad I have the original 499,846 rows.

The only conclusion I can think of is that excel is automatically removing duplicates as soon as I open the file in excel.

If that is the case, then what can I do stop having Excel remove my data when I open it?

 

[Joe4]

Welcome to the Board!

Unless you are running some VBA code or have some add-in running, Excel should not be removing any duplicate rows.
Are you sure that your CSV does not have any blank rows, or perhaps you have some rows that are so long that they spill over on to a second line when viewing in NotePad, but is really only one line.

Note, here is one thing you might want to try. In Windows Explorer, make a copy of your CSV file, but give it a TXT extension.
Then open that in Excel as a Delimiter Text file, but choose some delimiter that does not appear in your file (like a pipe symbol or tilde).
That should import every row to column A.
See how many rows that returns. If it returns 499,579, then I don't think Excel is dropping anything. i think you just have some "run-on" records that are displaying over two rows in NotePad.

 

[mbnumba6]

So I found a solution. You were kind of right and helped point me in the right direction.

My file did not have blank rows. However, some of the data had commas when they shouldn't. Like instead of a first name, there was a comma or two. Since I used the commas as delimiters it probably through excel off when I opened the CSV in Excel.

"Then open that in Excel as a Delimiter Text file, but choose some delimiter that does not appear in your file (like a pipe symbol or tilde).
That should import every row to column A. "

You're on the money here. I did just that. Then I went to Data tab > From Text/CSV.
Selected my csv file and Excel imported the raw data and also highlighted which data had errors.

I then tried another step. Copied and pasted the data from the csv notepad file straight into excel.
All my data was now in excel meaning I had my original 499,846. However, that data was in Column A only.

So to get my columns back I went to the data tab and selected "text to columns."

And it worked. All my data is there.

There were some issues with the raw data which through everything off in excel. Still surprised that excel would remove the data or simply not upload all the data. That was the first time I ever ran into that.

Thank you again for helping out.

 

[Joe4]

You are welcome.
Glad you got the mystery solved!