How to get data from Excel into a SQL table

xenon

Hello everyone.

I have an Excel spreadsheet with a few thousand items in there with the weight - just two columns. What I want to do is for each "Item" in the spreadsheet column "A" get the weight from column "B" and populate the weight field in my Item table in SQL.

Can anyone show me a script by which I could achieve this?

Thanks!

Ian

PS - this is NAV2009R2 so I guess a dataport would work too but I'm not a NAV developer and I don't have a developer licence.

archer89

you could use data migration tool.
goto Administration --> Application Setup --> Company Setup --> Data Migration
there you can import and export excel files.

also follow http://navtips.blogspot.co.at/2013/05/usage-of-data-migration-option-in.html

for importing data via sql server, e.g. with the sql server import/export wizard, better use csv file format instead of xls format. you can export that file format with excel (save as). that results in a text file, where the values are seperated by a semicolon.
but: in general it's no good idea to import data directly to the sql database, because the nav business logic is not run through so (insert trigger, ...) . can end in inconsistent data.

archer89

you could use data migration tool.
goto Administration --> Application Setup --> Company Setup --> Data Migration
there you can import and export excel files.

also follow http://navtips.blogspot.co.at/2013/05/usage-of-data-migration-option-in.html

for importing data via sql server, e.g. with the sql server import/export wizard, better use csv file format instead of xls format. you can export that file format with excel (save as). that results in a text file, where the values are seperated by a semicolon.
but: in general it's no good idea to import data directly to the sql database, because the nav business logic is not run through so (insert trigger, ...) . can end in inconsistent data.

xenon

Thanks!