Manipulating EXCEL Data (WinXP - SP2; OXP)

[BobH]

I want to create a database in MySQL (because that's what my web host supports) from data that I have in a spreadsheet. I know how to write the MySQL commands to create the table, but to create the table elements I need to insert text and characters in the first position of all the rows in column A without losing the data in the rows -- just insert values that MySQL needs before the values that are there. I also need to insert a single quote in the first position of each row in column H without losing the data in the rows. Lastly, I need to extract all the rows and place them in a plain text file with a comma and a space inserted after the last value in each column. I don't know how to do this with the REPLACE function in EDIT. Can it be done without writing code? If so how? I MIGHT be able to auto-record a macro to run on the file, but my skills are somewhat rusty and MySQL is completely intolerant of syntax errors.

I hope this makes SOME sense.

TIA

[Rudi]

You mentioned Find and Replace. This can be used to insert characters in front of the row text in columns A and quotes in front of the text in column H. To do this follow these steps:

PS: Assume your list starts in Cell A1 and you need to insert "Test" in front of the text in Column A

1. Go to a blank cell to the right of your list.
2. Type "X"&A1 and autofill to the bottom of the list.
3. Copy the autofilled data and paste special "values" to over write the formula.
4. Copy this data over the data in column A to replace it.
5. Run Find/Replace - Find: X, Replace: Test

For Column H do the same, except when you run Find/Replace - Find: X, Replace: "

Regarding extracting the data into a text file with a comma and space after each columns info; you could concatenate all the values into a single cell with a comma and space inbetween, then copy/paste as values and paste into Notepad. But there may be better ways than this suggestion....hang around and see if other loungers have some better options for the text file!

Cheers

[HansV]

You don't provide enough information to know what to do with column A

For column H: insert a blank column in column I.
In I1, enter the formula <code>="'"&H1</code>
Fill down as far as needed.
With the cells in column I still selected, select Edit | Copy
Next, select Edit | Paste Special..., click the Values option, then click OK.
You can now delete column H.

> with a comma and a space inserted after the last value in each column.
Do you really mean each column, or should it be each row?

[BobH]

Thanks, Rudi!

CONCATENATE got me there. Between REPLACE and CONCATENATE, I was able to create the plain text file I needed.

Thanks again for your help.

[GoCush]

Bob

I realize this post is quite old - as I found it by Searching -- but I also want to migrate data from Excel into a MySQL table.
I think I could figure it out with enough time, but though I might save some if you could post a small sample workbook and code showing how you accomplished this task.

Thanks in advance