cancel
Showing results for 
Search instead for 
Did you mean: 

Load or convert "semicolon-formatted" CSV into Excel XLSX

jmcalonge2
Level 2
Hi all. Simply, a mere technical question about the Excel VBO Object. We€™re dealing with a CSV exported file from an application that we need to use. The file is not a €œcomma-separated€ fields formatted file, but a €œsemicolon-separated€€¦. What to do? Three different approaches ( €œImport as CSV€, €œLoad CSV into Collection€ and €œImport Workbook (Fast)€ ) do not work properly. May be we could execute a code€¦ well, simply one single line, to convert the CSV into XLSX, but I can not find out how. See below a VBS sample code I€™ve found out there to see whether it€™s possible to use it anywhere into the Excel VBO. The relevant line is about: "TextToColumns". [BEGIN :: VBS] Dim myXL Const xlDelimited = 1 Const xlOpenXMLWorkbook = 51 Set myXL = CreateObject("Excel.Application") myXL.Visible=False For Each file In WScript.Arguments myXL.WorkBooks.OpenText file, , , xlDelimited, , , , , True 'Change the source path as needed. myXL.DisplayAlerts=False myXL.Sheets(1).Columns("A").TextToColumns myXL.Range("A1"), xlDelimited, , , , True 'semicolon-delimited myXL.ActiveWorkbook.SaveAs Replace(file, ".csv", ""), xlOpenXMLWorkbook 'Change the destination path as needed. myXL.DisplayAlerts=True myXL.ActiveWorkbook.Close False Next myXL.Quit Set myXL = Nothing [END :: VBS] Thanks in advance, as usual, for your help. - Arturo
3 REPLIES 3

SandroPaetzold
Level 3
Hi all, i have a problem very similar to Arturos. I have a CSV file with semicolon as a separator. If I open the file with a double-click on the icon, Excel opens everything correctly formatted. If I use the MS Excel VBO provided by BluePrism, Excel expects a comma as seperator. Is there a way to tell Excel to use the semicolon instead of a comma as seperator? I already tried to use my own CSV loader but unfortunatelly i run into a OutOfMemoryException. Maybe it is usefull to know, that the file has at least 150.000 rows with 76 columns. And there is no way to get this data in different chunks. I would realy appreciate any help. KR Sandro

EdritFranquiz
Level 3
Hi,    I have a similar problem, but my file has a different delimiter (^). I was trying to use the TextToColumn Method in a code stage but it is not working. Did you manage to solve this situation? Thanks!

NevilleWatson
Level 4
In the code stage itself there are boolean values to define the Delimiter text.