topic You need to have something… in Product Forum

Import CSV with data type text and semicolon delimiter

JeroenOosterhol — Wed, 16 Jan 2019 13:46:00 GMT

I'm importing a CSV file using a semicolon delimiter, which works successfully. However, I would like to use data type Text during the import, as the data contains "00000" values that are transformed to "0" during import. How can I achieve this? I am trying to use TextFileColumnDataTypes, but I'm not sure how to specify it. Alternatively, I could work with a text to columns if the solution is easier. The code used is: Dim dw, ds, dr, qt As Object Try dw = GetWorkbook(Handle, Workbook) ds = GetWorksheet(Handle, Workbook, Worksheet) dr = ds.Range(Range) qt = ds.QueryTables.Add(Connection:="TEXT;" & Path, Destination:=dr) With qt .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = 1 'xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = 1 'xlDelimited .TextFileTextQualifier = Qualifier .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileTrailingMinusNumbers = True End With qt.Refresh(False) Success = True Catch e As Exception Success = False Message = e.Message Finally dw = Nothing ds = Nothing dr = Nothing qt = Nothing End Try

You need to have something…

AmiBarrett — Sat, 19 Jan 2019 01:16:00 GMT

You need to have something that defines the column with typeof(String) Here's a C# action I wrote that'll handle CSVs with custom delimiters.

Inputs:
Delimiter - Text
Target - Text
headers - Flag

Outputs:
Collection - Collection

var file = Target;
var parser = new TextFieldParser(file);
var dt = new DataTable();
parser.SetDelimiters(Delimiter);
string temp = "";
int count=0;
while(!parser.EndOfData)
{
   if(count==0)
   {
	   string[] columns = parser.ReadFields();
	   if(headers)
	   {
		  foreach(string title in columns)
		  {
			string colName = title;
			int num=0;
			while(dt.Columns.Contains(colName))
			{
				colName = string.Format("{0}_{1}",title,++num);
			}
			dt.Columns.Add(colName, typeof(String));
			temp+=title;
		  }
	   }
	   else
	   {
		   int fields = columns.Length;
		   int colCount=1;
		   while(colCount<=fields)
		   {
			   string colName="F"+colCount.ToString();
			   dt.Columns.Add(colName, typeof(String));
			   colCount++;
		   }				   
		   var dataTableRow = dt.NewRow();
		   dt.Rows.Add(columns);
	   }
   }  
   else 
   {
		var dataTableRow = dt.NewRow();
		dataTableRow.ItemArray = parser.ReadFields();
		dt.Rows.Add(dataTableRow);
	   //csvData.Rows.Add(fieldData);
   }  
   count++;
}
Collection = dt;
row = temp;

I have been looking…

stefan.hansen — Wed, 30 Jan 2019 14:47:00 GMT

I have been looking everywhere for this!! I really need to be able to import CSV to collection and specify which character splits the data. But how do I use the code you wrote? If I make a new Object, make a new page on that object where I want to execute the code, make the data items/collection required, make a code stage with the input and outputs that you specified and copy/paste the code you wrote. Then I get 97 errors. It's like it can't use the language you wrote the code in - am I missing something?

Ahh - just found it! At the…

stefan.hansen — Wed, 30 Jan 2019 15:09:00 GMT

Ahh - just found it! At the Initialize page settings - Code options - In the buttom I could choose code language. BUT I still get this error: Page: Import CSV to collection Stage: Import CSV Type: Error Action: Validate Description: Compiler error at line 2: The type or namespace name 'TextFieldParser' could not be found (are you missing a using directive or an assembly reference?) Repairable: No @amibarrett - can you help?

I've got this action in the…

AmiBarrett — Fri, 01 Feb 2019 20:34:00 GMT

I've got this action in the middle of a bunch of other custom collection manipulation actions, so I'll just put all of the includes I put on the object. It's a bit of a shotgun blast and may import more than you actually need, but it should fix it. Please note that the file path has no quotes, and that the language is still C# despite the external ref and namespace for VB.

External Refs:
System.dll
System.Data.dll
System.Xml.dll
System.Drawing.dll
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Core.dll
Microsoft.VisualBasic.dll

Namespace Imports:
System
System.Drawing
System.Data
System.IO
Microsoft.VisualBasic
System.Collections.Generic
System.Text
System.Linq
Microsoft.VisualBasic.FileIO

@amibarrett I have tried to…

ChristianJuhl — Tue, 19 Feb 2019 22:01:00 GMT

@amibarrett I have tried to get this to work. I fixed all compile errors by adding the includes you have, but when I run the code stage the output is not written to the collection. It simply remains empty. Any ideas?

Found the problem! Looks…

AmiBarrett — Wed, 20 Feb 2019 02:31:00 GMT

Found the problem! Looks like this was an older pre-bugfix build I wrote while in my previous division. place int count=0; just before the while loop and it should resolve. Sorry about that!

That did the trick. Thanks!

ChristianJuhl — Wed, 20 Feb 2019 13:33:00 GMT

That did the trick. Thanks!

RE: I've got this action in the…

Nannevan_der_Wa — Fri, 11 Oct 2019 12:19:00 GMT

Hi Ami,

I have a semicolon delimited CSV and was very happy to read you built a C# function for it. I made the action and changed the language to C# but I am lost when it comes to the 'includes on the object' that need to be imported you are talking about. Could you explain in a bit more detail what needs to happen before this code will work?

Hope you can help!

Best,
Nanne

------------------------------
Nanne van der Wal
Advisor
EY
Europe/Amsterdam
------------------------------

RE: I've got this action in the…

AmiBarrett — Fri, 11 Oct 2019 15:41:00 GMT

When the forum migrated to the current software, it broke the formatting of a bunch of old posts. I've edited it for readability.

In the same place where you changed the code to C#, you should be able to modify the External References and Namespace Imports with the list in the previous post.

------------------------------
Ami Barrett
Lead RPA Software Developer
Solai & Cameron
Richardson, TX
------------------------------

RE: I've got this action in the…

Nannevan_der_Wa — Mon, 14 Oct 2019 09:01:00 GMT

Thanks! You are a hero Ami, works like charm :D.

A small note, I got an error on the last line: "row = temp;". I just deleted it to test and then the code worked.

------------------------------
Nanne van der Wal
Advisor
EY
Europe/Amsterdam
------------------------------

RE: That did the trick. Thanks!

bem — Mon, 27 Apr 2020 12:23:00 GMT

This is great but can you somehow set a filformat into the code? I have issues with Æ,Ø and Å. I can get my file out with , delimitator but that can't be used, it need to be a ; delimitator and here I donno if I somehow could set the filformat in also its a utf-8 format I need.

------------------------------
Berit Mogensen
Robotics developer
SE Group Service A/S
Europe/Copenhagen
------------------------------

RE: That did the trick. Thanks!

AmiBarrett — Mon, 27 Apr 2020 15:34:00 GMT

It actually defaults to UTF-8. You can add another argument after the file path if you want to change the encoding, though. https://docs.microsoft.com/en-us/dotnet/api/microsoft.visualbasic.fileio.textfieldparser.-ctor?view=netcore-3.1 I suppose it's possible that BP is doing something to the dataTable internally before it becomes a collection, but that's pure hypothesis.

As for semicolons, those should be accepted with that code snippet.

------------------------------
Ami Barrett
Sr Product Consultant
Blue Prism
Plano, TX
------------------------------