Importing an excel file with wrong date fields into libreoffice

English Applications
#1

Hi,

I have to import an excel file into libreoffice. It has a column of
dates, but either the stupid bank created the dates as strings, or the
import of xls to LO converted the entries to strings.


'03/04/2013

If I edit the cell (press F2), delete the “’” in front, then press
enter, then it is converted to date. But there are so many that I can
not do that by hand.

If I do a search and replace, the thing does not find any “’”.

How can I force conversion of the entire column to dates?

–
Cheers / Saludos,

Carlos E. R.
(from 12.1 x86_64 “Asparagus” at Telcontar)

#2

Try exporting to a textfile first (or just the column data if the offending character is not unique enough.

I just tested and Kwrite will “replace” the character with space, I assume the(hopefully) that when importing into Libre Office the leading space would be suppressed and ignored.

HTH,
TSU

#3

On 04/03/2013 09:23 PM, Carlos E. R. wrote:
> If I do a search and replace, the thing does not find any “’”.

try to search on “'0” then “'1” etc, only have to do it ten times,
and its done

it that does not work try “'01/” then “'02/” and in 12 iterations it
is done

–
dd

#4

On 2013-04-03 21:39, dd wrote:
> On 04/03/2013 09:23 PM, Carlos E. R. wrote:
>> If I do a search and replace, the thing does not find any “’”.
>
> try to search on “'0” then “'1” etc, only have to do it ten times, and
> its done

No, finds nothing :frowning:

–
Cheers / Saludos,

Carlos E. R.
(from 12.1 x86_64 “Asparagus” at Telcontar)

#5

Select the column, Search “/”, replace by “-”

Test: put "03/04/2013’ etc in a column, you’ll see that after doing the above it automatically changes to “03-04-13”, since DD-JJ-MM is the default. You could write a macro that searches for cells containing 2 slashes, replace the slashes by dashes.

#6

On 2013-04-03 21:36, tsu2 wrote:
>
> Try exporting to a textfile first (or just the column data if the
> offending character is not unique enough.
>
> I just tested and Kwrite will “replace” the character with space, I
> assume the(hopefully) that when importing into Libre Office the leading
> space would be suppressed and ignored.

YES! This works, thank you.

I selected the columns of dates, pasted into gedit, saved as ‘p.csv’,
then opened that file from LO. The import window appeared, I selected
the column, selected type as “date (DMY)” on it, and accepted. Got a new
LO calc with the dates imported correctly. I only needed to copy-paste
back to the sheet from the bank and change the date format to my
preferences.

So easy! I never figured it out. I hope I remember next time… huh, no,
I’ll write it up in a “notes” sheet on the file :wink:

Now I have another question… and I’m going to apply your same idea :slight_smile:

By bank previously gave the data in two columns: one for charges, and
another for… I don’t know how you say it when they add money to your
account, in English. So two columns with positive numbers.

Now they do one column only, with positive and negative numbers; so I
have to take the column of charges to an editor, add the “-” symbol in
front, and import back as csv…

Wish I was confident with doing macros.

My bank was bought by another one… and this one makes changes. For
example, when I try to download the account data as excel or text file
using firefox, it fails (it succeeds as pdf). It worked for years
previously. The file is generated, but not offered for download. I phone
support, they say they’ll call back, after several tries.

Later I tried Windows. Firefox fails there too, but Iexplorer succeeds.
YAGH! :-/

–
Cheers / Saludos,

Carlos E. R.
(from 12.1 x86_64 “Asparagus” at Telcontar)

#7

On 2013-04-03 22:26, Knurpht wrote:
>
> Select the column, Search “/”, replace by “-”
>
> Test: put "03/04/2013’ etc in a column, you’ll see that after doing the
> above it automatically changes to “03-04-13”, since DD-JJ-MM is the
> default. You could write a macro that searches for cells containing 2
> slashes, replace the slashes by dashes.

I was intrigued by your idea. So I opened the Excel file again and
tried: it worked! And it is very fast, too.

Only that the date changes to the local default instantly: DD/MM/YY

But that is no problem, it is trivial to reformat cells (I use YYYY-MM-DD).

Thanks! I’ll note it. I’ll create a new text file with LO tricks :wink:

–
Cheers / Saludos,

Carlos E. R.
(from 12.1 x86_64 “Asparagus” at Telcontar)

#8

On 2013-04-03 22:28, Carlos E. R. wrote:
> Now I have another question… and I’m going to apply your same idea :slight_smile:
>
> By bank previously gave the data in two columns: one for charges, and
> another for… I don’t know how you say it when they add money to your
> account, in English. So two columns with positive numbers.
>
> Now they do one column only, with positive and negative numbers; so I
> have to take the column of charges to an editor, add the “-” symbol in
> front, and import back as csv…
>
> Wish I was confident with doing macros.

Didn’t work out, I have to search and replace on start of line, and
gedit has no such option visible.

But I thought of another solution.

First I copied the data to another sheet.
In a new column, I put the formula


=B2*-1

which I copy to the entire column. This writes the negative number. Then
I select the column, copy it, then use “paste special” on another column
to paste only the number values. Finally, I do a search on entire cells
to remove the ones that show a “0”.

Now I need to join the two columns into one:


1      -->  1
2           2
3           3
-4      -4
5           5
-6      -6
7           7
-8      -8

Another formula, perhaps…

Wow, no, far easier! Simply select the first column, paste special on
top of the second one, with the option “skip empty cell” ticked.

–
Cheers / Saludos,

Carlos E. R.
(from 12.1 x86_64 “Asparagus” at Telcontar)