How to update a field using an excel file

I would like to update a field in my system with a value from an excel file
The excel file is called Hull Equip and it contains two columns Equipment No (which contains the existing values) and New Equip No (which contains the new number)
The table I am trying to update is called EQUIP and the field is called EQNUM
How would the query look?
Gordon
Gordon HughesDirectorAsked:
Who is Participating?
 
OMC2000Commented:
well, for tables where this columns is not null we should add extra condition:

Update TASKEQ
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = TASKEQ.EQNUM)
where TASKEQ.EQNUM in (select EQNUM from HullEQ)
0
 
OMC2000Commented:
Move first column after the second one, add two extra columns: one before the first column and one between two columns,
put into the first cell text:
update tableName set columnName = '
propogate it all rows
put into the first row third column text:
'' where columnName = '
propogate it all rows
put into the first row fifth column text:
';
propogate it all rows

copy all content to notpad for insance and remove all Tabs by command replace Tabs with empty string
Your SQL script is ready to execute.

Don't forget to replace tableName & columnName with your real identifiers.

If you need to execute such procedure automatically, you could record macro and play it in Excel
0
 
Gordon HughesDirectorAuthor Commented:
Hi
That sould complicated to me
What if i import the excel into a new table in the database and then run a sql script
Not sure what the script would look like
Gordon
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
OMC2000Commented:
In fact it was the simplest way.
This article discribes how to import excel into a new table in sql server.
https://support.microsoft.com/en-us/help/321686/how-to-import-data-from-excel-to-sql-server

Then you could execute the following command to update your data in original SQL server table with data from imported Excel table

update EQUIP set EQNUM = (select "New Equip No" from XLImport9 where XLImport9."Equipment No"=EQUIP.EQNUM )
0
 
Gordon HughesDirectorAuthor Commented:
Hi
Well not quite there
I have imported the excel file into(in the import I changed the values to varchar etc
and called the table HullEQ
I found the the EQNUM is used in several tables and view
So created the following
Update TASKEQ
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = TASKEQ.EQNUM)
Update WOC
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOC.EQNUM)
Update WOE
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOE.EQNUM)
Update WOP
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOP.EQNUM)
Update WOV
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOV.EQNUM)
Update WOCHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOCHVW.EQNUM)
Update WOCVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOCVW.EQNUM)
Update WODESCVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WODESCVW.EQNUM)
Update WOEHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEHVW.EQNUM)
Update WOEQCVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQCVW.EQNUM)
Update WOEQLCOM
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQLCOM.EQNUM)
Update WOEQLHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQLHVW.EQNUM)
Update WOEQLVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQLVW.EQNUM)
Update WOEQOVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQOVW.EQNUM)
Update WOEVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEVW.EQNUM)
Update WOHISTEQ
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOHISTEQ.EQNUM)
Update WOPHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOPHVW.EQNUM)
Update WORHEQVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WORHEQVW.EQNUM)
Update WOVHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOVHVW.EQNUM)
Update XWOEQVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = XWOEQVW.EQNUM)
update EQUIP
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = EQUIP.EQNUM)

But for some reason the underlined red elements (NewEQNUM and HullEQ) says invalid column name or invalid object

Any ideas
Gordon
0
 
OMC2000Commented:
Are you sure you imported Excel data to the same database and schema?
What is the result of the following query:

select * from HullEQ

?
0
 
Gordon HughesDirectorAuthor Commented:
Well it is very strange
The table is shown in the correct database
run Select * from HullEQ (if i hover over HullEQ iy says Invalid object name)
when i press execute it returns all the data under headings EQNUM and NewEQNUM

Gordon
0
 
Gordon HughesDirectorAuthor Commented:
OK I restarted the SQL server and now it recognised the table
Now when I run the query is come back against every table

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'EQNUM', table 'MP2Live.dbo.TASKEQ'; column does not allow nulls. UPDATE fails.

The HullEQ table does not contain any Null values as far as I can determine

Gordon
0
 
Gordon HughesDirectorAuthor Commented:
OK have changed all the lines
Now I have to deal with duplicates before it will work, so will close this question
0
 
OMC2000Commented:
The original question was answered. Alternative solutions were proporsed. Tipical problem was resolved. There is no real problem with the final solution. So, objection
0
 
Gordon HughesDirectorAuthor Commented:
Hi
What is this all about?
0
 
Gordon HughesDirectorAuthor Commented:
All good
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.