×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Specify delimiter and quote on SQL Export Statement

Specify delimiter and quote on SQL Export Statement

Specify delimiter and quote on SQL Export Statement

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

CODE

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

RE: Specify delimiter and quote on SQL Export Statement

Yes, there is. schema.ini is your friend

RE: Specify delimiter and quote on SQL Export Statement

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

CODE

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

RE: Specify delimiter and quote on SQL Export Statement

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

RE: Specify delimiter and quote on SQL Export Statement

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

RE: Specify delimiter and quote on SQL Export Statement

(OP)
Thanks.

Swi

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close