Specify delimiter and quote on SQL Export Statement 1

[Swi]

Is there any way to specify the delimeter and qualifying quotes on an export from an SQL command?

    If fso.FileExists(FilePath & "XXXX_" & Format(Date, "MM-DD-YYYY") & ".txt") Then fso.DeleteFile FilePath & "XXXX_" & Format(Date, "MM-DD-YYYY") & ".txt", True
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & App.Path & "\" & MDBFileName & "'"
    conn.Execute "SELECT * INTO [Text;Database=" _
                     & FilePath _
                     & "].[XXXX_" & Format(Date, "MM-DD-YYYY") & ".txt] " _
                     & "FROM [Final]", , _
                       adCmdText Or adExecuteNoRecords

Thanks.

Swi

 

[strongm]

Yes, there is. schema.ini is your friend

 

[Swi]

Ok, thanks. Will look into that. What are your thoughts on this?

        conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & App.Path & "\" & MDBFileName & "'"
        rstxt.Open "SELECT * FROM [Final]", conn
        Set OutStream = fso.OpenTextFile(FilePath & "Test_" & Format(Date, "MM-DD-YYYY") & ".txt", ForWriting, True)
        Dim HeaderTxtFnl As String
        HeaderTxtFnl = ""
        Dim z As Integer
        For z = 0 To rstxt.Fields.Count - 1
            HeaderTxtFnl = HeaderTxtFnl & rstxt.Fields(z).Name & vbTab
        Next
        OutStream.WriteLine Left(HeaderTxtFnl, Len(HeaderTxtFnl) - 1)
        OutStream.Write rstxt.GetString(adClipString, , vbTab, vbCrLf, "")
        OutStream.Close
        rstxt.Close
        conn.Close
        Set conn = Nothing
        Set rstxt = Nothing
        Set OutStream = Nothing

Swi

 

[strongm]

Well, it is a viable approach - albeit a little clunky (and doesn't appear to meet your requirement to specify qualifying quotes)

 

[strongm]

If you have successfully run the command from your original post, you should find a schema.ini has been created for you in FilePath folder.

The vague outline of what you need to do is then:

All you need to do is make sure that you append an entry for

[XXXX_" & Format(Date, "MM-DD-YYYY") & ".txt]

with settings like the following

Format=Delimited(*)
TextDelimiter='


And that's it. Your CSV will then have (in this case) as * delimiting each column, and text will be qualified by a single quote

 

[Swi]

Thanks.

Swi