I got this need for exporting excel in a text file with comma delimters and single quotes to troubleshoot a issue in oracle database.
The issue was to find the records in the table with against their primary keys, now the primary key type was a string and I had them in a excel file and they were 100s in number, now think to write a where clause it will be a pain in ass..
So I found out the method to export the excel in text format with the records as comma delimtted and with single quotes as we need them in where clause.
Eg. Select * from Employee Where EmployeeID in ('XYZ', 'ABC', 'YUV'.......)
1. Copy your contents in a MACRO enabled Excel workbook.
2. Press Alt + F11 or go to Developer Tab and then click Visual Basic in the code group.
3. Go to MS main menu - > Excel Options - > Popular - > select the Show Developer and click OK.
4. Open the Visual Basic editor and click the Module on the insert menu.
5. Paste the following code in the module space
Sub ExportTextFormat()
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma Exporter")
' Obtain next free file handle number.
FileNum = FreeFile()
' Turn error checking off.
On Error Resume Next
' Attempt to open destination file for output.
Open DestFile For Output As #FileNum
' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If
' Turn error checking on.
On Error GoTo 0
' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count
' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count
' Write current cell's text to file with quotation marks.
Print #FileNum, "'" & Selection.Cells(RowCount, _
ColumnCount).Text & "',";
' Check if cell is in last column.
If ColumnCount = Selection.Columns.Count Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount
' Close destination file.
Close #FileNum
End Sub
6. Save the file, select the data you want to export and run the Macro ExportTextFormat.
7. Give a filename with the full path (eg. D:/Test.txt)
8. You are good to go now.
Hope this helps!
-Azhar
