How to Find and replace imported CSV ?

General discussion on OpenOffice Calc and its spreadsheets.

How to Find and replace imported CSV ?

Postby LiverPAN » Mon Aug 04, 2014 10:16 am


I have some csv files that I have imported and want to amalgamate into a single Calc file.

They all start with a date field which looks normal on the import screen but when the file is opened, the system appears to add a ' before each string in the date field. It does not happen in any of the other fields.

The effect is to make the filed unable to function as a date field and sorting is as if it is a text string.

I have tried to find and replace the ' but OO cannot find the character. It funds any other string but the ' inside a cell leading a 0 is apparently invisible to the search function. I have tried using the regular expression option in case but still no joy.

If I manually remove the ' the cell immediately formats as a date. But I can't do that for over a thousand rows.

Any suggestions?

Thanks in Advance
Posts: 24
Joined: Tue Mar 25, 2014 6:30 am

Re: How to Find and replace imported CSV ?

Postby Gregory A. Chase » Mon Aug 04, 2014 12:09 pm


The apostrophe is not actually present in the cell. It's just a marker that the value is text when otherwise it would be interpreted and converted by Calc into a number. Dates are just numbers (integers, actually).

The simplest way to fix it is to choose the "detect special numbers" option when you do the import ... oh, hold on, are you actually still using OO 3.0? If so, it's time to upgrade if at all possible. The text import has had some important changes since then, if I recall correctly.

If you don't see the "detect special numbers" option, or can't repeat the import, you can fix the problem in the spreadsheet by first making sure that the cells in the date column are not formatted as text (any other format is ok), then do

Edit > Find & Replace
Search for: .*
Replace with &
Options/Regular expressions: ON
Options/Current selection only: ON
Click "Replace All".

This doesn't change the text, instead it allows Calc to reconsider the imported text under conditions that allow the conversion to number values (dates).

I Hope this will helps you
Gregory A. Chase
Posts: 22
Joined: Mon Mar 24, 2014 6:14 am

Re: How to Find and replace imported CSV ?

Postby Champ_AN » Mon Aug 04, 2014 12:18 pm


When you import the CSV file, you get a Text Import screen.
On that screen, near the bottom, there is a section marked Fields.
This section contains a drop down list marked Column Type.
Select the column/field that contains the Dates and change the Column Type from Standard (which is the default) to the appropriate Date format (there are 3 options pre-designated).

Posts: 19
Joined: Tue Mar 25, 2014 6:34 am

Return to Calc

Who is online

Users browsing this forum: No registered users and 1 guest