Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamicaly transfer table to text with no quotes and not comma-delimit 2

Status
Not open for further replies.

Katya85S

Programmer
Jul 19, 2004
190
I'm transferring table to the txt file, but have to have no quotes around each field and have '|' delimiter instead of comma-delimiter. Is there a way to do this programmatically?
P.S. I transfer the table using DoCmd.TransferText command.

I would appreciate any advice.
Thank you all in advance.
 
Hello:

You can use Access to transfer the table information to a text file, then open up Microsoft Word and used the Find and Replace method to remove the quotes and commas.

Regards
Mark
 
Manually export once. Use the 'Advanced' tab of the 'Export File Wizard' to save a specification file.
Then use that spec file for all of the future exports.


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill
 
thank you guys for the replay.
I need a dynamically set delimiters though, thus cannot use mhartman1's advice.
Transferring data manually, of course went successful, just what i needed, but using that file for the future dynamic TransferText command didn't do any good. The table was transferred with all the commas and quotes, as previously...
 
Here are all of the steps:

Click on table to export.
Click export.
Click type (text)
Choose location.
Hit Export button.
Choose Delimited.
Choose Next.
Choose other in top row and enter the "|" (pipe) as the delimiter. Choose 'Include field names' (if you want). Choose text qualifier 'none' in the second row. Choose the 'Advanced' tab.
Choose 'Save As'. (default will be 'TblName Export Specification' where TblName is the table in question.)
Hit OK.
Hit 'finish' (or cancel) at this point.

You now have a saved specification file. You never need to do this again (unless the table layout changes).

To use:
Code:
DoCmd.TransferText acExportDelim, "[b]TblName Export Specification[/b]", _
    "External Report", "C:\Txtfiles\April.doc"
Questions?


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill
 
I see what i did wrong...
Thanks much Greg. Yes, it works!
Many thanks! :)
 
Doesn't work for me. :-(
And why?
=>Because it seems to store the name of the export file in the specification.
I am trying to programmatically export 20 queries to tab-delimited text files, no text qualifier.
Doesn't work.
I know I could also do it by opening a recordset, cycling through the records/fields and outputting the contents tab-delimited to a text file, but that is somewhat..... stodgy, isn't it?

I have conducted a search through my harddrive trying to find a file holding these specifications, in hope I could manipulate it (throw out the "save as" file name).
No avail.

Any ideas?

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]
 
The specifications are stored in two system tables:
MSysIMEXSpecs and MSysIMEXColumns.

Hope This Helps, PH.
FAQ219-2884 or FAQ181-2886
 
Unfortunately not.
I've looked into these tables, and the destination file name is not stored in them.

However, the export still creates no text file except for the one query with which I had created the specification.
[sadeyes]
Am I missing something?
Here's the crucial part of my code:
Code:
alltabs = Array(///names of the queries I wish to export///)
langs = Array("German", "Spanish", "French", "Italian")
...
For i = 0 To UBound(alltabs)
    Set tbl = db.TableDefs(alltabs(i))
    For j = 0 To UBound(langs)
        thisone = "Export_" & alltabs(i) & "_" & langs(j)
        ssql = "SELECT " & tbl.Fields(0).Name & ", English, " & langs(j) & " FROM " & tbl.Name
        Set que = db.CreateQueryDef(thisone, ssql)
        DoCmd.TransferText acExportDelim, "exp", thisone, CurrentProject.Path & "\" & thisone & ".txt", True, , 65001
    Next j
Next i

MsgBox "All Tables exported!"

I have 5 queries, with shall be exported into 4 separate files each (language pairs), so there'll be 20 export files.

Without the spec, all works fine, but I'll get only semicolon-delimited text files with quotes.
However, one table holds text containing semicola and quotes.

As my other macros and tools all work with tab-delimited text without qualifier, that is what I need.
Alas: the spec doesn't help me so far.
[sadeyes]

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]
 
Go it!

Instead of a specification, i have to use a "schema.ini" file.
In this, the file name needs to stored, too.
That however can be solved by created the schema.ini in code just before exporting.

My slightly extended export block now looks like this:
Code:
For i = 0 To UBound(alltabs)
    Set tbl = db.TableDefs(alltabs(i))
    For j = 0 To UBound(langs)
        thisone = "Export_" & alltabs(i) & "_" & langs(j)
        '************************
        'Create export schema.ini
        a = FreeFile
        Open CurrentProject.Path & "\schema.ini" For Output As a
            Print #a, "[" & thisone & ".txt]"
            Print #a, "ColNameHeader = True"
            Print #a, "CharacterSet = 1252"
            Print #a, "Format = TabDelimited"
            Print #a, "TextDelimiter = None"
        Close a
        '*************************
        ssql = "SELECT " & tbl.Fields(0).Name & ", English, " & langs(j) & " FROM " & tbl.Name
        Set que = db.CreateQueryDef(thisone, ssql)
        DoCmd.TransferText acExportDelim, , thisone, CurrentProject.Path & "\" & thisone & ".txt", True, , 65001
    Next j
Next i
MsgBox "All Tables exported!"

Et voilà!

Life can be easy - if you now how to live it....
:p

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top