Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Manipulating EXCEL Data (WinXP - SP2; OXP)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Manipulating EXCEL Data (WinXP - SP2; OXP)

    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
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Manipulating EXCEL Data (WinXP - SP2; OXP)

    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?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating EXCEL Data (WinXP - SP2; OXP)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating EXCEL Data (WinXP - SP2; OXP)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •