Create queries and export them to different tab delimited text files

[vmenon]

Dear All,

Firstly let me describe my table structure to you:

Employee Name Telephone City
(There are around 15000 employees and approx. 300 cities)
​

What I would like to do:

• Create a query based on the city name (so there would be around 3000 queries)
• Save these queries based on their city names (e.g qryBombay, qryCelhi etc.)
• Export each of these quesries into a seperate tab delimited test file under "C:\EmployeeList\" folder (e.g. c:\EmployeeList\Bombay.txt ; c:\EmployeeList\Delhi.txt)

Is this possible? Can the above mentioned steps be run via a macro. I need to create these files on a monthly basis and a macro would be of life-saver to me.

Thanks & Regards,
Vinod Menon

 

[DCrake]

What you want to is acheivable but you may need to use vba to fully automate it. How confident are you with vba?

 

[vmenon]

Hi DCrake,

I'm a newbie in VBA.

Regards,
Vinod Menon

 

[DCrake]

In theory this is what is required

Create a query from the employee table that is grouped by city

Create a function that uses this query as a recordset

Loop through this recordset one at a time

Read the name of the city

Create a further recordset that is based again on the employee table filtered for the current city

Use the Open File For Output method to copy the employee data to a nominated text file
Again you would step throught this recordset one record at a time outputting the information to the text file. When you get to the end you close the text file.

Then you move to the next city in the first recordset and repeat the above steps.

When you have tested it and it words correctly you would create a macro that would call this function.

That's it in a nutshell. Not an easy task for a beginner though.

David

 

[DCrake]

Go on, I'm being generous today. Here is the code you need.

Public Function TextFileCreator(strTable As String, StrField As String)
'*************************************************************************************************************************
'Created by :D Crake Xcraft Limited
'Date       :25th June 2009
'Arguments  :strTable    - Name of table being queried
'           :StrField    - Name of field in table acting as grouper record
'
'Purpose    :Group a single table by a specified field and then use this information to create individual text files
'           :based on the field being grouped.
'           :The newly created text file can then be used to import the raw data from.
'Comments   :Files are saved in the current project path. This can be changed by the end user to be elsewhere
'Example    :TextFileCreator("TblEmployees","City")
'Issues     :Does not validate the existance of the table or that the field is named correctly
'**************************************************************************************************************************
'Copywrite  :There is no copywrite on this code, however, if passed on then pass on the above comments.
'**************************************************************************************************************************
Dim tPath As String
Dim tFile As String
Dim vItems As String

Dim Rs1 As DAO.Recordset
Dim Rs2 As DAO.Recordset

'This is where the files are being stored. This can be changed by the user to point to a different location
tPath = CurrentProject.Path

'Create the SQL to create a group by query sorted by the selected field

Set Rs1 = CurrentDb.OpenRecordset("Select " & StrField & " From " & strTable & " Group By " & StrField & " Order By " & StrField & ";")


'Check for any records in table
If Not Rs1.EOF And Not Rs1.BOF Then

    Do Until Rs1.EOF
        'Create a second SQL for the selected field contents in the grouped query
        Set Rs2 = CurrentDb.OpenRecordset("Select * From " & strTable & " Where " & StrField & " ='" & Rs1(StrField) & "'")
        'Create the destination path
        tFile = tPath & "\" & Rs1(StrField) & ".txt"
        'Check if it already exists, if so, delete it first
        If Dir(tFile) <> "" Then
            Kill tFile
        End If
        'Let the user know something is happening
        'If done from the immediate window the following line is ok
        Debug.Print "Working on " & tFile & ", please wait..."
        'if message to appear on status bat then use the next line
        DoCmd.Echo True, "Working on " & tFile & ", please wait..."
        
        'Create a new file and Open the text file
        Open tFile For Output As #1
        'Loop through all the matching records for the outer recordset
        Do Until Rs2.EOF
            'Create a string of values for each field in the table record
            'Using the Nz() function to trap any Null fields
            For nindex = 0 To Rs2.Fields.Count - 1
                vItems = vItems & Nz(Rs2(nindex), "") & ","
            Next
            'Drop the last comma from the string
            vItems = Left(vItems, Len(vItems) - 1)
            'Write the single record to the text file
            Print #1, vItems
            'go to the next record i the sub set
            Rs2.MoveNext
        Loop
        'No more for this group so close the file
        Close #1
        'give the system time to close the file before moving onto the next item
        DoEvents
        
        Rs2.Close
        'Go to the next record in the main query
        Rs1.MoveNext
    Loop
    'Go to end of table
    Rs1.Close
    
End If
'Release the memory
Set Rs1 = Nothing
Set Rs2 = Nothing

        
            

End Function

David

 

[namliam]

Dont mean to put a crimp in your coding and idea David, how about making a Parameter query... Feeding the city to the parameter and executing an export of said query?

Should be MUCH faster as well as easier to code and maintian

 

[DCrake]

I agree with the concept but the user would have to sit there typing in the parameter for each city wouldn't he?

My method overcomes that issue and also handles existing files as well. But to each their own.

David

 

[namliam]

No he wouldnt....

Use a group by query to find all the cities (in this case)
Have a template query:
Select * from yourTable where City = "<InsertCityHere>"
Save this as qryExport_template
Save this again as qryExport

Now in code loop the cities
Repalce <insertcityhere> by the real city and write the SQL to qryExport
TransferText qryExport
Rince and repeat

 

[vmenon]

Hi Namliam,

Would it be possible for you to create a sample database with the code and post it for my reference?

Regards,
Vinod Menon
vinu21may@gmail.com

 

[namliam]

Go into Module1 and run/look at the module ExportIt

All of the actual code:

Sub exportIt()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select distinct Field1 from tblYourTable")
    Do While Not rs.EOF
        CurrentDb.QueryDefs("qryExport").SQL = Replace(CurrentDb.QueryDefs("qryExport_Template").SQL, "<PutCityIn>", rs!Field1)
        DoCmd.TransferText acExportDelim, "qryExport_Spec", "qryExport", myFolder & rs!Field1 & ".txt"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub

Good luck!

 

[vmenon]

Hi Mailman,

Thanks a lot for your help. The db worked fantastically. Now the database has grown big and has over 6000 cities in it. I tried to follow the same procedure but the systems always hangs. Also it was quite difficult to search for a particular city and find out the details on the same. So had created a enw DB and the cities are all linked tables from another DB.

I would like to export the recrds based on employee location into seperate text files and would like the text files to be named as StateAbbr-City (i'e' State Abbreviation&"-"&City Name. Each text files should contain the Employee Name, Employee ID, MSISDN and the Employee location.

I tried all sorts of permutations & combinations but was not successful. Request your help for the same.

I am attaching herewith the satabase file for your reference.

Warm Regards,
Vinod Menon
vinu21may@gmail.com

 

[namliam]

The question lies in this, what does this query:
Select distinct empLoc from tblEmp

Retrieve for information? How is it formatted and how do you use it in the eventual output query...

I doubt the 'process' would hang your pc on 6000 iterations, though it will become increasingly slow... Which unfortunately cannot be helped.
If one city takes 2 seconds to export, then 6000 cities take 2 * 6000 = 12000 seconds = 200 minutes = 3+ hours Nothing one can do to change the speed...

You could maybe try opening the query one time then manually creating files using "Open", "write" and "Close" to create the file, write the data and close the file.
Manual operations though are usually slower

Another alternative you can try if you are working from/to the network, is to write into a temporary local folder (C:\Temp\Export or something simular) then move all files from there to the network... This may increase performance... or it may actaully slow it

 

[marjonp]

Hello! I am attempting to use the logic you provided, only instead of exporting delimited, i am exporting fixed width reports. I am importing one large text file, then exporting multiple text files based on a field called Origin. When attempting the export command, Access gives me this error: "The Microsoft Office Access database engine could not fine the object "095.txt' (095 is the first Origin in my db table). Make sure the object exists and that you spell its name and the path name correctly."
I have tried manually putting the object out in the directory and leaving it out of the directory. In boths instances Access gives me the same error message. I know access can find the location because when i leave the object in the directory, Access deletes it.
Any ideas on what function i am missing to get access to write out the data?
Here is my logic. All queries referenced exist in my db.
Sub exportIt()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select distinct Origin from tblAREF_Combined_In")
Do While Not rs.EOF
CurrentDb.QueryDefs("qryExport").SQL = Replace(CurrentDb.QueryDefs("qryExport_Template").SQL, "<PutOriginIn>", rs!Origin)
DoCmd.TransferText acExportFixed, "AREF_Layout_Specification", "qryExport", "\\mydir\" & rs!Origin & ".txt"

rs.MoveNext

Loop
rs.Close
Set rs = Nothing
End Sub
Thanks for any help you can provide!!

Go into Module1 and run/look at the module ExportIt

All of the actual code:

Sub exportIt()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select distinct Field1 from tblYourTable")
    Do While Not rs.EOF
        CurrentDb.QueryDefs("qryExport").SQL = Replace(CurrentDb.QueryDefs("qryExport_Template").SQL, "<PutCityIn>", rs!Field1)
        DoCmd.TransferText acExportDelim, "qryExport_Spec", "qryExport", myFolder & rs!Field1 & ".txt"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub

Good luck!