This video will walk through the process of transferring your extracted eCrash data into a SQL database
(though this premise can also apply to other database environments).
It serves as a starting point upon which you can then expand and apply to the data that best serves your needs.
If you need guidance on how to extract your crash data then please first view our “Exporting your eCrash Data” video.
Once a local database is established, your eCrash data can be exported to other internal
applications. An RMS or data reporting system are examples.
Additionally, be sure your machine has the SQLPS PowerShell module installed. You will need to
run Windows PowerShell as an administrator to accomplish this.
Without SQLPS, you will encounter errors when running one of the PowerShell scripts later in this video.
It is important to note that the extracted data and the databases must reside together on the same server or desktop machine.
The databases created will pull the data from a local folder.
Also, throughout the video we will be running a number of scripts. These will be available to download at the end of this training video.
In this example, we will be using SQL Server Express.
It has already been installed, so we are ready to begin creating our database.
I will copy and paste the “Create Database” script into SQL Server Management studio
and click “Execute”.
We receive a message that the database is not available to drop, because it does not already exist. If I expand the “Databases” section
of the tree, we see that the database has been created.
Next, we will create our tables.
Specifically, we will create the Crash, Vehicle, Driver, and Log tables.
Copy, paste and execute the scripts like we did before.
We receive a message that the commands completed successfully.
If I expand the “Tables” section of the tree
we see that our tables have been created.
We will now run a PowerShell script. This will provide a unique file name to each of the JSON files that we exported, from our “Exporting your
eCrash Data” video, and compile them into a single folder location.
Open the Windows PowerShell app
or Command Prompt.
If opening a command prompt, type “powershell”, so the following scripts will be recognized and run.
We will start by providing the location of the JSON exports
and the folder where we ultimately want the renamed files placed.
My source is located in the C drive folder, “Example Reports”.
I’ll highlight and copy the location. I’ll overwrite the example location in the script.
I’ll do the same with the final location of the renamed files.
We can copy and paste these two lines into our powershell window
and press “Enter”.
Next, copy and paste the next section
and press “Enter”.
In the last section, copy and paste this entire line into our PowerShell window
and press “Enter”.
Looking in our destination folder, we see the files have been renamed
and are all in the folder.
The next step is also a PowerShell script.
Here too, we will need to replace the location referenced with the location that we chose.
Copy the location from Step 3 and paste it into Step 4 to replace the example’s location.
I’m going to add double quotations, because I have a space in the folder’s name.
Otherwise, the script will not run properly.
Overwrite the “SQL Server Instance Name”
with the name of your installed instance.
Now, highlight the script,
copy it,
return to your PowerShell window,
paste,
and press “Enter”.
The next script requires that the location
of your original data extraction be entered over “location of original extraction”.
This is the “source” location from Step 3.
Copy the file path,
go back to the “Step 5” script, and paste this copied path.
Now copy the script and go back to Management Studio.
Click “New Query”, paste the script, and click “Execute”.
“1 row affected” displays in the Messages section
and a timestamp will be loaded into the log table.
As a side note:
If you ever forget to run an export of your data, and you deleted the RowVersion text file
then query this BatchLog table to find the last row pulled from the state repository. You can recreate this text file with the last row pulled
and then run the data extraction process to pull only data from the last extraction to now.
Finally, the data will be inserted into the database tables.
Copy everything in the example script
and paste it into Management Studio.
Click “Execute” and the crash data is loaded into SQL.
The data that we pulled from the JSON files are now in the SQL database.
The tables created are just examples of the data that can be loaded. Please use these scripts, available for download, and modify
them to
create other database tables and load other crash data elements as needed.
Thank you. This completes the overview and examples
of the eCrash data transfer process.