Search code examples
mysqlsqlexcelnotepad++

Excel columns to SQL statement

This question is on turning the copy pasted columns from Excel into a SQL statement. My current process includes manually replacing things. After using some notepad++ functionality to control h to remove and replace all occurrences of something, and I love that notepad++ offers additional functionality. I’m just not sure how to use this functionality to get a lot closer to the end product. I feel like I can’t operate the gas and brakes normally when I have to do this, and it would be really nice to automate. Maybe I just feel that way because I know it’s probably easy, or at least it bugs me so much I think that.

Copy/paste from excel into (it looks like this is notepad++):

"Random
Code"   "Random
Code"   "Random
Code"   "Random
Code"   "Random
Code"   "Random
Number"

First, I highlight everything and replace “ (double quote) with an acute, leftquote, backtick, whatever, this symbol `

Second, I replace the blank spaces with \r\n

`Random`

`Code`

`Random`

`Code`

`Random`

`Code`

`Random`

`Code`

`Random`

`Code`

`Random`

`Code`

This gives me every word on a new line, with each surrounded by the back tick.

As you may have guessed, here is where I manually clean the rest

create table table_for_upload (
`Random Code`: varchar(25) null,
`Random Code`: varchar(25) null,
`Random Code`: varchar(25) null,
`Random Code`: varchar(25) null,
`Random Code`: varchar(25) null);

What are the unanimous next steps to get the data looking more like a SQL statement. Using notepad++ control h functionality? Or vim? Won't add the tag for vim.

Solution

  • Most parts can be done with a regular expression search and replace:

    1. open the Replace dialog,
    2. choose "Regular expression" as search mode
    3. Find what: [[:space:]]*"([^"]+?)(\R\R?)([^"]+)"
    4. Replace with: '\1 \3': varchar(25) null,\2
    5. click "Replace All"
    6. take care of the first line manually: insert the "create table" part
    7. take care of the last line manually: replace the comma with ");"

    Lets examine that [[:space:]]*"([^"]+?)(\R\R?)([^"]+)":

    • [[::space::]]* matches the optional spaces before "Random
    • "([^"]+?) matches a double quote followed by everything that is not a double quote in a non greedy way and stores it in \1
    • (\R\R?) takes care of the line break between Random and Code and stores it in \2
    • ([^"]+)" matches everything that is not a double quote followed by a double quote and stores it in \3
    • the replace string uses the \1,\2,\3 values from your actual line and mixes them with the constant varchar... string