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.

CodePudding user response：

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