January 31, 2012 at 8:52 am
Good morning, does anyone know of a tool/method for formatting a large amount of date in prep for a SQL query? Someone gave me a spreadsheet with 1600+ Unique account numbers in which I would like to quickly format for an "In" statement. So, I need to wrap each account number in single quotes followed by a comma. Thank yo for any help that you may provide.
January 31, 2012 at 9:02 am
Write a formula in Excel or whatever spreadsheet package you're using. Then you can copy and paste it into your query.
John
January 31, 2012 at 9:05 am
I can think of two ways, and I have done both.
The first method is to add a column in Excel that formats the numbers as you want them, typically something like [="'" & TEXT(A1,0) & "',"]. To get fancy, replace the comma with a calculation that checks to see if it is needed based on the existence of a next row. Once that is done, copy and paste from the calculated column into SSMS. You should get the quoted list; remove the last comma and run your query.
The second method is to import your Excel spreadsheet into SQL Server and then JOIN to the new table you created. If you are using SSMS, you can right-click on the database name and select "Import Data" from the pop-up menu. Importing from Excel is a standard option so it should be relatively painless. You would then need to clean up your work by removing the table (if it is a one-time query).
January 31, 2012 at 9:08 am
Thanks for your responses. I used the excel method with creating a new column and using the following;
="'"& A2 & "'"&","
This worked well and was a classic case of as soon as I posted the question, I thought of the answer. Thanks again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply