none
Is it possible to change the default export specification to tab deliminated rather than comma deliminated RRS feed

  • Question

  • Hi,

    I have to export a large number of tables into Tab deliminated text files. The default is comma separated so at the moment I can see no other way than to create a large number of specifications which can be referenced by TransferText.

    Headers are not required and all fields are text so I can get away with reducing the number of specifications by naming the fields Field1, Field2, Field3 etc and calling the export specification Spec33, Spec41 etc with the number referring to the number of fields, but this is clumsy and requires users to create a specification where one does not currently exist.

    Ideally I would simply like to change the default delimiter to {Tab} but can't see how to do this either through Access options or VBA. The alternative which would also be acceptable is to build a specification in VBA but again, I can't find the export specification as an object.

    I'm using Access 2002-3 for this exercise but anticipate this will be upgraded to Access 2010 at some point in the not to distant future.

    Any suggestions gratefully received

     


    CJ_London
    Thursday, September 29, 2011 10:19 PM

Answers

  • Hi,

    I have to export a large number of tables into Tab deliminated text files. The default is comma separated so at the moment I can see no other way than to create a large number of specifications which can be referenced by TransferText.


    CJ_London


    Hi CJ_London,

    If you are familiar with VBA, it should not be too difficult to make your own - generalized - export routine for any table.

    Take the table name as paramater. Then loop through all the records in the table, and per record loop through the fields of the table. The field values can be separated by the separator of your choice (second parameter). After each record the string with field values is written to an external file.

    Within the same systematics you can start with a line of the field names.

     

    Imb.

    • Marked as answer by Bruce Song Tuesday, October 11, 2011 3:37 AM
    Monday, October 3, 2011 4:46 PM
  • The specification name for the set of options that determines how a text file is imported or linked. For a fixed-width text file, you must either specify an argument or use a schema.ini (schema: A description of a database that defines the attributes of the database, such as tables, fields (columns), and properties.) file, which must be stored in the same folder as the imported or linked text file.

     

    Check out this link

    http://www.utteraccess.com/forum/DocmdTransferText-Schem-t1592204.html

     


    Chris Ward
    • Marked as answer by Bruce Song Tuesday, October 11, 2011 3:37 AM
    Friday, September 30, 2011 2:55 PM

All replies

  • Hi CJ_London,

     

    Thats possible go through to manualy Export the Table to a Text file following the Export wizard and change the delimiter to Tab.

    See examples in below links:

    http://www.blueclaw-db.com/export-specifications.htm

    http://www.btabdevelopment.com/ts/03expspec

     

    You can then save the Export spec, and use it in your TransferText command.

     

    Hope this helps,
    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, September 29, 2011 11:53 PM
  • Hi Danishani,

    Thank you for responding.

    That is actually what I am doing at the moment. The problem is that the only change I require from the 'standard' default (which is the one transfertext will use if a specification is not specified) is to use a {tab} delimiter rather than the default comma. I have several hundred files which need to be exported which at the moment, because of the {tab} requirement means I have to manually create a specification for each one - I can standardise on field names (because headers are not required) but if the number of fields do not match the specification, the export will fail.

    What I am really trying to find is a way of changing the default delimitter permanently

    Many thanks

     

     


    CJ_London
    Friday, September 30, 2011 1:18 PM
  • Here is a link for setting the specifications in VBA

    http://msdn.microsoft.com/en-us/library/ff835958.aspx

    good luck


    Chris Ward
    Friday, September 30, 2011 2:36 PM
  • The specification name for the set of options that determines how a text file is imported or linked. For a fixed-width text file, you must either specify an argument or use a schema.ini (schema: A description of a database that defines the attributes of the database, such as tables, fields (columns), and properties.) file, which must be stored in the same folder as the imported or linked text file.

     

    Check out this link

    http://www.utteraccess.com/forum/DocmdTransferText-Schem-t1592204.html

     


    Chris Ward
    • Marked as answer by Bruce Song Tuesday, October 11, 2011 3:37 AM
    Friday, September 30, 2011 2:55 PM
  • And yet one more link

    http://www.utteraccess.com/forum/Error-3625-Schemaini-fil-t1949311.html

    Lots of code here


    Chris Ward
    Friday, September 30, 2011 3:27 PM
  • Hi Chris,

    Thanks for your responses, after much digging I finally got something to work based on this link

    http://social.msdn.microsoft.com/Forums/en/vblanguage/thread/f4abe22c-db28-4f3b-8c6d-50bb05b2e583.

    I used the test code DoCmd.RunSQL "SELECT * INTO [text;FMT=TabDelimited;HDR=NO;database=C:\].[TestFile#txt]FROM Test_Table;"

    Even then, the delimeter was still coming up as a comma - the FMT=TabDelimited; was ignored, but the HDR wasn't. However creating a Schema.ini with Format=TabDelimited solved the problem.

    So all I need to do now is create a new Schema.ini with the file name and Format specified before running the export and then delete it afterwards

    Many thanks for pointing me in the right direction

     

    CJ


    CJ_London
    Sunday, October 2, 2011 11:13 PM
  • Your welcome,

    Good luck with it.

    Also if you wouldn't mind voting something as helpful if it helped you?


    Chris Ward
    Monday, October 3, 2011 4:02 PM
  • Hi,

    I have to export a large number of tables into Tab deliminated text files. The default is comma separated so at the moment I can see no other way than to create a large number of specifications which can be referenced by TransferText.


    CJ_London


    Hi CJ_London,

    If you are familiar with VBA, it should not be too difficult to make your own - generalized - export routine for any table.

    Take the table name as paramater. Then loop through all the records in the table, and per record loop through the fields of the table. The field values can be separated by the separator of your choice (second parameter). After each record the string with field values is written to an external file.

    Within the same systematics you can start with a line of the field names.

     

    Imb.

    • Marked as answer by Bruce Song Tuesday, October 11, 2011 3:37 AM
    Monday, October 3, 2011 4:46 PM