CSV delimiter

IlkkaKnuuttila

New Member
Hello,

We're having an issue with Blue Prism when trying to read a CSV-file. For some reason Blue Prism is using comma as the delimiter when it should be semicolon. All the Windows settings should be right in the region settings but and when you open the CSV manually it uses semicolon as it should, but still BP uses comma. Is there a some other setting we might have missed?
 

anisjolly

Administrator
Hi @IlkkaKnuuttila

Comma delimited CSV files are normally the most common format. Are you using the standard File Management utility object from within Blue Prism to parse the CSV or are you using custom code?
 

PRJ

New Member
Hi,
I am facing the same issue of not reading the csv content properly. Though we are using the File Management Utility, i am not able to read csv file content as comma separated value.
 

VJR

Well-Known Member
Hi @IlkkaKnuuttila,

You can make the below changes to the Excel VBO for importing a CSV file with a semicolon.
(If you do not want to make the changes to the VBO then you can try using the 'Read All Text from File' and 'Read Lines From File' actions of 'Utility - File Management' and see if you can split the text by semicolon. But that is going to be a tedious task.)

For the Excel VBO method please refer the attached files.

1. Process Diagram.JPG: Contains the process flow diagram.

2. Source CSV file.JPG: This is how the CSV looks. You can see that the data itself contains a comma "," but is separated by a semicolon ";".

3. Import CSV VBO changes.JPG: Take a backup of the 'MS Excel VBO' before making the below changes.
You will need to Open the MS Excel VBO, make a copy of the already existing 'Import CSV' page (yellow highlighted). Give a name to the new page and also to its Code stage (red marked).

4. Code Properties change.JPG: By default the comma parameter is True. You need to set it to False and make semicolon as True.
Save the new action page. Publish it so that it will be visible in the Process. You need to hit the Reset button of the process or sometimes have to close and reopen Blue Prism.

5. Action properties.JPG: Set the action properties for the new action that you just designed as shown.

6. Output after running the process.JPG: The output now shows the text in separate columns leaving behind the comma intact and correctly separated by the semicolon.




Hi @PRJ,

You can too implement this solution and if your problem is different than this then you can open a new thread in the forum describing the complete details of your issue.
 

Attachments

  • 1. Process Diagram.JPG
    1. Process Diagram.JPG
    25.8 KB · Views: 545
  • 2. Source CSV file.JPG
    2. Source CSV file.JPG
    16.1 KB · Views: 547
  • 3. Import CSV VBO changes.JPG
    3. Import CSV VBO changes.JPG
    74.2 KB · Views: 599
  • 4. Code Properties change.JPG
    4. Code Properties change.JPG
    62.8 KB · Views: 658
  • 5. Action properties.JPG
    5. Action properties.JPG
    51.1 KB · Views: 605
  • 6. Output after running the process.JPG
    6. Output after running the process.JPG
    15.3 KB · Views: 467

jasperv

New Member
Thanks for the explanation. As an additional question, I would like to ask if you have a solution for a tidle (~) delimited file? I added the following to the code, but I cannot get it working:

.TextfileOther = True
.TextfileOtherChar = "~"

Thanks a lot!
 
Last edited:

VJR

Well-Known Member
Hi jasperv,

Set the rest of the Delimiters to False and just add the below line and it will work.

.TextFileOtherDelimiter = "~"
 
Last edited:

Sviatoslav

New Member
Hi @IlkkaKnuuttila,

You can make the below changes to the Excel VBO for importing a CSV file with a semicolon.
(If you do not want to make the changes to the VBO then you can try using the 'Read All Text from File' and 'Read Lines From File' actions of 'Utility - File Management' and see if you can split the text by semicolon. But that is going to be a tedious task.)

For the Excel VBO method please refer the attached files.

1. Process Diagram.JPG: Contains the process flow diagram.

2. Source CSV file.JPG: This is how the CSV looks. You can see that the data itself contains a comma "," but is separated by a semicolon ";".

3. Import CSV VBO changes.JPG: Take a backup of the 'MS Excel VBO' before making the below changes.
You will need to Open the MS Excel VBO, make a copy of the already existing 'Import CSV' page (yellow highlighted). Give a name to the new page and also to its Code stage (red marked).

4. Code Properties change.JPG: By default the comma parameter is True. You need to set it to False and make semicolon as True.
Save the new action page. Publish it so that it will be visible in the Process. You need to hit the Reset button of the process or sometimes have to close and reopen Blue Prism.

5. Action properties.JPG: Set the action properties for the new action that you just designed as shown.

6. Output after running the process.JPG: The output now shows the text in separate columns leaving behind the comma intact and correctly separated by the semicolon.




Hi @PRJ,

You can too implement this solution and if your problem is different than this then you can open a new thread in the forum describing the complete details of your issue.

Hi VJR,

Could you please advice. I followed all steps mentioned above, but Blue Prism is showing me following error while importing CSV file to Excel " : Failed to import CSV: The given key was not present in the dictionary."

Thank you for your time.

Best Regards,
Sviatoslav
 

VJR

Well-Known Member
Hi VJR,

Could you please advice. I followed all steps mentioned above, but Blue Prism is showing me following error while importing CSV file to Excel " : Failed to import CSV: The given key was not present in the dictionary."

Thank you for your time.

Best Regards,
Sviatoslav
Hi Sviatoslav,

There appears to be a problem with an incorrectly generated/passed parameter to/from the stages.
At first keep only the Create Instance -> Open Workbook -> Show stages.
This should display the file without any issues. If it throws an error then it is an issue in the parameters of these stages.
If it is working fine then add the Import CSV with Semicolon. If it now throws an error then check the parameters passed to/from this stage.
 

Sviatoslav

New Member
Hi VJR,

Thank you very much for advice. I checked it.

Now it works. Thank you very much for support!

Best regards,
Sviatoslav
 
Last edited:
  • Like
Reactions: VJR

delacoche

New Member
Hi @IlkkaKnuuttila,

You can make the below changes to the Excel VBO for importing a CSV file with a semicolon.
(If you do not want to make the changes to the VBO then you can try using the 'Read All Text from File' and 'Read Lines From File' actions of 'Utility - File Management' and see if you can split the text by semicolon. But that is going to be a tedious task.)

For the Excel VBO method please refer the attached files.

1. Process Diagram.JPG: Contains the process flow diagram.

2. Source CSV file.JPG: This is how the CSV looks. You can see that the data itself contains a comma "," but is separated by a semicolon ";".

3. Import CSV VBO changes.JPG: Take a backup of the 'MS Excel VBO' before making the below changes.
You will need to Open the MS Excel VBO, make a copy of the already existing 'Import CSV' page (yellow highlighted). Give a name to the new page and also to its Code stage (red marked).

4. Code Properties change.JPG: By default the comma parameter is True. You need to set it to False and make semicolon as True.
Save the new action page. Publish it so that it will be visible in the Process. You need to hit the Reset button of the process or sometimes have to close and reopen Blue Prism.

5. Action properties.JPG: Set the action properties for the new action that you just designed as shown.

6. Output after running the process.JPG: The output now shows the text in separate columns leaving behind the comma intact and correctly separated by the semicolon.




Hi @PRJ,

You can too implement this solution and if your problem is different than this then you can open a new thread in the forum describing the complete details of your issue.
hi , thx , this is to clear , and work too good , so i have a question , how it is possible to stock this in a collection directly using code stage.
 

ewilson

Member
If you check the latest release of the MS Excel VBO, available on the Digital Exchange, you'll see that the VBO includes an action called Import CSV that includes an optional input parameter to specify the delimiter used in the CSV file.

Cheers,
Eric
 
Top