Strip data from 1 field place into seperate fields

[CliveW]

I have a spreadsheet sent to me from an outside company.
They have put all of the addressess into one field that they wish us to use. Now for us to do the required work I need to split out the address field. into Addr1, Addr2, Addr3, Addr4, Zip code, Country. This is how they have it but all in the one field. The only thing I can use is the " , " for the seperator but don't know how to code the job please could someone help me.

Thanks
Clive

 

[JayKusch]

Create a table w/ the needed fields ... Address1, Address2, City, State, Zip ...

Then Import the file, comma-delimited into the table.

Thanks

J. Kusch

 

[mrees]

I would be very careful about using a comma-delimiter on text fields, especially if they may originate from a free format field, where a comma could be perfectly acceptable as part of an address.

You may leave yourself open to chopping up the address in the wrong places. I have lost count of the amount of times I've had a client who requests data in a CSV format, then wonders why the number of columns keeps changing.

It may be easier to get this fixed at source, we find a tilde (~) delimiter works best for us.

HTH
mrees

 

[CliveW]

Thanks,

Just that the xls spread sheet has the address lines all in 1 column, not in seperate columns which would allow for an easy import into sql server.