0

I have an Excel as shown in the image below.

  • File has 9 columns.

enter image description here

Also shows is a filter on the date field, just to confirm that there are no other data apart from dates.

enter image description here

I am trying to import the data into a online database, which gives me the following import options.

  • CSV
  • TAB

When I save the file in either of those formats, for some reason the final column occasionally overalls into the data column, even though this issue is not present in the core XLS file.

See below showing the date column no containing other data apart from dates.

enter image description here

Does anyone have some ideas as to how I can fix this without having to go through 1000000's of lines. I have tried using the filter to choose all the non date data but that does not work as it only picks a few of the non date lines.

thanks

1 Answer 1

0

Your last column Comments contains line breaks.

In Excel that is fine because you can have a line breaks in a single cell. But if you export this as a text file there is no way to distinguish between line breaks indicating a new entry and line breaks in a comment.

If you wrap your text columns in quotes (there should be an option in the export wizard) the problem should be gone.

If the destination database cannot handle line breaks in quoted text fields correctly you can always remove the line breaks before the export.

1
  • thanks for your help :) I managed to clean it up using =CLEAN function.
    – Ankh2054
    May 6, 2016 at 8:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .