Excel is opeing my CSV file strangely - has moved data around.

Hi All

I hope this is the right place to ask this, and I hope someone can shed some light on what is going on. I have googled and cannot find anything similar.

I have been working on a CSV file of products and information for my Woo Commerce Wesbite. It was going fine - I have been closing and reopening and it has always opened it to show data where I put it....until now.

All of a sudden, Excel has been doing strange things to it when it opens it up. I have been opening it as a comma delimited file. When I open it in Google sheets, it looks as it should, but when Excel opens it , it seems to move data around.

For example, it take some data from Column H and separates it out into a several rows of cells in Column A.

It also take data from Column AC and moves it to the same row, but Column V.

This is extremely annoying! Why ,excel, why? Can anyone tell me why it's dong it and how to stop it doing it?

In the attached screenshot, the file as it should be (this is an .xmls version) is at the top, and the CSV as excel opened it is at the bottom.

Excel vs Sheets.jpg

Many thanks

Becky

Originally Posted by TMS

Have you used Text to Columns with any delimiters. Excel can remember previous choices and apply them when you open/import text files.

If you have, check and clear any delimiters you have used.

Hi

Not that I am aware of. Is her some way I can tell?

Thanks

Becks

If you close Excel completely, then re-open it & open the csv file do you get the same problem?

Yes, it does. I also tried copying and pasting it from google sheets into a fresh new excel spreadsheet and saving that as a csv. It looks fine and as it used to, but then when I open it up again - back to the weird version. I don't understand it. i must be doing something differently somehow, but I don't know what has changed!

It is a CSV UTF-8 version file and when I open it (as I always have done previously) I select Delimited and then choose comma as the delimiter. In column data format i have now tried both general and text - it makes no difference.

Thanks

Becky

Would you be able to share a small sample of the CSV?

Hi

I'm just trying to figure out how to!

Becky

Hmm - I can't figure out how to attach the file to a comment - can you possibly guide me?

I would study my Styles when SS is working properly, maybe taking some screen shots for reference
Then upon reopening, I would compare before to now
If perchance something looks off, then I would trying modifying the Style issue

Originally Posted by TMS

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you - Done.

I have attached 3 files, beaus the file which is the porblem file opens both ways for me.
It should look like the excel file "Problem CSV file what it used to and should look like" (saved xls instead of csv for demonstration purposes only)

I have also saved another demo excel file instead of CSV of what it looks like when I try opening it in excel. Both of the above files look as per my earlierscreenshot too.

The "Sample of problem CSV file is the one that opens fine in google sheets, but gets changed when opened on excel.
Thanks Guys.

Becky

Thanks, But I'm afraid this is a bit over my head. I don't know what you mean by studying styles. I have tried visually comparing and have noted the differences I've found previously. It doesn't hep me figure out what is going on though.

Thanks for the files.

If I open the csv normally (ie double click) it opens in excel normally, however if I import it using From Text on the data tab I get the same problem.
It looks as though the import reads the \n\n as a newline character & I have no idea how to stop it from doing that.

On further inspection it's not the \n that is causing the problem, but there are hidden carriage returns before each \n which is causing the problems.
The only work round I can think of is to open the file in word Use search/replace & search for ^p\n and replace with \n

Hiya

Oh wow -so yes, if I open it by double clicking from document folder, it opens it normally too! Well spotted, than kyou.

I'll have a go at the ^p\n replacement too. Thank you and fingers crossed.

You're welcome & thanks for the feedback