I have a database with a number of tables that I need to import into NAV in order to create a NAV solution to replace the old outdated solution.
The first step was to build identical tables in NAV. In my case this was done manually, but there should not be difficult to convert a “CREATE TABLE” command into a NAV object text file.
I then got the name of all the tables in the database with
select name from sys.tables;
and copied the result to Excel. In Excel I first created the select statement in column C
="SELECT * FROM ["&A2&"];"
and then created the sqlcmd function in column G
="sqlcmd -S ServerName -E -d DatabaseName -Q " & CHAR(34)&C2&CHAR(34)&" -o "&A2&".csv -s ;"
Then I fill down column C and column G and have all the lines for my command file ready. Note that I use “;” as a decimal separator.
I create a folder for the data and within the folder I create a file called ExtractData.cmd and copy all the lines from column G to that file. Then this command file is executed and that folder fills up with CSV files for each table.
The final step is to import the data with the attached NAV Report.
