asked on

Need a way to export ms access table to text with tab delimitation using vb sql call?

I have the following sql statement that I use right now to export a database table into an excel spreadsheet using an automation process. Unfortunantly not all can be exported as excel files and instead need to be exported as a text file with tab delimitations. Can anyone help me change the sql statement I included below to make it instead export to a tab delimited text file. You help is welcomed. Thanks,

Dim DAODB As Database

'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
Set DAODB = OpenDatabase(strDB)

'If excel file already exists, you can delete it here
If Dir(strExcel) <> "" Then Kill strExcel

DAODB.Execute ("SELECT * INTO [Excel 8.0;DATABASE=" & strExcel & "].[" & _
strWorksheet & "] FROM " & "[" & strTable & "]")
DAODB.Close

Here is quickie, but you will need to reformat the output yourself. Delete every other line amd strip white space and the "|" character as a field delimiter.

DoCmd.OutputTo acOutputTXT, "TableName", acFormatTXT, Access.CurrentProject.Path & "\TableName.TXT"

Regards, P1

uncensored

ASKER

I already know that way, that is in access. I need to do this through VB instead for automation in my VB6 program.

What are you trying to export to, that needs a tab delimited format? Also please explain format desired?

It is not that hard to write a output formatter running off a ADO query for what you want.

What version of Access are we talking about?

Regards, P1

uncensored

ASKER

ok it is access 97 and what I need to do is basically export a table into a tab delimited text format. I know I can loop through the fields to export but I didn't want to have to do that if there is an easier way using a sql statement like the one I included in my first example above that will export a table into an Excel 97 spreadsheet. In my example above it takes less time to export this way then it would to loop through the fields, so what I really want is a modified version of the sql statement that will export to a text file instead of and Excel spreadsheet with tabs. I hope this explains it somewhat better if not see below but I know this does not work since I tried already!

DAODB.Execute ("SELECT * INTO [Wordpad;DATABASE=" & strDoc & "] FROM " & "[" & strTable & "]")

For me personally, The quickest way between what you want and the programming to get you there is the text output from Access and re-format it with VB to the final format.

Using the 1st ( check ) and 3rd ( double check ) lines build a field width array using the "|" character as the delimiter. This allows dynamic outputting when you change number of fields ( or widths ) in the MDB. This is to help you in case you have "|" in the text of your fields. Then decide how much format checking you want to do. Then parse the file every other line using the field width array to delimit cells and checking for the "|" character. Then "Trim" your data cells and output with vbTab between them.

You will not believe how fast VB does the re-formatting. I do this all the time, glueing data from one system to another when compatable data formats are un-available.

If you want and can wait a bit, I'll write the re-formater for you.

Regards, P1

CleanupPing

Hi uncensored,
This old question (QID 20561136) needs to be finalized -- accept an answer, split points, or get a refund. Please see http://www.cityofangels.com/Experts/Closing.htm for information and options.

ASKER CERTIFIED SOLUTION

YensidMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cdiscenna

Was This Question ever answered ?