Hi,
I have a large Excel file (65 columns) which i would like to upload to a Sharepoint list using ‘Add list items’.
I’m reading the Excel into a datatable and then using ‘add list items’ - It works when there are no blank values on the dataset.
when the original excel has blank cells - adding to the sharepoint list fails.
I tried using Linq to replace all blank values with [something] but my problem is that the blank values can be on a String column, an int one, or a date one.
this means i need to change the below query to say:
If the blank cell is on a string col >> Replace with “NA”
If the blank cell is on an int col >> Replace with 0
If the blank cell is on a date col >> Replace with a date ‘1/1/1900’
This is the code I’m currently using:
(From d In MyListToUpload2.AsEnumerable()
Select ia=d.ItemArray.ToList
Let ra = ia.ConvertAll(Function (x) If(isNothing(x) OrElse String.IsNullOrEmpty(x.toString), “na”, x)).toArray()
Select rc = dt_Corrected.Rows.Add(ra)).CopyToDataTable()
hi @Adi_Lupashko
use the below query to replace null with zero
syntax:
(From r In dtData.AsEnumerable
Let ra = r.ItemArray.Select(Function (e) If(isNothing(e) OrElse String.IsNullOrEmpty(e.toString), 0, e)).toArray
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()
@Adi_Lupashko
You can convert the datetime column into string and replace it with
syntax : (From r In dtData.AsEnumerable
Let ra = r.ItemArray.Select(Function (e) If(isNothing(e) OrElse String.IsNullOrEmpty(e.toString), “1/1/1990”, e)).toArray
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()
First thing …if it is datetime column them you wont have blank at all…may be the the column is taken as string only…you first either need to convert the column type to datatime…or later convert eqch item separately using logic which is tedeous for 64 columns
I think what I’m failing to explain is that i have many columns of all types and i need a solution for all types…
Dataset Example:
EmployeeID Name Hire Date Termination Date Salary ZipCode Organization
1133 Adi 1/1/2020 10,000 656565 ABC
4422 Omer 5/3/2021 3/3/2022 15,000
The end result should be:
EmployeeID Name Hire Date Termination Date Salary ZipCode Organization
1133 Adi 1/1/2020 1/1/1900 10,000 656565 ABC
4422 Omer 5/3/2021 3/3/2022 15,000 0 NA
Assign dtOutput = (
From a In dtExcel
Let EmployeeIDColumn = If(String.IsNullOrEmpty(a("EmployeeID").ToString) Or String.IsNullOrWhiteSpace(a("EmployeeID").ToString), "N/A", a("EmployeeID").ToString)
Let NameColumn = If(String.IsNullOrEmpty(a("Name").ToString) Or String.IsNullOrWhiteSpace(a("Name").ToString), "N/A", a("Name").ToString)
Let HireDateColumn = If(String.IsNullOrEmpty(a("Hire Date").ToString) Or String.IsNullOrWhiteSpace(a("Hire Date").ToString), "1/1/1900", a("Hire Date").ToString)
Let TerminationDateColumn = If(String.IsNullOrEmpty(a("Termination Date").ToString) Or String.IsNullOrWhiteSpace(a("Termination Date").ToString), "1/1/1900", a("Termination Date").ToString)
Let SalaryColumn = If(String.IsNullOrEmpty(a("Salary").ToString) Or String.IsNullOrWhiteSpace(a("Salary").ToString), "0", a("Salary").ToString)
Let ZipCodeColumn = If(String.IsNullOrEmpty(a("ZipCode").ToString) Or String.IsNullOrWhiteSpace(a("ZipCode").ToString), "0", a("ZipCode").ToString)
Let OrganizationColumn = If(String.IsNullOrEmpty(a("Organization").ToString) Or String.IsNullOrWhiteSpace(a("Organization").ToString), "N/A", a("Organization").ToString)
Select dtOutput.Rows.Add(EmployeeIDColumn, NameColumn, HireDateColumn, TerminationDateColumn, SalaryColumn, ZipCodeColumn, OrganizationColumn)
).CopyToDataTable
As you can see, you might do this with the 63 columns you mentioned, it will be time consuming but I think it will do the requirement you’re looking for.
Sample output from the table example you presented.
Thanks! this looks like what i need but dont think it will be efficient to do it for 65 columns…
Do you think i can do this only for one\two date columns and then keep all other columns? instead of selecting one by one? If i take your code for the date columns, and then do something else to replace all the numbers and strings - it might be more efficient.
(
From a In dtExcel
Let TermDateCol = If(String.IsNullOrEmpty(a(“Column48”).ToString) Or String.IsNullOrWhiteSpace(a(“Column48”).ToString), “1/1/1900”, a(“Column48”).ToString)
Select dtOutput.Rows.Add(.TermDateCol) [The "" here is obviously not working…]
).CopyToDataTable
you can dynamize it e.g. by a lookup dictionary and selecting a default value based on the data column datatype.
But keep in mind as also mentione above. There are more disturbing factors to expect and we would suggest to setup a dynamic approach along with a driving data schema
But I wouldn’t necessarily advise a solution that uses For Each or Loop especially when you are processing hundreds of thousand of transaction rows and as you mentioned 65 columns in a Data Table. It might cause a longer processing time and worse case a ‘job stopped’ as we experienced from our previous projects. But if you are only dealing with less than a 100,000 transaction rows, I think this approach is still safe.
New Dictionary(Of Type, Object) From {
{GetType(String),"NA"},
{GetType(int32), 0},
{GetType(DateTime), New DateTime(1900,1,1)}
}
cloning the dtData:
dtResult = dtData.Clone
Filling up:
dtResult =
(From d In dtData.AsEnumerable()
Let ra = d.ItemArray.Select(Function (x,i) If(isNothing(x) OrElse String.IsNullOrEmpty(x.toString().Trim()), dictLKDefaults(dtData.Columns(i).DataType), x)).toArray()
Select r = dtResult.Rows.Add(ra)).CopyToDataTable
Result:
This prototype can also be advanced to:
by forcing schema definitions and conversions
decomposition to essential activities
We would not deny to use also for each / Parallel For each activities, as the general processing costs are to rate and not the usage of a for each activity
I also noticed that you are processing same Data Table on the LINQ. You have to use different Data Table for the Output, if you don’t I think you will get an error.