Make Table to CSV, Remove Text Qualifier

nate11000

Registered User.
Local time
Today, 13:01
Joined
Jan 29, 2007
Messages
13
Thanks for looking!

I'm using a query to export a table to a text file, i.e.

SELECT * INTO [Text;HDR=No;Database=C:\].[test#txt]
FROM [TestTbl]

Text fields end up surrounded by double quotes as a text qualifier. Is there a property I can use to get rid of the text qualifier within the SQL (similar to how HDR=No gets rid of the headers)?

I've tried searching and guessing, but nothing has worked yet. Thanks!
 
Search access help and the forum/google for 'export specifications'. These allow you to specify delimiter type, date formats, and text qualifiers amongst other things.
 
Thanks Craig. I can pretty much only find info that relates to using transfertext or manual exports, though, rather than the SQL method I'm using.

I did see a sort of kludge method of using DSN={export spec name}, but I can't make it work. I have a spec named TestEx, and my SQL looks like this:
SELECT * INTO [Text;DSN=TestEx;HDR=No;Database=C:\].[test#txt]
FROM [TestTbl]

I get an error that says "The text file specification 'TestEx' does not exist." I've checked that it does exist in the MSysIMEXSpecs system table, and I've tried to specify it by number using DSN=4 and IMEX=4, but neither worked. Any other ideas?
 
Hmm. Not sure about using that method. I 've always created the query that I want exported and then use transfertext with a specification in a command button on a form to export it to a text file. Is there any reason why you can't use transfertext in your situation?

I've never used the DSN, HDR etc properties before so I'm not sure what to suggest there except maybe to try enclosing the spec name in quotes and see if that works.
ie
SELECT * INTO [Text;DSN="TestEx";HDR=No;Database=C:\].[test#txt]
FROM [TestTbl]
 
Using quotes doesn't work. It seems to be interpreting the 'TestEx' part right, based on the error; it's just not finding the TestEx spec, for some reason.

I can't use TransferText because I'm exporting dynamically created queries/tables from VBA.
 
Well, sorry Nate. Maybe someone else knows more about that than I and will chime in.

Good luck with it.
 
The only way I've seen of removing the text delimiter using this method of export is to change the schema.ini file that is created after the initial export (or create your own schema.ini file prior to export). If you add the line TextDelimter=none to the file (after the format line is where I put mine) and run your export again it will create the file without text delimiters.
 

Users who are viewing this thread

Back
Top Bottom