I have a csv file which is a dump of a database table with the submission results from a form. All the form fields are listed in one database field and I would like to separate the information into the relevant parts eg name, address, tel.no, etc. Is there an easy way to parse the information in Excel or OpenOffice?

cheers,
Mike

how can you tell the break between the fields? is there a space or a comma? If not I think you may be stuffed.

It looks like I can either download with or without carriage returns. Not sure if it is possible to specify a character. Assuming I can, how could I then separate them?

Excel has a Text to Columns feature that will split a single line into individual cells at each occurence of whatever symbol you choose.

So in the database, all the input fields are concatenated in one table field? or do they have their own field?

If there are no seperator characters and it is just a long string, you can use LEFT, MID and RIGHT functions to lift text strings from it. This will only work if the different entries have the same character lengths and positions in every string.

AlastairMc – Thanks but this didn’t work

soma_rich – yes the form fields are concatenated either with or without carriage returns

poisonspider – entries will have different character lengths

Thanks for your suggestions but it looks like I might be stumped.

cheers,
Mike

soma_rich – yes the form fields are concatenated either with or without carriage returns

Open the csv in Word and do a search and replace on the carriage returns (^p IIRC) to something that you can then use the text to columns in Excel e.x XXX.

Save as text file and then open import/open in excel.

You could replace the crs for commas, but I’d keep the delineation different until you know what you are dealing with.

If you are stuck, email it to me and I’ll have a look, but I might be out for the rest of the afternoon.

does the text import wizard come up?
in excel , if you switch from delimited to fixed width, it puts data into columns.

Your stumped if the carriage returns are not consistent and there is no delimiting character.

open excel first then select import from text on the data menu and see if that works.