Solved

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

Posted on 2014-11-17
37
144 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
ID: 40447498
can you post the workbook ?

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

Expert Comment

by:Koen Van Wielink
ID: 40447513
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 47

Expert Comment

by:Vitor Montalvão
ID: 40447516
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:Hakum
ID: 40447521
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
ID: 40447544
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
ID: 40449167
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
ID: 40449256
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
ID: 40449260
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
ID: 40449302
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
ID: 40449312
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
ID: 40449318
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
ID: 40449332
Well this screen show is from SQL ? you are positioned in SQL and trying to do what you want ?
gowflow
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40449335
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
ID: 40449339
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 47

Expert Comment

by:Vitor Montalvão
ID: 40449423
Wise recommendation, gowflow.
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40449426
+1
0
 
LVL 1

Author Comment

by:Hakum
ID: 40449452
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 13

Expert Comment

by:Koen Van Wielink
ID: 40449454
Ha Kum,

Please see my earlier comment. It depends on what exactly you're trying to update, and how many records are involved.
0
 
LVL 1

Author Comment

by:Hakum
ID: 40449457
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 13

Expert Comment

by:Koen Van Wielink
ID: 40449475
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 47

Expert Comment

by:Vitor Montalvão
ID: 40449597
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
ID: 40449630
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
ID: 40449651
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 47

Expert Comment

by:Vitor Montalvão
ID: 40449657
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
ID: 40449706
@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 47

Expert Comment

by:Vitor Montalvão
ID: 40449710
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
ID: 40449717
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 13

Expert Comment

by:Koen Van Wielink
ID: 40449718
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 47

Expert Comment

by:Vitor Montalvão
ID: 40449722
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
ID: 40449743
we are talking about 60ish, managers.

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

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40449765
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 13

Accepted Solution

by:
Koen Van Wielink earned 167 total points
ID: 40449769
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
ID: 40449818
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 13

Expert Comment

by:Koen Van Wielink
ID: 40449823
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
ID: 40449825
hehe.. its a application called Topdesk, a case file system etc.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40449830
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 13

Expert Comment

by:Koen Van Wielink
ID: 40449838
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

813 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

11 Experts available now in Live!

Get 1:1 Help Now