The method Aveva suggests for migrating from an old Historian server to a new historian server is to transfer the old memory block files, then export the configuration on the hold historian and import it into the new historian, and from that point onwards the new historian will be the same as the old historian.
That’s a good way of doing things if you’re on a fairly clean system, but there are potential issues.
Aveva has changed the data format over the years, so you could have history in a number of formats — people who have dealt with one of the conversions know that you can end up having to use two extraction subsystems, in addition to potentially multiple versions over time for other older data.
Another issue with a highly dynamic system with lots of programming changes is that every change can cause a new “tag” to be created. Over time, the data is consistent, but you have hundreds of thousands or even millions of tags, and the historian needs to track the name changes over time. This can slow the historian when you’re trying to get longer trends and also make maintenance more difficult.
So for some people, you might want to export the data out of an old historian en masse and import it into a new, clean historian. This has been my plan for several years, but without an easy path to do so, I held off until we were also upgrading the hardware directly. From there, I could pull the data out of the old historian using the sqlcmd command, then write each tag into the Historian’s database.
Looking at the original historian, it contained 24 billion values collected over 19 years. Therefore, it could take a huge amount of time to transfer everything. Instead of pulling all the values for one tag at once, I limited the time span to a set number of days, and go through each tag. In that way, the entire historian is filled up little by little, and over the potentially weeks and months required to fill the new historian, the recent history is filled up and over time more and more distant history becomes available in case you need to get things spun up quickly.
One question I had which was answered in practice is whether the data gets dumped in today’s history file or if it gets filled in appropriate tags. In fact it turns out the historian will go back in time and create the appropriate files. One of my first successful imports was a single value from 2013 on a certain tag, and it created a new directory and tag files for that day in 2013. This is exactly what I wanted to see, brand new tags in a brand new data file.
The file format shown in the file is:
ASCII
|
DATA_IMPORT|LOCAL|SERVER LOCAL|FASTLOAD|DEFAULT
[Tagname]|ORIGINAL|[data point date]|[data point time]|EU|[data point scaled value]|[data point quality]The standard good quality data point is 192.
So it should be easy, right? No problem!
Problem!
There’s a part of this that you can’t see. You can’t see it in most text editors.
See, there are two ways to do a new line.
In Windows, traditionally it’s been two characters, Line Feed and Carriage Return. If you think about a command line like a typewriter, then it makes a lot of sense — you have to feed the paper to get to the next line, then you need to return the carriage to the beginning to continue tiping from the beginning of the next line.
In Unix, they don’t do a carriage return, only a line feed. This makes some sense as if you’re doing a line feed you’re pretty much always starting from the next line regardless.
Later on, Windows has aligned more with Unix, which is really nice in many ways, but does mean that their tools mostly operate as if we’re on Unix.
Wonderware is a product that has existed for a very long time. It was first developed in 1987. The product line has always been highly entangled with Windows, all the way back to Windows 3.1.
So, have you figured out the problem yet?
Wonderware’s tools are looking for a line feed and carriage return, and if it only sees a line feed, it considers the line to have not ended.
Error in processing the file: [Your filename]
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Imagine spending hour after hour trying to get a simple csv file to run, only to find out that the enter key was doing the wrong thing.
The way to see what form of enter your file is using is to download notepad++ and activate the “Show all characters” feature. It looks like a backwads p with no hole and two lines. It will show you the CR characters and the LF characters.
I ended up choosing to limit the length of time to 15 days, because the number of lines is quite large — at one line per second, you end up with 3600 lines per hour, approximately 10,000 lines per tag per day.
I used the Wonderware Query tool to pull the tag list out of the new historian (to prevent pulling data for legacy tags that aren’t used any longer).
One thing I did to reduce the overall time required is I split the project into 2 separate jobs: I cut the tags into discrete and analog tags, then ran the two scripts pointing at each tag list separately. The character of the data is quite different between the two: Other than a few heartbeat tags, most discrete tags don’t change very often. They can only go from fully off to fully on, whereas analog tags can continuously change a very small amount. This means that despite historizing approximately 3 times more discrete tags, the tags pull and import much more quickly.
Here is the final program (courtesy of chatgpt). You run it on the Historian you’re loading up. It uses the sqlcmd program installed by sql server (or sql server express) to pull the data and place it into the fastload folder. If you have many many files to transfer, I’d recommend using a hard disk drive or better yet a ramdisk, because it creates multi-gigabyte files over and over again. I used the program called OSFMount from Passmark Software to create a 10GB ramdrive, which ended up being oversized for my purposes. You could do it on an SSD, but for a long-lived historian with many values you’re going to be writing and rewriting values so much you’ll probably massively reduce the lifespan of your ssd.
https://www.osforensics.com/tools/mount-disk-images.html
# ===========================================================
# PowerShell: Backfill Historian via FastLoad in 15‑day chunks
# ===========================================================
# --- Configurable section ---
$server = "old historian hostname"
$database = "Runtime"
$tagListPath = "C:\HistorianExport\Extract\tags.txt"
$exportRoot = "C:\HistorianExport\Output"
$chunkDays = 15
$fastLoadFolder = "C:\Historian\Data\DataImport\FastLoad"
# FastLoad header parameters
$fastLoadUser = "Data_Import"
$timeMode = 1 # 1 = local time, 0 = UTC
$timeZoneName = "Server Local"
$missingBlockBehavior = 10 # 10 = original by tag name
$timeSpanReplacement = 0 # 0 = backfill to now
# Earliest date to backfill
$earliestDate = Get-Date "2006-01-01"
# Start from today and work backwards
$endDate = Get-Date
# --- Setup: ensure export & FastLoad folders exist ---
foreach ($dir in @($exportRoot, $fastLoadFolder)) {
if (-not (Test-Path $dir)) {
New-Item -Path $dir -ItemType Directory | Out-Null
}
}
# Load tags
$tags = Get-Content $tagListPath
while ($endDate -gt $earliestDate) {
# Determine this chunk's range
$startDate = $endDate.AddDays(-$chunkDays)
if ($startDate -lt $earliestDate) { $startDate = $earliestDate }
$startString = $startDate.ToString("yyyy-MM-dd")
$endString = $endDate. ToString("yyyy-MM-dd")
$fileStart = $startDate.ToString("yyyyMMdd")
$fileEnd = $endDate. ToString("yyyyMMdd")
Write-Host "`n=== Processing chunk: $startString to $endString ==="
foreach ($tag in $tags) {
# Prepare per‑tag subfolder
$safeTag = $tag -replace '[\\/:*?"<>|]', '_'
$tagFolder = Join-Path $exportRoot $safeTag
if (-not (Test-Path $tagFolder)) {
New-Item -Path $tagFolder -ItemType Directory | Out-Null
}
# Paths for this tag+chunk
$outputFile = Join-Path $tagFolder "$safeTag`_${fileStart}_${fileEnd}.csv"
$tempDataFile = "$outputFile.tmp"
# SQL query: fetch data for this tag in the chunk
$query = @"
SET NOCOUNT ON;
SELECT
'$tag' AS TagName,
CONVERT(varchar(23), DateTime, 121) AS DateTime,
Value,
QualityDetail
FROM v_History
WHERE TagName = '$tag'
AND DateTime >= '$startString'
AND DateTime < '$endString'
AND Value IS NOT NULL
ORDER BY DateTime;
"@
Write-Host "Exporting '$tag'..."
sqlcmd -S $server -d $database -E -Q $query -s"," -W -h -1 -o $tempDataFile
if (Test-Path $tempDataFile) {
$lines = Get-Content $tempDataFile | Where-Object { $_.Trim() -ne "" }
if ($lines.Count -gt 0) {
# Open StreamWriter: ASCII (no BOM) + CRLF
$stream = [System.IO.StreamWriter]::new(
$outputFile,
$false,
[System.Text.Encoding]::ASCII
)
$stream.NewLine = "`r`n"
# FastLoad header (3 lines)
$stream.WriteLine("ASCII")
$stream.WriteLine("|")
$stream.WriteLine(
"$fastLoadUser|$timeMode|$timeZoneName|$missingBlockBehavior|$timeSpanReplacement"
)
# Write each data line (7 fields, 6 pipes)
foreach ($line in $lines) {
$parts = $line -split ","
if ($parts.Count -eq 4) {
try {
$dt = [datetime]::Parse($parts[1].Trim())
$datePart = $dt.ToString("yyyy/MM/dd")
$timePart = $dt.ToString("HH:mm:ss.fff")
$valueFormatted = "{0:0.##############################}" -f [double]$parts[2].Trim()
$quality = $parts[3].Trim()
$valueLine = (
"$($parts[0].Trim())" + "|0|" +
"$datePart" + "|" +
"$timePart" + "|0|" +
"$valueFormatted" + "|" +
"$quality"
)
$stream.WriteLine($valueLine)
}
catch {
Write-Host "⚠️ Bad datetime in line: $line"
}
}
else {
Write-Host "⚠️ Malformed line: $line"
}
}
$stream.Close()
# Atomically move to FastLoad folder
$dest = Join-Path $fastLoadFolder ([IO.Path]::GetFileName($outputFile))
Move-Item -Path $outputFile -Destination $dest -Force
Write-Host "✅ Moved $([IO.Path]::GetFileName($outputFile))"
}
else {
Write-Host "🚫 No data for '$tag' in this chunk."
}
Remove-Item $tempDataFile -ErrorAction SilentlyContinue
}
}
# Step back one chunk
$endDate = $startDate
}
Write-Host "`nAll chunks complete!"
The end result of all this is a clean Historian with all your data (notwithstanding alarms, I’m not transferring them and I don’t really know how and didn’t really want to) — and it shows in certain easy metrics. For example, the historian configuration export for me was previously 77MB and took several minutes to export, but the new historian with the same tags took 7MB and about 20 seconds to export. That level of cleaning is meaningful for system performance, and should also be meaningful for reducing hiccups due to back-end complexity.
