Suppose that you have a simple Microsoft Access database that you need to migrate to another database such as MS SQL Server. You model the tables and relationships in the new database, and now you need to export the actual data from Access and import it into the new database. This is how I did it:
I began by exporting the data as CSV. I found the easiest way was to simply use copy/paste. First though, open each tables in Design View. There is a tab named “Lookup” at the bottom. Ensure that any lookup field is set to display “Value List” instead of “Table/Query”. Then you can view the table normally and copy all data to the clipboard (Ctrl+A, Ctrl+C) and paste it into a .csv text file saved with UTF-8 encoding.
Next I used PowerShell to transform the CSV data into to SQL statement. While doing this I also adapted the structure of the data. For example, some fields were renamed, and I also had multiple fields that I wanted to merge into one. Here is a generic version of the script I put together that you can modify to your own needs:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$dir = Split-Path $MyInvocation.MyCommand.Path $data = Import-Csv "$dir\Access\data.csv" -Delimiter "`t" -Encoding UTF8 $out = [System.IO.StreamWriter]::new( "$dir\provisioning.sql" ) $out.WriteLine("SET IDENTITY_INSERT Chemicals ON") $data | ForEach-Object { $sql = "INSERT INTO MyTable (Id, Name, Link, DateModified) values ({0}, '{1}', '{2}', getutcdate() )" -f $_.Id, $_.Name, $_.WebLink; $out.WriteLine($sql) } $out.WriteLine("SET IDENTITY_INSERT Chemicals OFF") $out.close() |
Note: Normally you let SQL Server generate the ID of key fields. In this case though, we already have ID:s from the Access database. Since these ID:s specify the primary-foreign key relations, it is important to keep intact. To allow insertion of keys like that, IDs SET IDENTITY_INSERT Chemicals ON is used.
Finally, open the generated SQL script (provisioning.sql) in SQL Server Management Studio (or similar) and run it to provision your data!
Hello, I’m sorry to write this here,
Today I was looking for a program in my PC and trying to remember its name my brain immediately said “Artgem” (I was actually thinking in ArtRage) and it made me remember those glorious days when I was a kid and I used your program to do amateur pixelated sprites for my really amateur private games.
It really was (or is) a great tool. I wonder why is discontinued instead of been made freeware?
I hope all the developers are doing great these days. Stay healthy my friends!