6

I have a query Excel in each of my rows, about 2000. When I copy paste all queries into SQL Server roughly 100 rows are showing a error. Might not even be an error but the query will paste into SQL Server with double quotes in front of entire query.

I have error checking in place so it has something to do with the data in my Excel sheet. My query pasted in SQL Server for instance:

"insert into

I cannot paste the entire query or any data, sorry.

Thank you for looking at this

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3029609
  • 89
  • 1
  • 1
  • 6

8 Answers8

10

Actually a lot easier then all of the posts here, in another column do the following formula:

=CLEAN(MyCell)

ShanksPranks
  • 397
  • 3
  • 8
9

This occurs when a line has an embedded carriage return, it may happen in other situations as well. I just do a find/replace for double quotes in the resulting query, but you could also first paste it into Word then copy from there and it will paste without the quotes.

Alternatively, if it's just from embedded returns you could wrap your formula in a pair of SUBSTITUTE() functions to remove CHAR(13) and CHAR(10) if formula was in A1:

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),""),CHAR(10),"")
Hart CO
  • 34,064
  • 6
  • 48
  • 63
1

When you are typing your SQL query in the Excel cell never hit Enter, just keep typing all in a single line regardless of how long it is.

If you have queries already entered, double-click on the cell to go to the edit mode and start removing any new lines you have already entered.

WoJ
  • 27,165
  • 48
  • 180
  • 345
Denis Polosy
  • 11
  • 1
0

The simplest way is to copy from the Formula Bar instead of from the cell.

When you have multiple lines in the cell, Excel will fill the clipboard with a CSV-representation of the cell. That means: double quotes added around, and the double quotes inside the string are escaped with yet another double quote.

  • Not: select the cell, Ctrl-C
  • But instead: select the cell; in the formula bar, highlight the complete text (cursor at start, press shift, press arrow-down until the last character is highlighted), Ctrl-C.
  • and paste in the target application (Notepad, or browser-entry form, or...)

An easier way to select all the text from inside the formula bar would be nice...

PBI
  • 335
  • 1
  • 4
  • 1
    Place the cursor anywhere in the formula bar and hit `Home`, then press `Shift` + `End`. Shame `Ctrl` + `A` doesn't work from within the formula bar. – Austin Dean Jul 06 '17 at 20:30
0

Paste the MS Excel content into MS Word with "Keep Source Formatting" pasting option and then copy and paste it to SQL/XML.

Isha V Jagadishwari
  • 1
0

I have found that a tab in an excel file will also cause this behavior.

STEPS TO CREATE PROBLEM CITED BY OP

  • Find a table create script where all the fields are tabbed in 1 tab
  • copy that to excel Do some sql generation macro in a second column
  • (Col E, because A-D were consumed by the text to col operation ) Copy
  • Col E Paste into Visual Studio

You just reproduced the problem op has.

FIX

Go

  • to the table create script Hightlight all the columns
  • SHIFT-TAB until they are all at column 0
  • Paste that list of fields back into Excel.
  • Redo the TEXT TO COL operation
  • (the macro should still be good, check it to be sure)
  • Recopy the COL E to Visual Studio

You should have just solved the problem.

greg
  • 1,673
  • 1
  • 17
  • 30
0

I have to say that "Spacez" give the best answer. he give the road to find the real answer.

Sub CopyCellContents()
'create a reference in the VBE to Microsft Forms 2.0 Lib
' do this by (in VBA editor) clicking tools - > references and then ticking 
"Microsoft Forms 2.0 Library"
Dim objData As New DataObject
Dim strTemp As String
strTemp = ActiveCell.Value
objData.SetText (strTemp)
objData.PutInClipboard
End Sub
IT life
  • 1
-1

Although this question is quite old, I still found this link when googling. So I think it should be good to leave the thread I've found and works good for me.

Leave out quotes when copying from cell

Community
  • 1
  • 1
Spacez
  • 77
  • 2
  • 11
  • 1
    This doesn't answer the question, it's just a sign to another answer. You need to cite answer from elsewhere and add more detail here in order for this answer to be good. However, I this specific case, this question is a duplicate of the question you pointed at I believe. So flagging as duplicate is the best option here. – AXMIM Feb 15 '17 at 16:47