June 6, 2006 at 8:37 pm
Hi,
I would love to have some suggestions about how to migrate data from Access 2000 to SQL 2005. The tables in current access database is all messed up.
For instance there is a table where a column named 'contact_info' has name, address, state, Zip, all in the same column separated by comma.
Any sugestions on how to split and transfer the data appropriately to 'address', ' state, zip etc columns in the new normalised table structure?
Since I am new on to the scene I am really confused about how to do the same.
Thanks
Shyam
June 7, 2006 at 11:33 am
Try exporting your table to Excel. It has some nice functions that allow splitting out data.
Example: Cell A1 has "Seattle, WA 98155"
To get city: =LEFT(A1,SEARCH(",",A1)-1)
2-letter State: =MID(A1,SEARCH(",",A1)+2,2)
5-digit zip code: =RIGHT(A1,5)
You may need to adjust formulas if data size or content varies. Once the data looks good, copy and PASTE VALUES in the same spot to translate the formulas to values. Then either re-import your spreadsheet to an Access table or into SQL Server.
Dan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply