Importing OLD data into NEW SQL Server DB with MS Excel

Importing HTML webpage tables into EXCEL

http://webdesign.about.com/od/tables/a/aa071502a.htm

Export data from SQL Server to Excel to get a pattern of data and header text

Make Excel file data.xls

Make sure the header text is same as in SQL server db
In one sheet paste data from sql server db, in other sheet put data from web imported excel table
Arrange all data from web imported excel into refined data under the sql server defined column headers.

Take care to name province as named in SQL Server DB Province table

Make sure to remove all -/ with nothing

Convert dates to text in ms access (Fixing Date_of_Entry)

Use text to columns command method
Insert 4 cols after Date_of_Entry col

Do text-to-columns





Insert another col

Use concatenate command


Paste Special the concatenated value


Format all cells as text

Replace header of Date_of_Entry with new col

Del all old cols

 

Make data.accdb Access DB (Fixing Food_Item)

The purpose is to streamline Food_Item due to erratic entries
This DB will fix the Food_Item entries
Make two tables


Paste from SQL Server DB Food_Item into Food_Item table of Access DB


Paste all erratic entries from data.xls data from html. It will be caught due to primary key enforcement.

Paste it anyway and then replace all erratic entries in next col with real name corresponding with Food_Item table

Sort it


Import refined data sheet of data.xls into the access db in table named refined data



Make sure every field is text



Import steps are saved

Create query

Create relationship in the query

Change query to update query

Write the query like this

Export the data




The date should be converted to MM DD YYYY format before inserting into sql server.

Note that first col G then F then H is selected to make it MM DD YYYY

Select data minus the headers

Its now ready to be pasted in SQL Server DB from Refined Data.xls!