Revindex Storefront spots a powerful REST API for selecting, inserting, updating or deleting almost any data. The REST API has a more extensive support for manipulating bulk data than the regular CSV import on the screen. Please see Import and Export for more information.

However, because the REST API operates on XML or JSON and is intended for programmers, it's not easy to manipulate the data by non-technical users. An easier way is to allow users to manipulate the data from a CSV (Excel spreadsheet) and make use of other tools to transform the CSV into XML for the REST API.

You can use a Powershell script to take a CSV file and convert it to XML suitable for the REST API to work. The command line can be run from any computer or scheduled to import the CSV file. Since internally it uses the REST API, it could call any Insert, Update or Delete operations that the REST API can perform. Because internally it calls the REST API, it benefits from validation check as well as the ability to operate on almost every field.

# CsvImport.ps1 v1.0.0
#
# This script will bulk execute any API service you specify (insert, update and delete operations)
# using parameters and repeating for every record in your CSV file.
# The CSV file must include first line headers matching exactly the parameter list for the requested service.
# Remember to take a full backup of your system before performing any API operation.
#
# Example 1. To bulk execute the "InsertProductAttribute" API service, you need to provide a parameter CSV file like this (ideally with quotes):
#
# "booleanValue","decimalValue","integerValue","productAttributeDefinitionID","productID","productVariantID","selectionValue","stringValue"
# "TRUE","[NULL]","[NULL]","12","7","[NULL]","[NULL]","[NULL]"
# "[NULL]","[NULL]","[NULL]","15","[NULL]","8","[NULL]","[NULL]"
#
# Then run the command line below to execute the API service for each record in your CSV file:
#
# > &"C:\CsvImport.ps1" 
#        -APIKey 'xxxx' -APIService 'InsertProductAttribute' -APIUrl 'http://url/...'
#        -APIUsername 'admin' -ParamsFile 'C:\Params.csv'
#
######################################################################

# Configuration
######################################################################

param
(
    [parameter(Mandatory = $true)][string]$APIKey,
    [parameter(Mandatory = $true)][string]$APIService,
    [parameter(Mandatory = $true)][string]$APIUrl,
    [parameter(Mandatory = $true)][string]$APIUsername,
    [bool]$ExitOnError = $true,
    [parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)][string]$ParamsFile,
    [int]$NetworkTimeout = 30000,
    [string]$NullString = "[NULL]",
    [bool]$Silent = $false
)

# Functions
######################################################################


# Function to help post HTTP request to web service
Function PostWebRequest([String] $url, [String] $data, [int] $timeout)
{    
    $buffer = [System.Text.Encoding]::UTF8.GetBytes($data)
    [System.Net.HttpWebRequest] $webRequest = [System.Net.WebRequest]::Create($url)
    $webRequest.Timeout = $timeout
    $webRequest.Method = "POST"
    $webRequest.ContentType = "application/x-www-form-urlencoded"
    $webRequest.ContentLength = $buffer.Length;


    $requestStream = $webRequest.GetRequestStream()
    $requestStream.Write($buffer, 0, $buffer.Length)
    $requestStream.Flush()
    $requestStream.Close()


    [System.Net.HttpWebResponse] $webResponse = $webRequest.GetResponse()
    $streamReader = New-Object System.IO.StreamReader($webResponse.GetResponseStream())
    $result = $streamReader.ReadToEnd()
    return $result
}


# Function to send email
Function SendEmail([String]$smtpServer, [String] $smtpUser, [String] $smtpPassword, [String] $sender, [String] $recipient, [String] $subject, [String] $body, [String] $attachment)
{
    $msg = New-Object System.Net.Mail.MailMessage
    $msg.From = $sender
    $msg.ReplyTo = $sender
    
    foreach ($r in $recipient.Split(';'))
    {
        if ($r)
        {
            $msg.To.Add($r)
        }
    }
    $msg.subject = $subject
    $msg.body = $body
    
    if ($attachment -and [System.IO.File]::Exists($attachment))
    {
        $att = New-Object System.Net.Mail.Attachment($attachment)
        $msg.Attachments.Add($att)
    }


    $smtp = New-Object System.Net.Mail.SmtpClient($smtpServer)
    $smtp.Credentials = New-Object System.Net.NetworkCredential($smtpUser, $smtpPassword);
    $smtp.Send($msg)
}


# Start program
######################################################################

Try
{    
    # Load CSV parameters from file into memory
    if (![System.IO.File]::Exists($ParamsFile))
    {
        return
    }
    
    $ImportData = @(Import-Csv ($ParamsFile))
        
    # Get list of parameter names from our CSV header line
    $ImportHeaders = $ImportData | Get-Member -MemberType NoteProperty | foreach {$_.name}
    
    # Execute API service for each parameter record in CSV file
    $line = 0
    $successCount = 0
    foreach ($Row in $ImportData)
    {
        Try
        {
            $line++
            
            # We need to construct our XML request using the parameter list
            $strRequest = "<?xml version='1.0' encoding='utf-8'?>
                            <request>
                              <version>1.0</version>
                              <credential>
                                <username>$APIUsername</username>
                                <apiKey>$APIKey</apiKey>
                              </credential>
                              <service>$APIService</service>
                              <parameters>
                              "
            
            foreach ($Param in $ImportHeaders)
            {
                $v = $Row | Select –ExpandProperty $Param
                if ($v -ne $NullString)
                {
if ($v.StartsWith('<locale ') -or $v.StartsWith('<code ') -or $v.StartsWith('<rule '))
{
                    $strRequest += "                          <$Param>" +  $v + "</$Param>`r`n"
}
else
{
$strRequest += "                          <$Param>" +  [System.Web.HttpUtility]::HtmlEncode($v) + "</$Param>`r`n"
}
                }
            }                 
            
            $strRequest += "                          </parameters>
                            </request>"
            
            # Execute the API call
            $xRequest = [Xml] $strRequest
            [Xml]$xResponse = PostWebRequest $APIUrl $xRequest.InnerXml $NetworkTimeout
            if ($xResponse.response.code -ne '2000')
            {
                if (!$Silent)
                {
                    Write-Host "Error executing $APIService while processing record number $line. Response: " + $xResponse.response.code + ' ' + $xResponse.response.message
                }
                
                if ($ExitOnError)
                {
                    return
                }
            }
            else
            {
                $successCount++
            }
            
            if (!$Silent)
            {
                Write-Host "$successCount / $line records successfully executed."
            }
        }
        Catch
        {
            if (!$Silent)
            {
                Write-Host "Error executing $APIService while processing record number $line. " + $_.Exception.Message
            }
        
            if ($ExitOnError)
            {
                return
            }
        }
    }
}
Catch
{
    if (!$Silent)
    {
        Write-Host $_.Exception.Message
    }
}

For example, it can call the "InsertProductAttribute" service. Please see ProductAttribute for more information. All you need to do is make sure your CSV file header follows the request parameter list of that service call (double quotes around CSV fields are optional but highly recommended). 

"booleanValue","decimalValue","integerValue","productAttributeDefinitionID","productID","productVariantID",...
"TRUE","[NULL]","[NULL]","12","7","[NULL]",...
"[NULL]","[NULL]","[NULL]","15","[NULL]","8",...

You just need to enable the API under Configuration > API menu after logged in as Admin or Host. Then run the command line with the correct parameters specifying the operation you want to do and the location of your CSV file:

&"C:\CsvImport.ps1"  -APIKey 'xxxx' -APIService 'InsertProductAttribute' -APIUrl 'http://url/...' -APIUsername 'admin' -ParamsFile 'C:\Params.csv'


Remember to take a full backup before doing any major import.