InfoTechEE
asked on
Need to format CSV to inlude commas and single-quotes
I have an excel sheet with a single Column which is filled with email addresses.
I need to somehow format the list so that each email address is comma delimited, and has a single quote around it. So basically each cell needs to have a single quote around its content, and be comma delimited.
Here's how it looks now:
John@yahoo.com
Joe@gmail.com
Jane@msn.com
Here's my end goal:
'john@yahoo.com', 'joe@gmail.com', 'jane@msn.com'
Any ideas how to achieve this?
I need to somehow format the list so that each email address is comma delimited, and has a single quote around it. So basically each cell needs to have a single quote around its content, and be comma delimited.
Here's how it looks now:
John@yahoo.com
Joe@gmail.com
Jane@msn.com
Here's my end goal:
'john@yahoo.com', 'joe@gmail.com', 'jane@msn.com'
Any ideas how to achieve this?
You will need to write an Excel macro. The export utility will not do that for you.
ASKER
Is there a way to Import CSV files into MS SQL Server 2008...is there a wizard?
I'm sure it would have this ability.
I'm sure it would have this ability.
InfoTechEE,In your example, are those three entries in a single cell, or is each entry in its own cell?
maybe try a bulk insert http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html or http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
but do you want to runt his outside of sql server?
some excel vba code like this is a simple example
I specify the start row and col using irow and icol
I place results in row 1, col 5
Public Sub MergeColumns()
Dim iRow As Integer
Dim iCol As Integer
'Specify start row/col
iRow = 1
iCol = 4
While Worksheets("Sheet1").Cells (iRow, iCol) <> ""
If Worksheets("Sheet1").Cells (1, 5) <> "" Then
Worksheets("Sheet1").Cells (1, 5) = Worksheets("Sheet1").Cells (1, 5) & ","
End If
Worksheets("Sheet1").Cells (1, 5) = Worksheets("Sheet1").Cells (1, 5) & "'" & Worksheets("Sheet1").Cells (iRow, iCol) & "'"
iRow = iRow + 1
Wend
End Sub
but do you want to runt his outside of sql server?
some excel vba code like this is a simple example
I specify the start row and col using irow and icol
I place results in row 1, col 5
Public Sub MergeColumns()
Dim iRow As Integer
Dim iCol As Integer
'Specify start row/col
iRow = 1
iCol = 4
While Worksheets("Sheet1").Cells
If Worksheets("Sheet1").Cells
Worksheets("Sheet1").Cells
End If
Worksheets("Sheet1").Cells
iRow = iRow + 1
Wend
End Sub
ASKER
matthewspatrick: Each entry is in its own cell. All the email addresses are in Column A, and each email address is on its own row, in its own cell.
rockiroads: To come to think about it, can't this be done with Cancatination formula?
rockiroads: To come to think about it, can't this be done with Cancatination formula?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.