[Solved] File saved with wrong separator

[Stockman1]

We have a .ods sheet derived from a .CSV from our supplier.

I can see that the original sheet was opened with both the "," and "|" as separators.

This was an error because only the "|" should have been used.

This means that wherever there was a "," in the body of the spreadsheet the data in a particular cell after the "," got shunted over to the next cell.

Unfortunately 3 months of work have gone into the sheet since then. But it has been saved as .ods each time without anyone noticing that the columns are wrong wherever a row had a comma in it.

Is there any way to open the current .ods file so that the columns are back to how they would have been had the "''," not been used as a separator.

I have attached a small section of the file for clarity

Thank you.

[Zizi64]

You can adjust the properties of the Import filter. You can set up the separator character/s/, can set the format of thew columns...

[Lupp]

You are surely aware of the fact that the commas wrongly used as cell separators are no longer present in the file in any way.
Thus a reconstruction of original entries containing a comma depends on the correct recognition of the rows where this needs to be done. Without your thorough explanation concerning the related characteristics any proposal based on stabbing in the dark may miss needed correctens and, even worse, may introduce new errors probably only noticed by you another few months later.

Please give a clear "syntactical" explanation, and name a dozen (or so) rows where the wrong splitting occured together with the cell contents as they should be.

[Stockman1]

Please see row 128 and 129 at columns W. At that point, what is in W should be in V, what is in X should be in W etc.
Because of the uniform nature of most of the columns, the error at V128 and V129 and then many lines below will be very apparent.

[soby]

As Lupp explained we're in the dark, but we all want to help, can you provide us with a little original .csv from your supplier. The attachment is not really of help for us. We want our self inspect the file so we can see the problem.

[keme]

If you don't have the file that soby mentioned (or if that is not relevant to your current issue), the best solution may be to export as CSV again, and edit that text file by hand before importing it back. In any case, reliably locating the faulty rows is the greater challenge, I guess.

One approach to giving guidance to people who try to help you:

• Provide an overview of which columns you require. A list of headings should do (preferably one which fits the actual table in your previous attachment).
• Also, tell us which columns may have contained a comma. Typically they are in descriptive fields and in numbers, but not in product codes or status fields.
• For the columns with a limited set of possible values, it would help to know which values are permissible (like "integers from 0 to 2000", "Y and N", "Red, Green, Black and Silver"). That makes it easier to create automated markup for rows that require fixing. As you say, the errors is often apparent when you look at them, but locating them by manually browsing a table of 1000 rows and 30 columns is tedious work, and there is a good chance that some mistake will be missed.
• Did the blank columns F through O also come about because of mistakenly interpreted column separators, or did you remove something (e.g. confidential info)?

[Stockman1]

Thanks for the reply Keme.
Yes you've accurately guessed that we have sensitive information within the file hence the problem sending the original file in it's un-adjusted form.
As there does not seem to be a 'stock' fix for this problem I have had a rethink.
We have only added to new and inserted columns. Therefore I am able to do a vlookup from the original file and import cells from the original csv to the ods file(s).
It's going to take a while as there are many lines and I know that once lookups go beyond 100000 lines on this pc I have to go for lunch.
Thanks all for your input.
Marking as solved

[Lupp]

Please see row 128 and 129 at columns W. At that point, what is in W should be in V, what is in X should be in W etc.

On the other hand there are many rows where a content obviously belonging to column Z actually is dislocated to column Y or even farther to the left. In fact the short sample contains exactly 661 cases where a single N or Y is contained in a cell within the range of columns from W through Y. Well, in a majority of these cases there is also a N/Y flag in column Z, but in many cases there is none.

Because of the uniform nature of most of the columns, the error at V128 and V129 and then many lines below will be very apparent.

Regarding my findings above I cannot confirm this. In specific column W doesn't seem to contain a consistent type of information. However, excluding columns W and V still leaves 165 cases where column Y contains the displaced Y/N. In none of these cases Z also has such a flag.
That's no basis for any reliable advice.