Solved

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

Posted on 2014-11-17
37
140 Views
Last Modified: 2014-11-18
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?
0
Comment
Question by:Hakum
  • 12
  • 9
  • 8
  • +2
37 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
can you post the workbook ?

Can you change the int to double this is your problem !
gowflow
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 1

Author Comment

by:Hakum
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 1

Author Comment

by:Hakum
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
Can you paste the output results that you get over here, like it will specify what error exactly you got.

HTH.
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 1

Author Comment

by:Hakum
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well this screen show is from SQL ? you are positioned in SQL and trying to do what you want ?
gowflow
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Wise recommendation, gowflow.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
+1
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Ha Kum,

Please see my earlier comment. It depends on what exactly you're trying to update, and how many records are involved.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:Hakum
Comment Utility
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 1

Author Comment

by:Hakum
Comment Utility
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
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 166 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 1

Author Comment

by:Hakum
Comment Utility
@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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 1

Author Comment

by:Hakum
Comment Utility
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 1

Author Comment

by:Hakum
Comment Utility
we are talking about 60ish, managers.

here is the sample data:
-sample.xlsx
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
Comment Utility
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
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 167 total points
Comment Utility
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
 
LVL 1

Author Comment

by:Hakum
Comment Utility
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
 
LVL 1

Author Comment

by:Hakum
Comment Utility
hehe.. its a application called Topdesk, a case file system etc.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Couldn't agree more! This was a fun question, and a good example of what this site is all about I think.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now