Click here to Skip to main content
12,997,531 members (58,928 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Help needed in figuring out a solution for my problem. Can this be done using .NET

I have a data which contains Sl.No, Mobile No, Email Id, Address Main (4 Columns).
In "Address Main" Column the entire address is entered in a single cell

(Kishore kumar.Madugmla 1 11 110/92/A/43, New Tatachari colony,Shyamlal Buildings, Begum pet, Hyderabad91-9290910799)

I tried to used Delimiter and tried to Split it with the help of " , " or " - " and it was a failure. There are around 3562 records and the data in the address field is not proper in most of the cells.

Is there a way i can split the field in Column D into different cells, for example i want the data in this way (Using the above sample address cell)

Kishore Kumar.Madugula H.No: 1-11-110/92/A/43 New Tatachari colony Shyamlal Buildings

P.S : But for the above sample every details is seperated by " , " but for many other cell it is not the case. That is the main challenge for me.

Can you help me out with a solution

Sample Document
Posted 26-Apr-13 1:59am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

If your data is missing a consistent seperator, there's no reliable way to do this. How is your code going to know that this:
123 Some Street SomeCity SomeStateSomPostalCode

Should be:
123 Some Street
SomeCity, SomeState  SomePostalCode

and not
123 Some Street SomeCity
SomeState  SomePostalCode


It can't. At least not without a TON of database work and complete lists of streets, cities, territories, postal codes, blah, blah, blah, ... som your code has something to draw from and recognize the malformed data.

Truthfully, with only 3500 records, it would be faster to go through them by hand and fix them all before importing them than it would to write the code to do it for you and test the crap out of it. You'd STILL have to go through and make sure that data is correct.

This is the problem with collecting data from the user and skilling the validation step at input time. The work you save them suddenly falls entirely on you to fix.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Thanks For the reply.

Yes i can do it manually but i will be getting this kind of data twice in every week. So i have to create a generic solution for this kind of problem.

Can you help me out in finding out a solution. Is it possible.....!
Dave Kreskowiak 26-Apr-13 9:52am
Re-read what I posted. THERE IS NO SOLUTION ON YOUR END!!

The data must be corrected on the source end, where it is input.

It is IMPOSSIBLE to come up with code that can read your mind and make the corrections. It has no way of knowing what a correct address should be. I told you how you rmight get closer, but it's not a fool-proof solution and will NOT fix every single addres with 100% accuracy. You will still need to look over every single record yourself.

Your ONLY solution is to have the source of the data correct all the problems on their end before it is sent to you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170622.1 | Last Updated 26 Apr 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100