Topic Thread

Expand all | Collapse all

CSV Format Issue

  • 1.  CSV Format Issue

    Posted 09-23-2019 04:47
    Hello - I am currently copying data from a web page and pasting it in a CSV. When Im trying to retrieve the data from the CSV - the number values are being formatted to scientific e-notation. For ex : if the number is 345622342342 before pasting it into a csv and closing that instance. When i try to retrieve the value from the CSV, it is being interpreted like the format ' 3.45E+15 '. How can i retrieve the data in the correct format from the csv?  Format Cell option may not work as the cell where the values are stored are dynamic and is based on the data copied. Please let me know if there are any other ways.

    Thanks in advance.

    ------------------------------
    Maneesh


    ------------------------------


  • 2.  RE: CSV Format Issue

    Posted 09-23-2019 06:59
    try to use a c# code stage and transform number to integers

    1. float myFloat = 3.5F;
    2. int myInt = Convert.ToInt32(myFloat);
    Or if entire columns ==> myNum = dt.Columns[0];
    myNum = int.Parse(dt.Rows[0][0].ToString());
    Stackoverflow should be your friend

    more info can be found here
    https://stackoverflow.com/questions/2592180/how-can-i-convert-this-column-value-to-an-integer


    ------------------------------
    Cohen
    RPA Developer

    Romania
    ------------------------------



  • 3.  RE: CSV Format Issue

    Posted 09-25-2019 12:48
    Hi Maneesh, 

    Can you post your sample CSV?

    ------------------------------
    Naga
    Associate
    Asia/Kolkata
    ------------------------------



  • 4.  RE: CSV Format Issue

    Posted 11-13-2019 16:49
    There will probably be a number of other solutions, but one thing you can try is:

    - open the CSV as text  ( Utility - File Management - Read All Text From File )
    - use Utility Strings - Split Text and Split Lines to transform it to a collection in the right format

    Basically manipulate the raw text within blue prism, rather than the CSV going through a conversion which seems to be causing your problems.

    ------------------------------
    Willem Borgesius
    Technical Director
    Delta4Services
    Europe/London
    ------------------------------



  • 5.  RE: CSV Format Issue

    Posted 11-14-2019 13:58
    Hello, Maneesh

    We used to have a lot of problems reading csv files. The best solution we found was using a schema.ini file to set how the csv file should be interpreted.
    You can set the data type of a column so you can retrieve it the you need. 

    Take a look at this link: https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver15
    and the attached file example

    Cheers






    ------------------------------
    Diogo Furlan
    Analyst
    Grupo NC
    America/Sao_Paulo
    ------------------------------