[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Extract excel data - only rows where field has a numeric number & remove dashes

Posted on 2011-10-21
16
Medium Priority
?
234 Views
Last Modified: 2012-05-12
Hi,

I have some data in some excel files that I need to import into A SQL database, but first what I’d like to do is extract only all of the rows where the field named: “Stk Tech Desc” has a numeric number, not any of the records that have text because the new filed in the SQL table only accepts numeric values. The other issue is some numbers include dashes for example: 3898-850 and need the dashes removed so that is says 3898850.

I’ve attached a small sample of the data, is this possible to do in Excel or other program such as Access?

CatalogueNumbers.xls
0
Comment
Question by:kevin1983
  • 8
  • 7
16 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 37006775
Without using any macros, you could insert the following formula to the right of the existing data to show only those items in "Stk Tech Desc" that are either numeric OR have a single dash in them as you described:
In cell E2:
=IF(ISERR(FIND("-",C2)),IF(ISNUMBER(VALUE(C2)),C2,""),SUBSTITUTE(C2,"-",""))

You could then copy this formula down, filter this list on the blank values in this column, then remove the rows where the value is blank.

This solution is pretty specific to your given example.  If there was a "Stk Tech Desc" that had another "semi-numeric" format such as 123-45-6546, it would not be converted or considered numeric.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37006819
Try creating a table linked to your Excel file and running an INSERT query to get the data into your SQL table with an SQL statement as follows:

INSERT INTO YourTableName ([Stk Tech Desc],[Stk Tech Ref],[Stk Part Code])
VALUES( replace('' & [Stk Tech Desc], '-', ''),[Stk Tech Ref],[Stk Part Code])
WHERE IsNumeric(replace('' & [Stk Tech Desc], '-', '')) = TRUE
0
 

Author Comment

by:kevin1983
ID: 37006876
Thanks - I think that may do what I need. I just reliased some codes have dots and/ or slashes instead of dashes such as the following code: 333.531.012  or 257675-9/4832120-6 - sorry only just spotted this.

I guess i could add a seperate column with a new formular for these ones? some columns are being picked up that have a mix of text and numbers - for example it says: item code 14-56-792 and column E displays "Item code"

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 61

Expert Comment

by:mbizup
ID: 37006995
You could revise the INSERT Query as follows:

INSERT INTO YourTableName ([Stk Tech Desc],[Stk Tech Ref],[Stk Part Code])
VALUES( Replace(replace(replace('' & [Stk Tech Desc], '-', ''),'/',''),'.',''),[Stk Tech Ref],[Stk Part Code])
WHERE IsNumeric(Replace(replace(replace('' & [Stk Tech Desc], '-', ''),'/',''),'.',''))= TRUE

Also, you could import the raw Excel Data into a Temp table, and add a 'test' column to validate the data before inserting it into your SQL table.

0
 

Author Comment

by:kevin1983
ID: 37007104
mbizup:thanks ill try your suggestion
0
 

Author Comment

by:kevin1983
ID: 37007126
mbizup: I guess you mean load up Excel and point it to the SQL table as a data export?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37007173
Hmm - I could be misreading your requirements entirely!

Your question is cross-posted to the Access zone.  Are you not using Access at all?

The Method I am describing will work with an intermediate table in an Access database that is linked (or imported from) your Excel spreadsheet, with an INSERT query in the Access QBE used to get your data into SQL.
0
 

Author Comment

by:kevin1983
ID: 37007608
mbizup:dont think your misreading requirments - not currently using access, my data is in Excel (sample attached on orginal post) but I just thought perhaps I could use access if required...ie by importing data into it as you just described. maybe I shouldnt have posted in the access zone.

Ill try you method in access
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37007707
< maybe I shouldnt have posted in the access zone >

I personally think it was a great idea to post in both zones for the variety of suggestions.

This is absolutely how I would approach this problem - maybe taking a coded approach.

But there may be a more straight forward way in Excel that I am not aware of (I am definitely not an Expert in Excel).
0
 

Author Comment

by:kevin1983
ID: 37010678
ok, ive imported the excel file into Access 2003 and created a new blank local access table named "test" to test outwith the same field names as the excel file. I created a new query with your code and when running the query a message says there is a missing ; at the end of your statement, I tries adding on after "TRUE" but still same message, any ideas where it needs to go?

INSERT INTO Test ([Stk Tech Desc],[Stk Tech Ref],[Stk Part Code])
VALUES( Replace(replace(replace('' & [Stk Tech Desc], '-', ''),'/',''),'.',''),[Stk Tech Ref],[Stk Part Code])
WHERE IsNumeric(Replace(replace(replace('' & [Stk Tech Desc], '-', ''),'/',''),'.',''))= TRUE
FROM List_Frame_1;
0
 

Author Comment

by:kevin1983
ID: 37010682
...the excel file was inserted into a table named :"List_Frame1" - ive renamed to "Table-Items"

0
 
LVL 61

Expert Comment

by:mbizup
ID: 37010871
Can you upload your sample?  It seems to test out okay here.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37010883
I don't have your tables, though so what I tested was a little different than what I posted.

I do think I see the issue...  Try this, and if it doesnt work out, upload the sample if you can:

INSERT INTO Test ([Stk Tech Desc],[Stk Tech Ref],[Stk Part Code])
SELECT Replace(replace(replace('' & [Stk Tech Desc], '-', ''),'/',''),'.',''),[Stk Tech Ref],[Stk Part Code]
FROM List_Frame_1
WHERE IsNumeric(Replace(replace(replace('' & [Stk Tech Desc], '-', ''),'/',''),'.',''))= TRUE
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37010888
If the table name is now Table-Items, change the FROM clause to

FROM [Table-Items]

Including the [] so that the - doesn't cause an error.
0
 

Author Closing Comment

by:kevin1983
ID: 37010942
Thanks a lot - thats done the job a treat.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37010954
EXCELlent!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

872 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