Results 1 to 8 of 8

Thread: Importing an excel file with wrong date fields into libreoffice

  1. #1
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Importing an excel file with wrong date fields into libreoffice

    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.

    Code:
    '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. #2
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    11,499
    Blog Entries
    2

    Default Re: Importing an excel file with wrong date fields into libreoffice

    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. #3
    Join Date
    Jun 2008
    Location
    Earth - Denmark
    Posts
    10,730

    Default Re: Importing an excel file with wrong date fields into libreoffice

    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. #4
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: Importing an excel file with wrong date fields into libreoffice

    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 :-(

    --
    Cheers / Saludos,

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

  5. #5
    Join Date
    Jun 2008
    Location
    Groningen, Netherlands
    Posts
    20,059
    Blog Entries
    14

    Default Re: Importing an excel file with wrong date fields into libreoffice

    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.
    ° Appreciate my reply? Click the star and let me know why.

    ° Perfection is not gonna happen. No way.

    https://en.opensuse.org/openSUSE:Board#Members
    http://en.opensuse.org/User:Knurpht
    http://nl.opensuse.org/Gebruiker:Knurpht

  6. #6
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: Importing an excel file with wrong date fields into libreoffice

    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 ;-)


    Now I have another question... and I'm going to apply your same idea :-)

    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. #7
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: Importing an excel file with wrong date fields into libreoffice

    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 ;-)


    --
    Cheers / Saludos,

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

  8. #8
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: Importing an excel file with wrong date fields into libreoffice

    On 2013-04-03 22:28, Carlos E. R. wrote:
    > Now I have another question... and I'm going to apply your same idea :-)
    >
    > 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

    Code:
    =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:

    Code:
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •