Columns in Downloaded sales data

Sorry if I'm missing something obvious or if this has been discussed before and I can't find it.

When downloading sales data to a CSV it seems that not all the appropriate data gets into the appropriate column. It seems as if a blank field is ignored so that the next field with content gets put in the first empty cell in the row available rather than the correct column according to the column headings. This means that it is impossible to sort by column. For example sorting by source doesn't work in one exmaple I tried and causes as per the list below. As you can see, some of these are from the source field but some are from the currencycode field and some are from the storeemailsent field i.e 4 columns too far to the left. But they are all shown in the "sourced" column.

Is there a way I can solve this without manipulating the whole CSV line by line? If not, can support look into this because it makes using the data export so time consuming as to be almost pointless.

Here's the data:

ALL IN THE "SOURCED" COLUMN ("Incorrect Field" is my added comment)
Search Engine
I am a dietician/nutritionist
Existing Customer
Search Engine
GBP                    (Incorrect Field)
Search Engine
Search Engine
GBP                    (Incorrect Field)
Existing Customer
Search Engine
Search Engine
Existing Customer
Recommendation
GBP                      (Incorrect Field)
Search Engine
Search Engine
GBP                      (Incorrect Field)
GBP
Existing Customer
Existing Customer
Google
Existing Customer
Recommendation
Existing Customer
Existing Customer
Existing Customer
Existing Customer
GBP                      (Incorrect Field)
FALSE                    (Incorrect Field)
TRUE                     (Incorrect Field)


This CSV has over 1000 lines. This sample has 30 lines of which 7 are incorrect. In the actual CSV there are 179 incorrect lines where this has happened! I can't go through a CSV like this manipulting 179 lines by various bits of cutting and pasting before I can use the thing!

I know I'm probably being thick, but when I use the pipe delimiter I get all the pipes between the data but the whole row is one column.

I went to try again with the quotes but now every time I log on and ask for an expoort I get a 505 error!!!!!

Is there a way of separating columns on the CSV by delimiter that I don't know about?

Hi,
Is the data in the wrong columns in the data file or is excel doing something when you import it?
Many Thanks
Support

Deep joy! Thank you!