SQL - Import of .xls successful but data not changed in tabel

Hi,

I'm very new to SQL (knowlegde not existing at all), Since its not possible to change data in a SQL database directly from Excel i'm trying out with importing the data instead but i'm getting error while trying to do so.

This is the error:

Capture.PNG
What i have done is, connected to the sql table from excel and made a copy of it and pasted it into new workbook.

Any idea's what i'v done wrong?
LVL 1
HakumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
can you post the workbook ?

Can you change the int to double this is your problem !
gowflow
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
First of all, what you're seeing in your screenshot is a warning, not an error. It basically says that the data in your excel sheet is interpreted as a "double" datatype (essentially a numeric value with decimals) but is being imported in your table as an integer (no decimals). If your excel data for that column has no decimals this is not an issue. If it does, the decimals get trimmed off. If that's not the behavior you want, you have to change the data type of the table.
I don't understand your second part though. You say you're trying to insert data into your database, but then you say you copied data from the database into a workbook? What do you mean by that?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Persoon is a new table? If so, just change the data type from INT to DOUBLE.
If not, check why it's suggestion DOUBLE from the source. Maybe you only have number like 12.00, 8.00, 33.00, ... If so don't bother with this and let it convert to INT since the decimal places are always zero.
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.

HakumAuthor Commented:
Hi gowflow!

Sadly there is alot of data which i cannot share, but tell me is there any other way? Maybe I could simply delete the content and keep the row headers, Would that be enough?
0
gowflowCommented:
Well my answer is: This specific field that we see part of the description as it is bigger than the column setting and also is in Danish:
extemtogev...

Do you kow if this field has decimals or not ? if no decimlals then no problem keep it INT.
gowflow
0
HakumAuthor Commented:
Make sense what you all are saying although i'm not using the data with decimals. I tried to ignore the error msg and simply try to finish the import through the wizard but it wont let me. i will try to check the col and see how its setup, and get back, thank you so far :)

@Koen - Sorry for the confusion, i was simply trying to explain where i got the data from, which i can see is not relevant to the case :) sorry for this.

@Vitor - This is not a new table,

@Gowflow - You are awesome as always thank you!!
0
gowflowCommented:
Tks for your nice comment. I would suggest the following:
Go to your access and open the table in Design mode so you get the fields for that field
extemtogev...

Change its type from Double to Integer or better Long Integer

and I think this should solve the issue.
You need to be careful as if this field has possible data that has decimals they will be truncated in this operation.

gowlfow
0
Valliappan ANSenior Tech ConsultantCommented:
Can you paste the output results that you get over here, like it will specify what error exactly you got.

HTH.
0
HakumAuthor Commented:
No luck :( maybe its me that is doing something wrong i tried to change the format for the col from Excel to Text and now its screwing me over.. :(

@Gowflow - Im not sure how to do this, i tried to open the the xlsx file with access and it linked it but not sure where and how to do so. kindly advise.

this is the log file:
Report1.txt
0
gowflowCommented:
Let me get this right:

You have a Table in Access that have data In a Table say TableA and from Excel you want to Import the data from Aceess to Excel is that correct  ?

gowflow
0
HakumAuthor Commented:
No, this is not what i want to do.

I have a table in SQL, where i want to change the value in 1 col from true to false in a few rows. i have exported the data from sql to excel and made the changes in excel and now i want import the changes back to the sql database.

made sense?
0
gowflowCommented:
Well this screen show is from SQL ? you are positioned in SQL and trying to do what you want ?
gowflow
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Ok, that's probably not the most convenient way to tackle your problem. Questions:

1. Is there a general logic to the updates your applying (e.g. update all records for product X, update all customers that haven't made a purchase in the last 30 days, etc.)
2. How many rows are we talking about that should be updated?

If there's a general logic that applies, you can probably write an update statement to apply the changes in the database directly. If not, and if there aren't too many records, you could also update the records directly in SQL itself (right click on the table, select "edit top 200 rows").
If that doesn't work, you would have to import your excel into a separate table, then run an update from the Excel import table on the original table.
0
gowflowCommented:
I believe you are setting yourself for MAJOR trouble and before going further it is in your best interest to STOP whatever your doing as you may surely end-up messing up your data completely. As it seems the way you explained it are sending data to excel that is limited and is coming back with no restrictions reason why you are getting all these errors.

SO STOP is my suggestion will wait for your answer and I may have a very easy solution.
gowflow
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Wise recommendation, gowflow.
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
+1
0
HakumAuthor Commented:
Here by stopped! :)

Yes this is correct i'm trying to import the excel data from Microsoft SQL Server Management Studio 2012, from my workstation, where i have connected to the server and right click on the database, then choose Tasks -> Import data, and then run the wizard.

Okay, i understand that i could corrupt everything and that excel is not the best application to make changes with, then please advise what would be be the best solution if i want to be able to change value in a specific column, for some rows, not all but only a few?
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Ha Kum,

Please see my earlier comment. It depends on what exactly you're trying to update, and how many records are involved.
0
HakumAuthor Commented:
No there is not a logic that applies sadly. we are talking about 800 rows in total and i want to change about 60 rows
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
In that case the quick and dirty method is to do it directly in the database, but I'm not sure if my fellow experts would agree.

Open the management studio and right click on the table name that contains the records you want to update. Select "edit top 200 rows". In the select statement that's generated automatically, remove the "top 200" to get all the records to return It should say something like

select *
from yourTable

This works if you indeed have a few 100 rows in your table. It's not recommended if you have many 10's of thousands.
Now, manually look for the records that need to be updated, place the cursor on the column that needs adjusting, and update the value. Repeat for all 60 records.
Each update is saved when you move the cursor to another record.

It's not the safest method, but with the limited data we have it's the easiest. If you have unique identifiers for the records (a list of ID's or something) which need to be updated it would be better to write an update script.

Vitor, Gowflow, your opinion on this please.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I personally don't like to edit rows directly in Management Studio but seeing the options here can be the less bad option.
How many records has the table? How do you know which records you need to update?
0
HakumAuthor Commented:
it has 802 records in total and yes, i do know which records to edit, and will do so if you guys recommend it
0
gowflowCommented:
Can you have a pattern to the changes like
All that belong to field1 and with date in field2 between a and b and are not in field5 ...

Some thing like this ?

If yes then we build the querry for you on a SELCT basis you check the records if they are the ones you want then we change that querry to UPDATE.

So will wait for your answer to advise next step.
gowflow
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
My question is how do you know which records are to be updated. Someone gave you a list of those records or are records that has specific criteria?
0
HakumAuthor Commented:
@Gowflow & Vitor - sadly there is no pattern, its a list of name that where we have to state if there are a manager or not , it is a manual process sadly even was when the list was created, so sadly i think i will need to go through the list manually its a one time.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
its a list of name that where we have to state if there are a manager or not
Well, we got something. How do you state that?
0
HakumAuthor Commented:
go through the list and see by its name if they are a manager or not.

like i know that Malene is a leader of a department then she will be a manager
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
If you have a list of names this can serve as the IN clause of an update statement. Can you post just 1 or 2 lines of dummy data and a ddummy example of the list of names you have?
@Vitor, I'm no fan of direct edits either, hence the request for a second opinion.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. And how many managers are we talking about?
Maybe you can make a list so we'll help you filter only on these rows and then will be more easy, fast and secure to update them.
0
HakumAuthor Commented:
we are talking about 60ish, managers.

here is the sample data:
-sample.xlsx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Export the names to a SQL Server table, let call it Managers, ok?
After that you just need to have a script similar as the following on:
UPDATE person
SET ColumnNameHere = NewValueHere
FROM  person
INNER JOIN  Managers
ON  person.name = Managers.name

Open in new window

0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Ok, I think we're getting somewhere.
I'm not quite sure what field you have to refer to (there are several that contain name references), but if we assume for example that your list contains the same names as in the column ref_aanhefinformeel, you can run the following update:

update yourSampleTable
Set isManager = True
Where ref_aanhefinformeel in (Select name from yourNameList)

Open in new window


For this to work you first have to import your Excel file with the manager names in a new table. This works in the same way as what you tried already in your original post, except that you would create a new table rather than import it into an existing one. That table would be the one called yourNameList in the sample code above.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HakumAuthor Commented:
Works like a charms!!! thank you guys! i got the list a wanted with some minor changes!! sorry for being confusing and thank you for saving my A$$!!!
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Glad we could help.
Just curious, what system are you using? The column names are all in Dutch by the looks of it. As a Dutch national I can't resist asking...
0
HakumAuthor Commented:
hehe.. its a application called Topdesk, a case file system etc.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This question should be saved as case study. It shows how the EE's experts can be useful not only for helping others to achieve what they want but in a more properly way that they had in mind, alerting them for the risks of their solution. It also shows a great team work.
Was nice to work with you guys. Cheers.
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Couldn't agree more! This was a fun question, and a good example of what this site is all about I think.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.