save csv as pipe delimited using vba

Question

I have a csv file which has data as shown below:

PPIC,11/20/2013 10:23,11431,10963,,Tremors ,
PPIC,11/20/2013 10:23,11431,11592,,"Glioblastoma, Barin ",

the key difference is that row 1 contains a single word (last column), whereas the second row contains data enclosed in double quotes (but comma separated)

This is causing my BULK Import routine to import data wrong for second row. when BULK IMPORT runs, it splits the second row into multiple columns.

I read lots of posts on StackOverflow, and lots of suggestions point out to have a "pipe" delimited file as the input for bulk insert, that will remove any inconsistencies with the quoted text.

How can I convert this comma separated file into a pipe delimited file using a vb excel macro? I want to keep the process automated (where it will take the input csv file, convert it to pipe delimited and then send the file further for importing). OR How can I address the inconsistent quotes to be used whilst doing a BULK Insert?

Any thoughts / help appreciated.

When you create a csv file at that time use pipe as delimited instade of comma. thats the easy way. — Hiten004
@hiten004 the source cannot spit the pipe-delimited data. Thats the limitation. — DataRiver
Is this data coming from any database with sql or query. if yes then you can remove comma before importing data into scv file — Hiten004
@pnuts: here is what my bulkinsert looks like: BULK INSERT dim_assessment FROM '\\server\DIM_Assessment_converted_new.csv' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); GO — DataRiver

John Bustos John Bustos · Accepted Answer · 2013-12-10T22:16:11

This would do it:

Sub MySub()

    Dim FileString As String
    Dim Pattern As String
    Dim ReplacementPattern As String

    Dim ChangedStr As String

    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")

    FileString = "PPIC,11/20/2013 10:23,11431,10963,,Tremors ," & vbCrLf & "PPIC,11/20/2013 10:23,11431,11592,,""Glioblastoma, Barin "","

    Pattern = "(""[^""]*""|[^"",]*)?,"
    ReplacementPattern = "$1|"

    RE.Pattern = Pattern
    RE.Global = True
    RE.MultiLine = True

    ChangedStr = RE.Replace(mystr, ReplacementPattern)

End Sub

Hope this does the trick

save csv as pipe delimited using vba

1 Answers