excel help

[rh71]

Have a simple XLS with 1 column but over 1000 rows with values such as:

joe
jane
bob
.
.
.


We want to use those values in an SQL statement so we just want an output like this:

'joe','jane','bob', ...

How do we do this ? The output being in A1 of the spreadsheet or wherever is fine, as long as it's copy/pasteable.

 

[zerocool1]

import to access?

 

[rh71]

I seemed to get what I want by creating 2 columns around it and doing a replace via an exported .csv (then notepad)... Curious if there's an easier way IN excel.

 

[KLin]

I don't think a cell can hold that much text.

EDIT:

The macro below does what you want, but it's too long of a string to fit in a cell if I use 100 rows. So 1000 wouldn't work.

Public Sub SQL()
Dim i As Long, strSQL As String, wrk As Worksheet
Set wrk = ActiveSheet
strSQL = "SELECT * from tblNames Where name in("
With wrk
For i = 1 To 20
If i < 20 Then
strSQL = strSQL & "'" & .Cells(i, "A").Value & "',"
Else
strSQL = strSQL & "'" & .Cells(i, "A").Value & "')"
End If
Next i
.Cells(i, "B").Value = strSQL
End With
End Sub

 

[ActuaryTm]

The simple approach would be something along these lines:

• Sub RangeConcatenate()
  Dim CellValue As Range
  For Each CellValue In Selection
  Range("D1").Value = Range("D1") & "'" & CellValue & "'" & ","
  Next
  End Sub

Cell D1 would obviously be the cconcatenated resultant output of whatever selection was made prior to running the macro.

More robust solutions can be found via this search.

 

[WildHorse]

If you want to just export your spreadsheet to a text file, then you can create a SQL database and then load it into SQL with the command:

Load data local infile 'filename.txt' into table tablename;

Then in SQL you do your SELECT statement to pull the names

I'm speaking from a MySQL orientation, not sure about other SQL implementations.