Text Qualifier

lwibisana

Registered User.
Local time
Today, 11:51
Joined
Dec 9, 2010
Messages
13
Hi,
Can some body help me please.
I am trying to export from access table to csv file using
docmd.transfertext acexportdelim,,"TableA","myfile.csv"
how ever it came with the double quoute in between the field ("firstname","lastname").
I need the format without the double quote (firstname,lastname)
I have googled and it point to schema.ini. I don't know how to get this, and how to use it. I am really appreciate your help.
Thanks.
 
You have to setup an import specification...
In the menu go:
File> Get external data

Import your file by hand, once done dont press the "Finish" button but instead choose "Advanced" and make sure to set the text qualifier, click on "Save as"... and give the specification a name.
You use this specification in your Docmd.TransferText and you will be good to go :)
 
.csv files use double quotes to denote string fields. You want to export the strings *without* using the required double quote delimiters on the string?
 
Last edited:
after I exported and save the spec name, how do I use it :confused:.
I use docmd.transfertext "specname","TableA","Myfile.csv" but it didn't do anything at all :(.
and I want to export without the string at all.
 
You want an export file that does *not* have double quotes in it, right? I'm trying to understand what you need in the output file. What will you be doung with this exported file? Importing it into something else?
 
Yes, I am trying to export to csv file that doesn't have the double qoutes.
 
As I asked earlier: what will you do with the file that you export? Import to something else?
 
are you saying the double quotes can't be removed from the field?? if I export from ms-access directly to csv file, I can choose text delimiter to none. so, I think there is a code in docmd.transfertext to exclude those double quotes.
I am not sure about the other application. but the things is they need the format without double quotes.
 
You may have to do this in code

Code:
Public Function SendToCSV(sFileName As String,sPathName As String)

Dim Rs As DAO.RecordSet
Dim ff As Long
Dim nIndex As Integer
Dim sStr As String

Set Rs = CurrentDb.OpenRecordset("YourTableNameHere")

ff = FreeFile

Open sPathName & "\" & sFileName For Output As #ff
 
Do Until Rs.EOF
   '/Loop though all the fields for each record and concat them together with a comma delimiter only.
   '/Note:The Trim(Rs(nIndex) & "") syntax contends with Null or ZLS fields

   For nIndex = 0 To Rs.Fields.Count -1
        sStr = sStr &  "," & Trim(Rs(nIndex) & "")
   Next nIndex

   '/Drop leading comma
   If Left(sStr,1) = "," Then
      sStr = Mid(sStr,2)
   End If

   '/Write record to the csv file

   Print #ff, sStr

   '/Reset the sStr to ""

   sStr = ""
   Rs.MoveNext
Loop
Close #ff
Rs.Close
Set Rs = Nothing

End Function

Save the function in a standard module (not same name as function)

Then in the immediate window or on an OnClick Event of a command button

Immediate Window:
Code:
?SendToCSV("YouFileNameHere.csv","C:\YourPathHere")
Command Button
Code:
Call SendToCSV("YouFileNameHere.csv","C:\YourPathHere")

The above code is aircode and as such is untested. Remember to subsitute you names for the example names used in code.
 
Thanks Dave, I'll try your code.
so there isn't anything in docmd.transfertext??
 
Do not use it often so cannot comment.
What version of Access are you using?
 
I am using access 2007, however the database still 2003. (.mdb)
 
So has it given you what you wanted?
No Errors:eek:
 
are you saying the double quotes can't be removed from the field?? if I export from ms-access directly to csv file, I can choose text delimiter to none. so, I think there is a code in docmd.transfertext to exclude those double quotes.
I am not sure about the other application. but the things is they need the format without double quotes.

Have you tried using an Import/Export specification like I suggested? You need to use a specification that will allow you to set the "None" your talking about.

By not using a specification access uses its default settings.
 
Modification to earlier function:
This inserts the column headings into the output file upon request
Code:
Public Function SendToCSV(sFileName As String,sPathName As String, [COLOR="Blue"]blnHeadings As Boolean[/COLOR])

Dim Rs As DAO.RecordSet
Dim ff As Long
Dim nIndex As Integer
Dim sStr As String

Set Rs = CurrentDb.OpenRecordset("YourTableNameHere")

ff = FreeFile

Open sPathName & "\" & sFileName For Output As #ff

'/Additional Code: 
[COLOR="Blue"]'/Does the user want to include column headings in the first row
If blnHeadings = True Then

   For nIndex = 0 To Rs.Fields.Count -1
        sStr = sStr &  "," & Trim(Rs(nIndex).Name & "")
   Next nIndex
   '/Drop the leading comma
   sStr = Mid(sStr,2)
   Print #ff, sStr
   sStr = ""
End If[/COLOR]


Do Until Rs.EOF
   '/Loop though all the fields for each record and concat them together with a comma delimiter only.
   '/Note:The Trim(Rs(nIndex) & "") syntax contends with Null or ZLS fields

   For nIndex = 0 To Rs.Fields.Count -1
        sStr = sStr &  "," & Trim(Rs(nIndex) & "")
   Next nIndex

   '/Drop leading comma
   If Left(sStr,1) = "," Then
      sStr = Mid(sStr,2)
   End If

   '/Write record to the csv file

   Print #ff, sStr

   '/Reset the sStr to ""

   sStr = ""
   Rs.MoveNext
Loop
Close #ff
Rs.Close
Set Rs = Nothing

End Function
 

Users who are viewing this thread

Back
Top Bottom