How to automatically create a target table for a data import set?

[raking08]

I have been asked to migrate an existing (and very complex) structured knowledge repository to ServNow ( and this is my first exposure to SN so please forgive a dumb question). I realize that I will need to create the main data tables one column at a time from scratch in order to have all the proper configuration (7 tables 50-120 columns each lots of ref fields)

BUT I have another 170 tables that I have in Excel that I can load to an import set as they are more simple ( no reference fields etc) These each have 10-70 columns each and all unique. These will only be referenced by records in the main data tables.

I need to transform these to   a set of 170 tables ( a one time exercise) What I hope to do is to automatically create the custom target table from the individual import sets.

Is this possible?

Is there a better way?   Can MS Access be used to create tables via ODBC Connection? (For instance I can create these kinds of table directly from MS Access in SharePoint in one click)

Many Thanks

R

[raking08]

this is close to what my fried developed and is the easiest and lowest risk way to handle this special situation as long as the skill set is available.

thx

Richard

[raking08]

I also meant to say and security access to run this script. r

[Mukesh Sharma]

this can be done in 2 Steps .

1. First you have to Create Excel file which will include list of all tables name and values. This Excel you have to import in sys_db_object table.

2. Second you have to Create another Excel file which will include list of all columns . including name, value,table name , type etc. This excel you have to import in sys_dictionary_list table.

Please Note : By doing this way, you might loose some basic properties of table's and columns. I would suggest you first try for 5 or 10 tables , if it works, you can do for the rest.

Thanks

Mukesh Sharma

[gpopp]

Hi Mukesh,

I tried your solution to create one table like this. (I had a lot of fields from another database). So I initially created the table by hand (table->new) but did not add any fields. I then did your step #2 and uploaded basically the schema for the table and transform mapped it into sys_dictionary. This worked really well! Thanks for the great information!

I DO have one problem though. I created all my fields as Strings and did not specify a size so they all created as Max Length of 40. There are a few fields that I want to increase in size so I changed Max Length to larger values (by hand edit of the field in the normal way). However, these fields still truncate at 40 when I try to edit them. Do you know why this is happening and how I can fix it?

Thanks!

Greg

[raking08]

This is finally and nicely resolved. I think Mukesh's solution is a good one, but in the meantime a friend developed a script that used the import set to create the new tables columns. It works great and I will see if he would like to share it here ( As I did not develop it its not my place to share)

Thanks to all

Richard