Exporting Query into tab delimited text format

Hello all,

I've been reading code for about a half an hour and understand very little.  Still new at vb.  I need to export a select query as a tab delimited txt file and store it in a specifice folder and I need this file to erase and replace any existing file in that folder.  Is this possible.  Any information would be helpful.  And remember I am new to this, so assume I know nothing, and walk me through it.  Thank you in advance.
JPDuvalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

g_johnsonCommented:
here's a walk through, but you'll have more research to do:

1.  Is this a one time thing?  If so, use DTS in SQL Enterprise Manager
2.  If it must be written in code:
     a.  run a select query, e.g., rs1.open "SELECT * FROM yourtable"
     b.  build your tab-delimited string, e.g.
             for i = 0 to rs1.fields.count - 1
                  strNew = strNew & rs1.fields(i).value & chr(9)
             next 'i
     c.  you can simply open the file for output to write to it and over-write anything that used to be in it
              open "c:\path\filename" for output as #1
              print #1, strNew
              close #1

That's the basics!  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jkaiosIT DirectorCommented:
I assume that you've already declared your variables and set the connection to the database as follows:

  Dim your_ADO_Conn As New ADODB.Connection
  Dim rs1 As New ADODB.Recordset
  Dim strNew As String

Step1: Open/run the select query.
--------
  rs1.Open "SELECT * FROM yourtable", your_ADODB_Conn

Step2: Create and open a new text file to export your data into.
--------
  Open "C:\temp\mydata.txt" For Output As #1

Step3: Loop thru the recordset and output all field values.
--------
  Do While Not rs1.EOF    
    For i = 0 To rs1.Fields.Count -1
      strNew = strNew & rs1.Fields(i).Value & vbTab
    Next
    Print #1, strNew      'output the current record to text file
    strNew = ""             'clear variable before the next record
    rs1.MoveNext           'go to the next record
  Loop
0
jkaiosIT DirectorCommented:
>> I need to export a select query as a tab delimited txt file and store it in a specifice folder and I need this file to erase and replace any existing file in that folder.  Is this possible. <<

When you use the "Output" keyword on the "Open" statement, any existing file will be replaced automatically with the new file.

With your "strNew" variable, better declare it as of type Variant to be flexible to hold any type of data.

I use  the "vbTab" built-in constant which is equivalent to Chr(9) to make your code more self-documented.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

daniel_j_cCommented:
@jkaios - wouldn't recommend using variants - the file is going to get outputted as a string anyway and variant's are incredibly slow.

also, try this for the inner loop:

dim fld   as field
dim sTemp1 as string
dim sTemp2 as string

  Do While Not rs1.EOF    
    For each fld  rs1.fields
      sTemp1 = sTemp1 & fld.value & vbtab
    next fld

    if len(sTemp1) > 2000 then
      sTemp2 = sTemp2 & sTemp1
      sTemp1 = ""
   
      if len(sTemp2)>64000 then
        print #1, sTemp2
       sTemp2 = "'
      end if
    end if

    rs1.movenext
  loop

print #1, sTemp2 & sTemp1

there are 3 optimisations here - if you are dealing with a large numbers of records, you'll notice a massive performance increase:

a) using for each fld in... means that the fld object has already been resolved and an additional object lookup does not have to be done for each field

b) vb's string handling is appaling so when a string gets over 2000 chars or so it's much more efficient to pack those 2000 chars onto another sttring and start the first one again

c) dumping a small amount of data (ie. 1 record) to a file at a time has a big overhead - the most efficient size is between 32K and 64K of data at a time.

0
aikimarkCommented:
You can get your database to do more than just return rows and columns:

rs1.Open "SELECT col1 & chr(9) & col2 & chr(9) & col3 & chr(9) & col4 & chr(9) & col5 As TabDelimitedRow FROM yourtable", your_ADODB_Conn

Note: the chr(9) is the tab character which is being concatenated with the column data.

Your VB code simplifies into
Open "C:\temp\mydata.txt" For Output As #1
Do While Not rs1.EOF    
    Print #1, strNew      'output the current record to text file
    rs1.MoveNext           'go to the next record
Loop

0
daniel_j_cCommented:
good one aikimark - just remember if any of the columns could be null, you need to do this (and in sqlserver its char not chr):

rs1.Open "SELECT isnull(col1,'') & char(9) & isnull(col2,'') & char(9) & isnull(col3,'') & char(9) & ...
0
aikimarkCommented:
I assumed this was an MSAccess database.  As such, the & operator takes care of handling Null value concatenations.  I don't see a need to use the IsNull() function.  Furthermore, the IsNull() function in VB and MSAccess SQL environments only has one parameter.  You might be thinking of the NZ() function.

If SQLServer, then we need to use the + character to concatenate strings.
0
daniel_j_cCommented:
ok - thought this was sql server. in sql if 1 element of an expression is null, the whole expression evaluates to, hence the use of isnull(). isnull in vb has 1 param but sql server has two - 'value' and 'new value'.
0
aikimarkCommented:
The comments about string concatenation speed are true.  If you must do a lot of string concatenations (which you don't in this instance), I'd recommend something like VB.Net's StringBuilder class.  There are several fast concatenation routines available for free download from various sites.  I also wrote my own StringBuilder class to accomodate lots of concatenations, which I will post if anyone is interested in this side topic.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.