DoCmd.TransferText acExportDelim puts quotes around all data
-
Why does the DoCmd.TransferText acExportDelim, , "dbo.tempSequence", "A:\" & RunsheetID & ".csv", False method put quotes around all the data? Help says you can create your own specification file by using the Export wizard on the file menu, but apparently they turned it off in Access 2003. In the wizard on the advanced options you can select {none} for text qualifier, but I can't figure out how to do this using the DoCmd.TransferText acExportDelim method. Does anyone know anything about this?
-
Hi macrocharlie,
I just did a quick test on one of my databases and found the same. It appears as though Access defaults the delimiter to a comma when using acExportDelim, my machine at the time had the regional settings to be a pipe "|" so when I opened the csv in Excel it was showing a) everything in the same cell and b) text with quotes.
I changed the regional settings on my machine to have a delimiter of a comma and opened the csv in Excel again and it's now all correct, text doesn't have quotes and everything appearing in it's own cell.
Worth a look
-
Thanks for the reply. It's not the delimiter that's the problem. It's that it puts quotes around all data since all of my exported columns are nvarchar. Here's an example:
"Bracket Type=4",,,,,,,,,,,,,,,,,,,,
"Sample Type","File Name","L5 Notebook Number","Sample ID","Path","Instrument Method","Sample Vol","L4 Study Name","Level","Sample Name","Position","Inj Vol","Process Method","Comment","Calibration File","Sample Wt","ISTD Amt","Dil Factor","L1 Run No","L2 Analyst","L3 StudyTracker"
This is what I want it to look like:
Bracket Type=4,,,,,,,,,,,,,,,,,,,,
Sample Type,File Name,L5 Notebook Number,Sample ID,Path,Instrument Method,Sample Vol,L4 Study Name,Level,Sample Name,Position,Inj Vol,Process Method,Comment,Calibration File,Sample Wt,ISTD Amt,Dil Factor,L1 Run No,L2 Analyst,L3 StudyTracker
-
Hi again,
Ok - another try
I don't have 2003 to hand I'm afraid but looking at 2002.Have you tried exporting the file manually by right-clicking on the table/query you wish to export? Once I change the filetype to csv and click on OK to export, it then pushes me through to the Export Wizard.
From that point onwards I think you'll know what to do, ie you can change all the text qualifiers / delimiters, whatever, and save that as a spec.
Hope that works for you
-
Thanks, yes I tried that and it works great. If you click on the Advanced button you get a screen where you can set Text Qualifier to ", ' or {none}. {none} is what I want but I can't find anyway to do this in code. It must be done in a procedure since other users will be using this and I don't want them to use the Export wizard. If anyone knows of a way to use {none} with the DoCmd.TransferText acExportDelim method, please let me know.
-
Hi macrocharlie,
once you get to that point you can save what options you want as a spec and call it a name which you can use in your vba code.
ie - when in advanced do all your changes and click on save, it will ask you for a name. From that point onwards, you can use that in the vba export function that you're putting together for the users, the users wouldn't need to have any involvement.
Prod me if I'm not clear
Cheers
-
also as far as I'm aware - this cannot be set via code, you have to do it this way first and save your export spec
-
Carl
What I was hoping to do was to do as you say and export it manually and save the Spec file from that manual export. You can then list that Spec file in the method so it will use it. The problem is the Save As and Spec butttons are grayed out when I do this. I am using a project (ADP) so maybe you can't do this in a project.
-
ahh - I'm not so sure about adp's. Thanks to my joys of tying Access to Sybase I'm forced to use mdb's with tons of VBA to make all my connections
You do still have one more option though - do you have the ability to export these tables / queries to another mdb file (for now)?
Providing that you can, export your data to another local database and create the export specs in there- you *should* (again - I don't know whether the adp will allow it) be able to go back to the adp and import the specs from your mdb.
Other than that - I'm at a loss I'm afraid.
Fingers crossed for you.
-
found some information you probably didn't want to hear:
http://support.microsoft.com/default.aspx?scid=kb;en-us;313895
Trust M$
-
Carl
Just thought I'd let you know I finally created a solution to this problem. Since creating Schema.ini options are grayed out in Microsoft Access Projects I created one using code:
Sub CreateSchemaIni(SequenceName As String)
Dim FirstLine As String
FirstLine = "[" & SequenceName & ".csv]" 'setup ini for filename
Dim fs As FileSystemObject 'must reference Microsoft Scripting Runtime
Dim a As TextStream
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("a:\schema.ini", True) 'filename and path, overwrite if already there
a.WriteLine FirstLine 'start writing file
a.WriteLine ("ColNameHeader=False") 'no headers
a.WriteLine ("CharacterSet=ansi") 'use ansi char set
a.WriteLine ("Format=CSVDelimited") 'comma delimted
a.WriteLine ("TextDelimiter=none") 'no quotes around text data
a.Close
End Sub
Note: If the disk does not have a Schema.ini file with the csv filename in it, Access automatically creates one or overwrites the one that's there if it doesn't have the csv filename in it
-
ah good work mate.
Glad you solved it
-
I want to thank all of you who responded to the original question. I had the same problem with the quotation marks. Because of your diligence and conversation, I was able to resolve my quatation mark issue as well.
Thanks.
-
You're welcome!
-
What would the equivalent of
a.WriteLine FirstLine
be if I wanted to use
Dim a As ADODB.Stream
rather than
Dim a As TextStream
?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply