Output to a Text File

[Nylex]

I have a product database which we imput all purchses and the cost of each item
Every item is recorded by a Serial No Description and Selling price along
with a lot of other information

Our Accounting software is MYOB and instead of entering new Products into
both the Database and MYOB is there a way that I can output from a table a
Tab Delimited Text File with just "SerialNo" "Description" "SellingPrice" as
MYOB has an import facilitie that allows us to import and update the products
- this would save a lot of duplicate work, also the product can be entered
into the database using one description and the person who puts it into MYOB
uses a different descriptions - this causes problems with our customers as
out DataBase prints the Delivery Docket and MYOB prints and sends the Invoice

Hope I have given enough information

Thanks

 

[Phil]

You need a bit of VBA like -

docmd.TransferText acExportDelim, , "qryCurrentData",
"C:\Temp\CurrentData.csv"

If you have a "created date" field on your record, the field could have a
default value = date(). Then you could have a query set up to filter based on
the date as per above, this would export to the above csv file.

 

['69 Camaro]

Hi.

is there a way that I can output from a table a
Tab Delimited Text File with just "SerialNo" "Description" "SellingPrice"
as
MYOB has an import facilitie that allows us to import and update the
products

There are two ways:

1.) Create a SELECT query with the indicated column names displayed, then
create an export specification for that query that includes tab delimited
text, then use the TransferText method to export the query results to a text
file. Example SELECT query syntax:

SELECT SerialNo, [Description], SellingPrice
FROM tblProducts;

.. . . where the table name is tblProducts.

Example VBA for the TransferText method:

DoCmd.TransferText acExportDelim, "ExportSpec", "qryProducts",
"C:\Data\Products.txt", True

2.) It takes longer to explain this than to do it (copy/pasting these
examples will be even faster), but create a make-table query with the
indicated column names displayed and the results saved to a text file, run
the query, then delete the resulting CSV delimited text file and open the
Schema.ini file and change the following line:

Format=CSVDelimited

To:

Format=TabDelimited

.. . . for the destination file (since there may be multiple file names
listed if you've ever used a query to export data in this directory before),
save the Schema.ini file, then run the make-table query again to get a tab
delimited text file, instead of the default CSV file. The Schema.ini file
is located in the same directory as the resulting text file. Example
make-table query syntax:

SELECT SerialNo, [Description], SellingPrice
INTO [TEXT;HDR=Yes;DATABASE=C:\Data\].Products.txt
FROM tblProducts;

.. . . where the destination file is saved as C:\Data\Products.txt, and the
table name is tblProducts.

Example C:\Data\Schema.ini file that Jet creates for you and you alter the
format to TabDelimited:

[Products.txt]
ColNameHeader=True
CharacterSet=1252
Format=TabDelimited
Col1=SerialNo Integer
Col2=Description Char Width 20
Col3=SellingPrice Currency


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Nylex]

I have never exported so I am very new to this

I have created a SELECT query Named "MYOB" whish has "serialNo"
"ProductName" and SellPrice"

Where do I put your next instructions?

I have also checked the MYOB system and I can inport from a comma seperated
CSV file as well as a TAB delimited file - just have to selcet when importing

If you can explain where your next instruction goes please

Hi.

is there a way that I can output from a table a
Tab Delimited Text File with just "SerialNo" "Description" "SellingPrice"
as
MYOB has an import facilitie that allows us to import and update the
products

There are two ways:

1.) Create a SELECT query with the indicated column names displayed, then
create an export specification for that query that includes tab delimited
text, then use the TransferText method to export the query results to a text
file. Example SELECT query syntax:

SELECT SerialNo, [Description], SellingPrice
FROM tblProducts;

.. . . where the table name is tblProducts.

Example VBA for the TransferText method:

DoCmd.TransferText acExportDelim, "ExportSpec", "qryProducts",
"C:\Data\Products.txt", True

2.) It takes longer to explain this than to do it (copy/pasting these
examples will be even faster), but create a make-table query with the
indicated column names displayed and the results saved to a text file, run
the query, then delete the resulting CSV delimited text file and open the
Schema.ini file and change the following line:

Format=CSVDelimited

To:

Format=TabDelimited

.. . . for the destination file (since there may be multiple file names
listed if you've ever used a query to export data in this directory before),
save the Schema.ini file, then run the make-table query again to get a tab
delimited text file, instead of the default CSV file. The Schema.ini file
is located in the same directory as the resulting text file. Example
make-table query syntax:

SELECT SerialNo, [Description], SellingPrice
INTO [TEXT;HDR=Yes;DATABASE=C:\Data\].Products.txt
FROM tblProducts;

.. . . where the destination file is saved as C:\Data\Products.txt, and the
table name is tblProducts.

Example C:\Data\Schema.ini file that Jet creates for you and you alter the
format to TabDelimited:

[Products.txt]
ColNameHeader=True
CharacterSet=1252
Format=TabDelimited
Col1=SerialNo Integer
Col2=Description Char Width 20
Col3=SellingPrice Currency


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi.

Where do I put your next instructions?

I have also checked the MYOB system and I can inport from a comma
seperated
CSV file as well as a TAB delimited file

If you don't require a tab delimited file, then that's even easier, because
you probably don't need an export specification to create a default CSV text
file. Create a form and add a button to it. In the button's OnClick event,
paste the following code:

DoCmd.TransferText acExportDelim, , "MYOB", "C:\Data\Products.csv", True

Change the path and file name, C:\Data\Products.csv, to whatever you wish.
Save and compile the code. Switch to Form View and press the button, and
the file will be exported for you.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Nylex]

Thank you

That worked perfectly

We have over 2500 products in our database and all our alterations we have
been making was going to be a huge job to bring our MYOB ledger upto date

Your advice I have just created and run and it took 2 mins - that included
created the CSV file and MYOB updating its records

Thank you again

 

['69 Camaro]

Hi.

Thank you

That worked perfectly

You're very welcome.

We have over 2500 products in our database and all our alterations we have
been making was going to be a huge job to bring our MYOB ledger upto date

Your advice I have just created and run and it took 2 mins - that included
created the CSV file and MYOB updating its records

It's always good to know that the computer works for you, not the other way
around.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.