How to convert Excel to csv

V

VJR

Well-Known Member
Hi shilpareddy,

- Simply make a copy of the existing 'Save Workbook As' action tab of the MS Excel VBO.
- Rename it as 'Save Workbook As CSV'
- Go to the Code stage and modify the below marked in bold
wb.SaveAs(filename,6)

6 is the numeric constant for xlCSV FileFormat.
- Publish your new action
- From the process when you pass the 'Filename' Input parameter pass it with the .csv extension
"c:\MyFileName.csv"
- After you run the process you will see the Input/Output as below
1525405115248.png
 
J

junjie

Member
hi VJ
for the above, save the excel file into CSV format file
we do action stage Save workbook as , and how do we write in the Filename property ?
let's say the excel file filepath: C\Documents\staffmanagement.xlsx

how do we write in the filename to turn it (save it) into csv file ?

View attachment 1531546952826.png
 
V

VJR

Well-Known Member
junjie said:
hi VJ
for the above, save the excel file into CSV format file
we do action stage Save workbook as , and how do we write in the Filename property ?
let's say the excel file filepath: C\Documents\staffmanagement.xlsx

how do we write in the filename to turn it (save it) into csv file ?

View attachment 1371
Click to expand...
The above post already has the answers to both of your questions. Re-pasting them here below

1) "we do action stage Save workbook as"
No, we should not be using the 'Save Workbook As' action.
- Simply make a copy of the existing 'Save Workbook As' action tab of the MS Excel VBO.
- Rename it as 'Save Workbook As CSV'

2) "and how do we write in the Filename property ?"
- From the process when you pass the 'Filename' Input parameter pass it with the .csv extension
"c:\MyFileName.csv"
 
J

junjie

Member
one clarification:
the c:\MyFileName.csv , the MyFileName is the name of the file i'm working on it, right?
 
V

VJR

Well-Known Member
Yes, but with the extension .csv which will create another file on that location as .csv.
 
Y

YGC

New Member
Hi VJR, your solution works perfectly fine.

The only thing, when I transform my excel to xlCSV format (as I show in next picture). The last line ends with a comma. Is there anyway to avoid the last comma? or remove it using blueprism but without importing the excel file as a collection and looping line by line removing the last comma?

View attachment 1535616470490.png



Thank you very much in advance.
 
V

VJR

Well-Known Member
YGC said:
Hi VJR, your solution works perfectly fine.

The only thing, when I transform my excel to xlCSV format (as I show in next picture). The last line ends with a comma. Is there anyway to avoid the last comma? or remove it using blueprism but without importing the excel file as a collection and looping line by line removing the last comma?

View attachment 1804



Thank you very much in advance.
Click to expand...
Hi YGC,

How does the original Excel look like? Does it have any invisible character like a space or a tab in the last column because of which it is automatically adding a comma at the end?
 
Y

YGC

New Member
VJR said:
Hi YGC,

How does the original Excel look like? Does it have any invisible character like a space or a tab in the last column because of which it is automatically adding a comma at the end?
Click to expand...
Hi VJR,

The original excel look like in the picture attached here. I can't perceive anything unusual. Do you?


Could be the extra column?

-YG

View attachment 1535696840912.png
 
V

VJR

Well-Known Member
YGC said:
Hi VJR,

The original excel look like in the picture attached here. I can't perceive anything unusual. Do you?


Could be the extra column?

-YG

View attachment 1811
Click to expand...
Some bordered cell formatting seems to be visible in the column N. Perhaps data was present there earlier and was deleted later in which case Excel considers it as having data OR only bordering is present without any data. In any case if that entire column is deleted (not removing of formatting) and if you are still getting the last comma do post back.
 
  • Like
Reactions: YGC
Y

YGC

New Member
VJR said:
Some bordered cell formatting seems to be visible in the column N. Perhaps data was present there earlier and was deleted later in which case Excel considers it as having data OR only bordering is present without any data. In any case if that entire column is deleted (not removing of formatting) and if you are still getting the last comma do post back.
Click to expand...

Hi VJ,

That was! , I don't know how I couldn't see it earlier. You were right about the cell formatting in Column N. Now i have the csv file without the last comma.


Thank you very much for your quick response and everything I appreciated it a lot.

-YG.
 
A

asrobotic

New Member
VJR said:
Hi shilpareddy,

- Simply make a copy of the existing 'Save Workbook As' action tab of the MS Excel VBO.
- Rename it as 'Save Workbook As CSV'
- Go to the Code stage and modify the below marked in bold
wb.SaveAs(filename,6)

6 is the numeric constant for xlCSV FileFormat.
- Publish your new action
- From the process when you pass the 'Filename' Input parameter pass it with the .csv extension
"c:\MyFileName.csv"
- After you run the process you will see the Input/Output as below
View attachment 716
Click to expand...
Hi VJR,

I need to ask you something related to this case. I want to save workbook as "Text(Tab Delimited)" format. Do you know how can i do this ? I couldnt find the numeric constant for this format. Thank you for your help :)
 
K

kaaviya

New Member
VJR said:
Hi shilpareddy,

- Simply make a copy of the existing 'Save Workbook As' action tab of the MS Excel VBO.
- Rename it as 'Save Workbook As CSV'
- Go to the Code stage and modify the below marked in bold
wb.SaveAs(filename,6)

6 is the numeric constant for xlCSV FileFormat.
- Publish your new action
- From the process when you pass the 'Filename' Input parameter pass it with the .csv extension
"c:\MyFileName.csv"
- After you run the process you will see the Input/Output as below
View attachment 716
Click to expand...
Hi,

Can you please share the information about how to convert excel to macintosh csv in blue prism. i used the file format 22 which is the converting format number. but its not working. can you find any other way to convert into mcintosh format?.
 
K

kaaviya

New Member
Hi,

Can you please share the information about how to convert excel to macintosh csv in blue prism. i used the file format 22 which is the converting format number. but its not working. can you find any other way to convert into mcintosh format?.
 
P

Praveen.s9

New Member
asrobotic said:
Hi VJR,

I need to ask you something related to this case. I want to save workbook as "Text(Tab Delimited)" format. Do you know how can i do this ? I couldnt find the numeric constant for this format. Thank you for your help :)
Click to expand...
Hi ,

21 is the excel file format constant for ".txt"
 
R

RishabhJ396

New Member
Hi

Can you please share the information about how to convert Excel to PDF in Blue Prism. What number we can use in place of 6 for PDF.
"wb.SaveAs(filename,6)"
 
S

sahil_raina_91

Active Member

Attachments

  • Excel Save As PDF.zip
    100.3 KB · Views: 21
R

RishabhJ396

New Member
sahil_raina_91 said:
The wb.SaveAs() method does not support conversion to PDF.
Use ExportAsFixedFormat() method. Refer : https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.exportasfixedformat

If you are not comfortable with coding, follow the attached document that I found on the BP forums
Click to expand...
Hi

When I used the code mentioned in the file I got the below error.
Internal : Could not execute code stage because exception thrown by code stage: Exception from HRESULT: 0x800A03EC

Any suggestions on what could be the issue.
 
You must log in or register to reply here.
Top