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

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
kevin1983Asked:
Who is Participating?
 
mbizupCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
mbizupCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
kevin1983Author Commented:
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
 
mbizupCommented:
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
 
kevin1983Author Commented:
mbizup:thanks ill try your suggestion
0
 
kevin1983Author Commented:
mbizup: I guess you mean load up Excel and point it to the SQL table as a data export?
0
 
mbizupCommented:
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
 
kevin1983Author Commented:
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
 
mbizupCommented:
< 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
 
kevin1983Author Commented:
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
 
kevin1983Author Commented:
...the excel file was inserted into a table named :"List_Frame1" - ive renamed to "Table-Items"

0
 
mbizupCommented:
Can you upload your sample?  It seems to test out okay here.
0
 
mbizupCommented:
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
 
kevin1983Author Commented:
Thanks a lot - thats done the job a treat.
0
 
mbizupCommented:
EXCELlent!
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.