CSV Export

[QuePID]

I have an excel sheets which contain information which will be exported via CSV in Excel and later be imported via CSV into a mysql database.

The problem is that some columns in the excel can contain quite a few common delimiters such as commas, apostrophes for text fields, along with single and double quotes being used to hold longitude/latitude fields (in degrees minutes and seconds.

I notice that no fields contain | and perhaps it could be used to enclose fields, and still delimit with commas.

How would one go about producing a CSV with | used to enclose fields?

 

[diddi]

Sub SaveCSV
    Open "C:\test.txt" for output as #1
        Print #1,"|" & mydata & "|"
    Close 1
End Sub

 

[Pascal]

Hi,

Here's some code that might meet your needs, or you can use as a starting point: -

Sub PipeExport()

Dim intUsedRows As Integer
Dim intUsedColumns As Integer

    Open "C:\Export Delimited\Pipey.txt" For Output As #1

    With Worksheets(1).Range("a:f")
        
        intUsedRows = ActiveSheet.UsedRange.Rows.Count
        intUsedColumns = ActiveSheet.UsedRange.Columns.Count

        For i = 1 To intUsedRows
            For j = 1 To intUsedColumns - 1
                Print #1, .Cells(i, j); "|";
            Next j
            
            Print #1, .Cells(i, intUsedColumns)
        Next i
    End With

    Close #1
    
    MsgBox ("Done Successfully")
    
End Sub

Regards

 

[QuePID]

Thanks everyone,

I found one by microsoft which uses double quotes and modified it to use | for encapsulating fields.

Sub QuoteCommaExport()
' 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