Read CSV inputting empty strings instead of nulls

Help
, ,
1

Hello, i have a sequence that includes a Read CSV activity, once the .csv is read i am looping through each record, adding it a datatable doing some validation and inserting each row into a SQL Server DB using the insert activity. The issue is that if there is an empty cell in the .csv, it gets inserted as as empty string instead of a null. I saw some people doing some if statements to check if a row includes an empty string then changing it to a null. My question is, is there a cleaner way to handle this?

Thanks.

1 Like
2

dont think there is…

well, unless you want to create a stored procedure and do all validation and cleaning in there…

1 Like
3

Hi
Use a assign activity before passing the string variable to sql
If stored in a variable named strinput
Then in assign activity

strinput = IF(string.IsNullOrEmpty(strinput.ToString),Nothing,strinput)

And if then pass this strinput to sql
Cheers @zspilman

1 Like
4

@Palaniyappan I am not writing raw SQL that i can manipulate, i am using UIPaths Insert activity that inserts a datatable for each record. @bcorrea after implimenting the proccess that we discussed i realized that it is way too slow to go through each column and then each row and replace the empty strings with a null (Nothing) value. There must be a better way. Maybe it is better to just input it as SQL and use @Palaniyappan 's answer and not use the insert activity.

1 Like
5

Yah once after changing or assign in that.l variable with value either as nothing or with its own value l, we can pass that as input parameter to SQL INSERT Statement in EXECUTE NON QUERY ACTIVITY

cheers @zspilman

6

@Palaniyappan @bcorrea I found a better solution. Read the csv as a txt file and replace all the “” (empty strings) with Nothing (null) then build the datatable from the string with the generate datatable activity. You can replace all the empty strings with null values in one quick action here instead of having to iterate through potentially thousands of records and replace each one. The only issue is that if the generate table activity decides a column is a string it will put empty strings by all the null values but this is good for my other data types ie datetimes, integers, etc. i think this i a bug with @uipath and should be fixed as empty cells should be nulls not empty strings.

7

im glad you found something that works for you, did you replace like this?

FROM ,, TO ,null,

Still i dont think it is a bug, it is default csv formatting…

8

@bcorrea No an emty string in csv is ,“”,…i did csvString.Replace(“”“”“” , nothing). i understand that the csv format is default to empty string but why does the datatable have to reflect that perfectly. I can see many applications of this activity that one would be inserting into a database and its a shame to waste all that space with empty strings, it should be a choice made by the developer not UIPath.

9

csv do not have strong types, so if you want to force empty text then you are right string would be 2 quotes, also if you want to force null for any column you would need to not have ,,…

10

@bcorrea Yes after replacing the empty string values in my csv all the values are in good order but when I build by datatable based on that csv file to insert it into the DB, if the column type is of type String it turns all my null values right back to empty strings the issue is in the “Generate Data Table” action not in the csv file itself.

11

i understand, but for me this is the standard behavior for csv and string type… the only way to be null is to write that… you should imagine the other way around… if you NEEDED the empty string instead of the null and then you would need to write the quotes…

12

@bcorrea I understand which is why it should be a choice in the activity weather you want nulls or empty strings, why do Data Tables have to be coupled with csv’s? In programming standard behavior of string type is not by default empty string…they are null until you define them just like any other datatype.