Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Issue with the data while converting the .CSV to .XLSX

    Hi Experts
    I have received a .CSV file and I convert this file to .XLSX
    Issue is after converting the file from .CSV to Excel I find a special character “ ‘ “ in front of the each cell’s data either text, number, or date. I find that it is available in the .CSV file itself in front of each entry. Is there any way to remove it from the excel sheet.
    Regards,
    JD

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Putting an apostrophe in front of data tells Excel to treat everything as text, so you lose all formatting. To have Excel recognise numbers, dates etc you need to remove the apostrophe from the CSV data before import. Can you post a couple of lines from the CSV so we can see what can be done to fix the issue?

    cheers, Paul

  3. #3
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Paul

    Please find few sample lines below:

    TITOLO DOCUMENTO;LINE OF BUSINESS;COVER;INS;
    ;'ABC;'Aviation;'FIN;'005

    Regards,
    JD

  4. #4
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Paul,

    I just checked all the excel data is in General form you are right about it.

    Regards,
    JD

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    You could:
    1. Open the CSV in Notepad.
    2. Select Edit > Replace.
    3. In Find enter ;'
    4. In Replace enter ;
    5. Select Replace All.
    6. Save As using a different name - we want to keep the original.
    7. Import into Excel.



    cheers, Paul

  6. #6
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Paul

    With this the data is reflecting without the special charactor. Currently with this conversion of data from CVS to excel all the cell's data is reflecting general type irrespective of its actual data type. Is there any way to make the repective data to reflect under its actaul data type. Like date column should have date type etc...

    Regards,
    JD

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Excel has a CSV importer for you to identify columns as having a specific format, just in case the auto format doesn't work.
    I imported your test and the number came in as a number, although it may not be as it has two leading zeros.
    Note: tested with LibreOffice, I don't have MS Office on this box.

    cheers, Paul

  8. #8
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Paul

    Thanks for your help and guidance.

    Just for my understanding. The VBA code written in Excel works in LibreOffice as well?

    Regards,
    JD

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    VBA may work in LibreOffice. See this link:
    http://ask.libreoffice.org/en/questi...2#post-id-6982

    cheers, Paul

  10. The Following User Says Thank You to Paul T For This Useful Post:

    Jaggi (2015-04-24)

Tags for this Thread

Posting Permissions

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