Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more
COURSE of the MONTH
13 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
excel & SQL
Posted on 2016-08-23
Please open attached file in excel.
I have over 5000 rows of the similar format in excel. and have two things need help.
1. How can I extract the number like first row 1429156 into different column
2. How can i extract the last name and first name smith (lastname), jayle (firstname) into different column.
After then, I will take the excelsheet and import into the table.
(This is something I want to learn if you can show me :-)). I know how to import data into the table.
1. How can I extract the number like first row 1429156 in SQL
2. How can i extract the last name and first name smith (lastname), jayle (firstname) in SQL
sample.xlsx
I have over 5000 rows of the similar format in excel. and have two things need help.
1. How can I extract the number like first row 1429156 into different column
2. How can i extract the last name and first name smith (lastname), jayle (firstname) into different column.
After then, I will take the excelsheet and import into the table.
(This is something I want to learn if you can show me :-)). I know how to import data into the table.
1. How can I extract the number like first row 1429156 in SQL
2. How can i extract the last name and first name smith (lastname), jayle (firstname) in SQL
sample.xlsx
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
2
- +1
8 Comments
Active today
You can split the data into separate columns using the Text to Columns wizard.
Select the data in column A and then from the Data ribbon select "Text to Columns".
Step 1 - Select Delimited Radio button, click Next
Step 2 - Check boxes against Space and Other, in box next to Other enter a comma, click Next
Step 3 - If you want the contents of column A overwritten leave Destination as $A$1 otherwise change to $B$1. To ensure the dates are populated correctly, select the header of each of the date columns, choose the Date radio button and use the dropdown next to it to specify the format. This is the format of the source data as opposed to how the dates should end up.
Click Finish.
As for doing it in SQL - sorry, beyond me. I guess each of the sections of the text come from different fields of the table so should be able to extract separately.
Thanks
Rob H
Select the data in column A and then from the Data ribbon select "Text to Columns".
Step 1 - Select Delimited Radio button, click Next
Step 2 - Check boxes against Space and Other, in box next to Other enter a comma, click Next
Step 3 - If you want the contents of column A overwritten leave Destination as $A$1 otherwise change to $B$1. To ensure the dates are populated correctly, select the header of each of the date columns, choose the Date radio button and use the dropdown next to it to specify the format. This is the format of the source data as opposed to how the dates should end up.
Click Finish.
As for doing it in SQL - sorry, beyond me. I guess each of the sections of the text come from different fields of the table so should be able to extract separately.
Thanks
Rob H
Active today
There are potential issues with Text to Column if a line does not fit the same convention as other lines.
For example in your sample, row 11 does not have a first name; resulting split data ends up out of alignment.
If your names could have second names or initials that would also mess it up eg if "TURNER, SAMF" was "TURNER, SAM F" (note space between SAM & F) this would also give misalignment.
Thanks
Rob
For example in your sample, row 11 does not have a first name; resulting split data ends up out of alignment.
If your names could have second names or initials that would also mess it up eg if "TURNER, SAMF" was "TURNER, SAM F" (note space between SAM & F) this would also give misalignment.
Thanks
Rob
Active today
please show me the codes. if I do not expect the codes will work perfectly to match up.
I will review it and correct manually.
I will review it and correct manually.
Active today
It isn't code, Text to Columns is a standard feature. Follow the steps in my first post.
Active today
Sorry, don't know SQL. My earlier comment was merely a guess based on VERY limited knowledge.
Active 4 days ago
Your source data appears to have fixed width values (12, 8,11,11 characters each). Using Text-To-Columns in Excel would easily split up the data. As Rob noted, however, your sample data set is truncating the names in such a way that all or part of the first names are missing.
Whatever process is generating this data would need to be revised first so that the full names are listed (perhaps expand the output of the full name field to say 30-35 characters rather than 12). Then you can easily parse out the contents.
I've attached a modified version of your workbook that shows how the first and last names - such as they are - can be extracted from the parsed data. The "Original" sheet shows your example, plus screenshots of the Text-To-Columns dialog boxes needed to prepare the data. The "Sheet1" sheet shows the parsed data plus two new columns with formulas for showing the first and last names.
First Name:
E2: =IFERROR(MID(A2,FIND(",",A
Last Name
F2: =IFERROR(LEFT(A2,FIND(",",
Regards,
-Glenn
EE-sample.xlsx
Whatever process is generating this data would need to be revised first so that the full names are listed (perhaps expand the output of the full name field to say 30-35 characters rather than 12). Then you can easily parse out the contents.
I've attached a modified version of your workbook that shows how the first and last names - such as they are - can be extracted from the parsed data. The "Original" sheet shows your example, plus screenshots of the Text-To-Columns dialog boxes needed to prepare the data. The "Sheet1" sheet shows the parsed data plus two new columns with formulas for showing the first and last names.
First Name:
E2: =IFERROR(MID(A2,FIND(",",A
Last Name
F2: =IFERROR(LEFT(A2,FIND(",",
Regards,
-Glenn
EE-sample.xlsx
Active today
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Split:
-- Rob Henson (https:#a41767137)
-- Rob Henson (https:#a41767151)
-- Glenn Ray (https:#a41767629)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
frankhelk
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Split:
-- Rob Henson (https:#a41767137)
-- Rob Henson (https:#a41767151)
-- Glenn Ray (https:#a41767629)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
frankhelk
Experts-Exchange Cleanup Volunteer
Featured Post
In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Course of the Month13 days, 5 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.