Exporting Text Files

[jthornton]

Using a schema.ini file I cannot figure out a way to get rid of the quotes that appear in a comma delimited text file for a text field. I am exporting a table out of Access into a text file. All text fields have quotes around them.

I have looked at the information posted at "http://www.vb-world.net/tips/tip132.html" and the references.

Does anyone have any idea?

Thanks.

Jim

[This message has been edited by jthornton (edited 11-01-1999).]

[Clunietp]

This is unrelated but may solve an underlying problem....

you can test for a quote in VB by using Chr(34)

if mid(mytext, 1, 1) = chr(34) then mid(mytext, 1, 1) = SomeOtherValueProbablyASpace

you can put this in a for/next loop and look for all quotes and replace them.

Tom

[jthornton]

I think I see what you are saying, but it is the long way around.

The data in the text fields of the table that I am exporting do not have quotes. What I am trying to do is create a generic procedure to export text files. If you go to the Import/Export Wizard in Access97 to export a text file, click on the Advanced button and there will be an option for a text indicator. I don't have it in front of me at the moment but it is a combo box where you can choose: 1. " 2. ' 3. none.

I want to be able to create the schema.ini on the fly (which I have done) and then export a comma delimited file without the quotes around the text fields.

Sample Table Date:

InvNum Desc
1001 Fishing Rod
1002 Tennis Racquet

Sample Export File:

1001, "Fishing Rod"
1002, "Tennis Racquet"

In the sample export, you can see what is happening with the quotes.

Any other ideas? (The text file could be up to 200,000 rows.)

Thanks.

Jim

[Clunietp]

I always do this when I need to automate access and need all the correct parameters:

Create what you want to do using a macro in Access. Convert the macro to visual basic (one of MS Access options)

Use that code along with the Access Object library

'create object like this
dim axs as new access.application

'open db
axs.opendatabase "c:\datbasename.mdb"

'add code here from converted macro

'close db
axs.closecurrentdatabase
set axs = nothing

that usually works for me... i may have some of the function names wrong but that should get you started...

Tom

[JHausmann]

If you don't mind doing a 2-step process you could:

1) in Access create a code module that does the following:

DoCMD.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tablename", "c:\your\file\location.here", True

Note: the table name and file name need to be in quotes

2) in Excel open the file created in step1 and save it as a .csv file.

No quotes, no mess. You might be able to export to a .csv file directly. Check the documentation for transferspreadsheet (the constant "acSpreadsheetTypeExcel97" will change if this can be done).



[This message has been edited by JHausmann (edited 11-02-1999).]

[jthornton]

That would work, but my problem still is that this needs to be transparent to the user. Otherwise I would goto the effort of having to create an export specification.

The frustrating thing is that this is close to being an undocumented procedure(actually half documented). I would think that other people would have run across the same thing in wanting to create flexible export files.

I am still waiting to hear back from Microsoft. Thanks for the help so far.

[jthornton]

That would work but that is really not my problem. IF I did this I still need an Import/Export Specification to export a file without the quotes around the text field.

What I am trying to do is create a comma delimited file without the text qualifier and without creating a new export specification everytime.

Still looking for help.

Jim

[vladimir]

I had the same problem. My solution was to use "Print #" (or "Write #") statements in your VB code - that way you have full control of what gets written to the file.
I think, that behavior of MS Access - creating quotes arount text field when exporting into comma-delimited text file is by design. In general text fields may contain commas, so you need quotes to separate them.

Hope that helps.
Best regards, vlad.

[JHausmann]

You can use OLE/Automation to hide the details from the user. Of course this may *require* them to have Excel in order to run your application.

[This message has been edited by JHausmann (edited 11-03-1999).]