Import CSV to Excel

[RitaReis_Cabrit]

Hi everyone! Hope that you guys can help me.

I have a file (with file type: 'file') that can be opened manually to Notepad. I want to extract that file information to a collection and text mining it. I tried to import it with CSV with MS VBO Excel and it split the data at ' , ' and I want it to split at ' ; '
Does anyone know how can I solve this problem?

PS: it's possible to open the file through excel and force the opening to a read-only file

Thanks a lot,
Rita Cabrita

------------------------------
Rita Reis Cabrita
------------------------------

[AndreyKudinov]

You will need to change (or better copy) excel vbo action and change delimiter in the code stage to ";"
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False < change to True
.TextFileCommaDelimiter = True < change to False
.TextFileSpaceDelimiter = False

"Import CSV" will be read only by definition - it doesn't open file, but imports its content

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

[PatrickAucoin]

Hi Rita,
Here are some thoughts in case you don't want to edit any code stages:
The Utility - File Management VBO has some actions that might help: Read All Text from File and/or Read Lines From File.
Also take a look at the Split Text action in the Utility - Strings VBO. This action lets you specify the delimiter.
Another (potentially messier) option would be to replace the semicolons with commas and then use Get CSV as Collection.

------------------------------
Patrick Aucoin
Senior Product Consultant
Blue Prism
------------------------------

[sumire]

How about replacing the separator?

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

[AndreyKudinov]

Problem with simply replacing separator is that semicolon is there for a reason - that most likely means comma is a decimal separator in a file and replacing semicolon to comma will only make things worse in most cases. This is actually valid, but hard to get right:

"Pi";3,14
"Oh,hi!; This is a ""simple"" string
with a line break!";0​

Excel would "open" it correctly on a PC with language/locale set to russian for example, but would fail on en_us, VBO action ("import text") would fail for other reasons (linebreak). Parsing it manually is a pain too. 

Also many 3rd party programs fail to generate a proper csv, so readin it becomes a problem. Excel doesn't like to quote strings for example.

CSV files can have many quirks (separator within a string, multiline strings, escaping..), so in the end - pick whatever works for your case.

p.s. Most compatible/reliable way in general would be to use some mature csv net library for parsing like csvhelper, but you'd need to create custom vbo for it and install dlls on resource PCs, so it is not a viable option in most cases. ​

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

[SAINNATHSURAY]

Hi mate , 

1.I am struggling with same issue , I have tried the above method but I am getting an error .
"Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) "

2.I have tried another way 
Read all text from file" is in utility-file management
"get csv as collection" is in utility-strings

unfortunately my CSV have duplicate columns  so it is throwing error in 2 way

------------------------------
SAINNATH SURAY
Business process Lead
TATA CONSULTANCY SERVICE
Asia/Kolkata
------------------------------

[PvD_SE]

Hi S,

Duplicate column headers is always gonna be a problem when importing a CSV. But if my memory serves me right, you can state that the CSV to be opened does not have column headers, in that case any duplicate headers will be ignored and you will get default headers in style with Column1, Column 2 etc. You can rename these if you like after loading the CSV.

About the delimiter, I suggest to start playing with your 'Region settings' and its 'Additional settings' in windows and see if they could be tinkered to whatever it is you got in your CSV file.

------------------------------
Happy coding!
Paul
Sweden
------------------------------

[SAINNATHSURAY]

Hi Paul ,

Can you little bit elaborate the solution please.

Thanks,
Sainath.


------------------------------
SAINNATH SURAY
Business process Lead
TATA CONSULTANCY SERVICE
Asia/Kolkata
------------------------------

[PvD_SE]

Handle duplicate column headers from a CSV file:
If you have a CSV that will have duplicate column headers and use this action:

...and set the flag 'First Line Is Header' to True, it will crash due to duplicate column headers. If you set it to False, it will assume the first row to be data and not crash as a result of the duplicate column headers. Of course you still have to remove the first row and this was not meant to be data. 
Note that it could also be a problem if the column headers in the CSV contain illegal characters. From what I've experienced, a dot '.' in a column header is not allowed. There are likely more illegal chars.

CSV delimiters:
In my part of the world, delimiters in CSV files can either be a comma och a semicolon. If you check your windows settings for your region: 
Region settings>>Additional date, time & regional settings>>Change date, time, or number formats>>Additional settings
There you'll find your windows preference for correct CSV build and split-up. On my PC I have:

So, if I get a CSV file delimited by ; it will work fine, if I get a comma separated file instead, it will not work as intended.
At my place of work, we specify to the sender of the file what format and delimiter we expect and we reject alternatives. When validating an incoming CSV file, our process will first read the first row, check if it contains our preferred delimiter and either approve or reject it. 
When correctly formatted, the CSV will open in a multi column collection, incorrectly formatted, it will create only one column in the collection. Assuming there were multiple columns in the CSV of course.

------------------------------
Happy coding!
Paul
Sweden
------------------------------