Solved How to handle csv file

arajan37

New Member
Hi,

I am having csv file, kindly guide me how to handle the csv file. csv file have text with ";" .
kindly guide me how to convert this into proper excel file so that I can import the file and use it in my test. i have tried loads of vbo but i am not to success it.

Thanks,
Alagurajan P
 
Hi Rajan,

can you please share the sample CSV file which you are using and kindly remove the sensitive data before uploading...
 
Hi Rajan,

Here is the steps to Load CSV File and Save it as Excel.

1. Create Excel Instance
2. Create Workbook
3. Show Workbook
4. Import CSV (Action)
5. Save the File and Exit
 

Attachments

  • Import CSV Properties.PNG
    Import CSV Properties.PNG
    28.3 KB · Views: 1,344
  • Main daigram.PNG
    Main daigram.PNG
    22.6 KB · Views: 1,276

arajan37

New Member
Hi bro,
can you paste the screenshot of output of this because for me its just saved similar to csv file. but i need to split the above file properly so that i can import the row for my Tc. (in my case no delimiter is taken into consideration)
 

Attachments

  • 1.PNG
    1.PNG
    25.7 KB · Views: 751
  • 2.PNG
    2.PNG
    89.5 KB · Views: 736
Hi Rajan,

please find attached Process file have a look.

Change the CSV File path in CSV File Path Dataitem
Change the Saving Excel File Full Path with name in FileNameSave Dataitem
 

Attachments

  • BPA Process - Convert CSV to Excel.zip
    2.3 KB · Views: 541

arajan37

New Member
Hi bro thanks for your help but my problem is in both excel and collection. it's just similar to csv bro. i want to split the excel with column wise bro. kindly suggest me how to do that
 

Attachments

  • 5.PNG
    5.PNG
    36.5 KB · Views: 436
  • 4.PNG
    4.PNG
    116.3 KB · Views: 414

rsingh40

Member
Hi arajan37,

Is it possible to share that .csv file. As when am trying to save data in note and rename to .csv all values are automatically converted in row and column. So if you can share we can do R&D and give you some solution
 
Hi Rajan,

in your situation you have to change the value of semicolumn delimiter in Import CSV code stage.

1. Goto Excel VBO Object
2. Goto Import CSV page
3. Open Code stage and goto code tab
4. there you will find "Textfilecommadelimiter = True <--- change this to false
5. there you will find "Textfilesemicolumndemiliter = False <-- change this to true
6. save it and refresh the process and run it

that's it
 

Attachments

  • csv.PNG
    csv.PNG
    5 KB · Views: 544

junjie

Member
hi bro
in case i have excel file and now want to save it as csv file.
if we do it in excel, we will open the file >> save as >> we select the csv(comma delimited) (.csv) >> save
when we open this csv file, all the data format remains the same as it is in the excel file.
so, i want to do like this with blue prism steps. (save excel to csv and data format remains unchanged)
can you please help advise which actions and steps to do ? (save file.xlsx as file.csv)
could anyone help me ... :(
 

junjie

Member
hello bro
now i can save the file excel into csv , and the data format remains unchanged. however, there's an error happening.
that is, when i open this csv file, there's a message popup saying: this file extension and format did not match ..., and i need to select yes to open it.
i don't want to see this error message :(

View attachment 1531584011469.png
 
Hi junjie,

for this you have to write a VB code in Code stage.

1. create a new page in Excel VBO object
2. add code stage
3. create 2 input parameter (Excel file full path with .xlsx or xls, CSV file full path with .csv)
4. in code paste below code
Code:
Dim ExcelApp As New Application
Dim ExcelWkb As Workbook

Try

ExcelApp.DisplayAlerts = False
ExcelWkb = ExcelApp.Workbooks.Open("filepath\filename.xlsx")
ExcelWkb.SaveAs(FileName:="Csvfilepath\CsvFileName.csv",FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
ExcelWkb.Close()
ExcelApp.DisplayAlerts = True
ExcelApp.Quit()

Catch ex as Exception
      Throw ex

End Try
 

junjie

Member
hi bro
the Excel VBO object is created by the service provider (of blue prism), and seem not allowed to make any change on this object.
is this the only way we can do ? or ...
 
Hi ,
you can create new page under Excel VBO object itself. if its not allowing you to create new page then you can create new object and use this new object in process
 

junjie

Member
the thing is this object is always got locked and edited by someone, always at any time, so i need to wait when i can open and save as another object :(
because it has many pages that if i create new it wont know how to do it (since it has initiate, action pages...in order for it to function)
 
Top