CSV Export

QuePID

New Member
Joined
Aug 27, 2013
Messages
2
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?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
Code:
Sub SaveCSV
    Open "C:\test.txt" for output as #1
        Print #1,"|" & mydata & "|"
    Close 1
End Sub
 

Pascal

Board Regular
Joined
Jun 6, 2007
Messages
200
Hi,

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

Code:
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

New Member
Joined
Aug 27, 2013
Messages
2
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
 

Forum statistics

Threads
1,137,350
Messages
5,680,976
Members
419,947
Latest member
cchristian6

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top