I have been wanting a CSV importer since Microsoft Lightswitch’s Importer with the ability to match the CSV Headers with the database field names.
My first attempt at this was Copy/pasted Excel results into Angular - Radzen IDE (Angular) - Radzen
I would like any feedback on better ways to do any part of the following code.
Some assumptions
The CSV file has a header record
You can use Visual Studio or code and MSSQL.
The code uses Blazor Server but could be adapted for WebAssembly.
Using MSSQL but should work for others.
Normally I would import my file into a staging table then use a stored procedure to put then in the final tables so I have fill control on what happens.
The table is setup to allow nulls on all the fields and an identity field as the primary key.
The outcome
Use the upload control to upload the csv .
Select the Database fields that you want to match with the CSV Header Fields
The Result imported data
Here are the steps.
-
Create a Database call it CSVImportExample
-
In the database create a Tables called Customers_Staging ,Customers and a stored procedure usp_CustomerUpdate
/****** Object: Table [dbo].[Customers] Script Date: 6/09/2021 7:27:34 pm ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[Gender] [nvarchar](50) NULL,
[Title] [nvarchar](50) NULL,
[GivenName] [nvarchar](50) NOT NULL,
[MiddleInitial] [nvarchar](50) NULL,
[Surname] [nvarchar](60) NOT NULL,
[StreetAddress] [nvarchar](50) NULL,
[City] [nvarchar](50) NOT NULL,
[ZipCode] [int] NULL,
[Country] [nvarchar](50) NULL,
[EmailAddress] [nvarchar](50) NULL,
[Age] [int] NOT NULL,
[Company] [nvarchar](50) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Customers_Staging] Script Date: 6/09/2021 7:27:34 pm ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers_Staging](
[Customer_StagingID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](50) NULL,
[GivenName] [nvarchar](50) NULL,
[MiddleInitial] [nvarchar](50) NULL,
[Surname] [nvarchar](60) NULL,
[StreetAddress] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[ZipCode] [int] NULL,
[Country] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
[EmailAddress] [nvarchar](50) NULL,
[Age] [int] NULL,
[Company] [nvarchar](50) NULL,
CONSTRAINT [PK_Customers_Staging] PRIMARY KEY CLUSTERED
(
[Customer_StagingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[usp_CustomerUpdate] Script Date: 6/09/2021 7:27:34 pm ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[usp_CustomerUpdate]
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Customers
( Title, GivenName, MiddleInitial, Surname, StreetAddress, City, ZipCode, Country,Gender, EmailAddress, Age, Company)
SELECT Title, isnull(GivenName,'n/a'), MiddleInitial, isnull(Surname,'n/a'), StreetAddress, isnull(City,'????'), ZipCode, Country, Gender, EmailAddress, isnull(Age,0), Company
FROM Customers_Staging
delete from [dbo].[Customers_Staging]
END
GO
- Create a Server Side Blazor application in Radzen called ImportCSVFiles
- Click on add a new data source
New
Select your database and Infer Schema (Make sure that Infer settings are all ticked)
Make sure that you select the stored procedure
Press Finish and close MyDataSources.
- Create a new CRUD Page from the table Customers
Then just keep pressing Next until you have created the Customer Page. Just to test if the data gets imported.
Run the project just to make the Blazor Project and then stop it.
- Next Open the Project in your c# editor.
Add a Class to the Models Folder calling Mappings (This is used for mapping the CSV fields to the Database fields)
Add the Following Code to the class and save it.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace ImportCsvFiles.Models
{
public class Mappings
{
public int Position { get; set; }
public string CSVField { get; set; }
public string DatabaseField { get; set; }
}
}
- Jump back into Radzen, no need to close the editor, then create an Empty Page call it “ImportFile” and make it the Start Page.
- In the Page Load Event add the following
You may need to change the Property type to IEnumerable<ImportCsvFiles.Models.Mappings>
What are these Variables used for
fieldNames holds the csv Header Record in a array.
ImportFields is the matching of the Header to the database fields.
csvFile is a string array split on the lines of the file.
BeforeFileisImported used to hide buttons .
The Load dialog should look like this
- Next add a Row with 2 Columns in it.
Set the 1st column to 4 and the 2nd to 4 as well.
Add the Upload control and a Button to the 1st column and a button to the 2nd
Name the First Button Import and the 2nd Cancel
- Go back to your Editor and open the folder Controllers and edit the UploadController.cs
Add the following to the using section
using System.Linq;
using System.Collections.Generic;
using ImportCsvFiles.Models.CsvImportExample;
using Radzen;
Now replace the Single file upload with the following code
// Single file upload
[HttpPost("upload/single")]
public IActionResult Single(IFormFile file)
{
string[] csvFile = null;
string[] headers = null;
List<Models.Mappings> Map = new List<Models.Mappings>();
//Get the Fields in the table Customer
var fieldNames = new Customer().GetType().GetProperties().Select(p => p.Name).ToList();
//Add a "None" to the start of the csv field list , this is used as the default value
fieldNames.Insert(0, "None");
try
{
//Code for importing a csv
if (file.FileName.EndsWith(".csv"))
{
using (var sreader = new StreamReader(file.OpenReadStream()))
{
// Read file into a string array
csvFile = sreader.ReadToEnd().Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
// Get the header row of the CSV file and put it in an array
headers = csvFile[0].Split(',');
}
int i = 0;
// Match CSV header to database Field names if possible
foreach (string line in headers)
{
Models.Mappings m = new Models.Mappings();
m.Position = i;
m.CSVField = line;
m.DatabaseField = fieldNames.Where(f => f.Contains(line)).DefaultIfEmpty("None").FirstOrDefault();
Map.Add(m);
i++;
}
}
////Future code to import a spreadsheet
//if (file.FileName.EndsWith(".xlsx"))
//{
//}
// Return values back to the program
return Ok(new { map = Map, fieldNames = fieldNames, csvFile = csvFile });
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
- Next go the Folder Pages and open ImportFile.razor.cs
Add the following code
public void getDatabaseFields(char separator = ',')
{
bool FirstLine = true;
foreach (string line in csvFile)
{
// Don't import Header
if (!FirstLine)
{
//
string[] values = line.Split(separator);
// New Customers_Staging record
Models.CsvImportExample.CustomersStaging table = new Models.CsvImportExample.CustomersStaging();
foreach (Models.Mappings field in ImportFields)
{
if (field.CSVField != "None")
{
switch (field.DatabaseField)
{
case "City":
table.City = values[field.Position];
break;
case "Country":
table.Country = values[field.Position];
break;
case "Company":
table.Company = values[field.Position];
break;
case "EmailAddress":
table.EmailAddress = values[field.Position];
break;
case "Gender":
table.Gender = values[field.Position];
break;
case "GivenName":
table.GivenName = values[field.Position];
break;
case "MiddleInitial":
table.MiddleInitial = values[field.Position];
break;
case "StreetAddress":
table.StreetAddress = values[field.Position];
break;
case "Surname":
table.Surname = values[field.Position];
break;
case "Title":
table.Title = values[field.Position];
break;
case "ZipCode":
table.ZipCode = int.Parse(values[field.Position]);
break;
default:
break;
}
}
}
_ = CsvImportExample.CreateCustomersStaging(table);
}
FirstLine = false;
}
//Run the stored procedure
_ = CsvImportExample.UspCustomerUpdates();
ClearFields();
}
public void ClearFields()
{
BeforeFileisImported = true;
csvFile = null;
ImportFields = new List<Models.Mappings>();
StateHasChanged();
}
- Save all files and go back to Radzen
In the Upload control on the ImportFile form.
Add to Url field: upload/single
Add to Accept: csv/*,.csv
Add to Visible : ${BeforeFileisImported}
Next go to Events and select Complete
Add Execute C# and add the following code
${ fieldNames} = (System.Text.Json.JsonSerializer.Deserialize<string[]>${event}.JsonResponse.RootElement.GetPropety("fieldNames").GetRawText())).ToList<string>();
${ImportFields} = (System.Text.Json.JsonSerializer.Deserialize<Models.Mappings[]>${event}.JsonResponse.RootElemet.GetProperty("map").GetRawText(), new System.Text.Json.JsonSerializerOptions { PropertyNamingPolicy = System.Text.Json.JsonNamingPolicy.CamelCase})).ToList<Models.Mappings>();
${ csvFile} = (System.Text.Json.JsonSerializer.Deserialize<string[]>(${event}.JsonResponse.RootElement.GetProperty("csvFile").GetRawText()));
${BeforeFileisImported}= false;
- To the Import Button
To the Visible property ${!BeforeFileisImported}
Go to Events and select Click
Add Execute C# and add the following code
getDatabaseFields(',');
-
To the Cancel Button
To the Visible property ${!BeforeFileisImported}
Go to Events and select Click
Add Execute C# and add the following code
ClearFields(); -
Next add a row below the last Row with a Columns in it.
Add a DataList to it.
To the Data property: Select ${ImportFields}
Open template designer (Green Above)
With it, open add a row and 2 columns to it, and set both to 6
To the 1st Column add a DropDown
Set the following property
Data: ${fieldNames}
Value: ${data.DatabaseField}
TextProperty:Name (need to Click on Data-bind this property and type it in)
ValueProperty:Name(need to Click on Data-bind this property and type it in)
To the 2nd Column add a Label
Set the property Text: ${data.CSVField}
Now End template editing
- Run it and happy importing
