We help IT Professionals succeed at work.
Get Started

Export to text files based on a value

213 Views
Last Modified: 2018-03-21
Export to text files based on the value from SQL table. What I want to accomplish is if Filename Column in SQL table dbo.Employee is (See attached):
1) "Filename1" then export with the name Filename1_today'sdate.txt.
2) "Filename2" then export with the name Filename2_today'sdate.txt.
3) "Filename3" then export with the name Filename3_today'sdate.txt.


Here is my vb.net code that works but only for 1 file.

Private Sub ExportToTXT()

        'CONSTANT RECORDSET:
        Dim conn As New ADODB.Connection
        Dim rsConstantPage As New ADODB.Recordset

        'LOOP RECORDSET:
        '/* EMPLOYEE LOOP */
        Dim rsEmployeeNameLoop As New ADODB.Recordset
        Dim rsEmployeeAddressLoop As New ADODB.Recordset
        Dim rsEmployeeCityStateZipLoop As New ADODB.Recordset
        Dim rsEmployeeEmploymentDateLoop As New ADODB.Recordset

        '/* EMPLOYER LOOP */
        Dim rsEmployerNameLoop As New ADODB.Recordset
        Dim rsEmployerAddressLoop As New ADODB.Recordset
        Dim rsEmployerCityStateZipLoop As New ADODB.Recordset

        '- - - - - - - - - - - - - - - - -

        'PAGE STRING CONSTANT:
        Dim ConstantPage As String

        'SPACE STRING CONSTANT:
        Dim r As String

        'DASH STRING CONSTANT:
        Dim s As String

        'COMMA STRING CONSTANT:
        Dim t As String


       
        


        Try
            ConstantPage = "Page 1"

            '/*SPACE CONSTANT*/
            r = " "

            '/*DASH CONSTANT*/
            s = "-"

            '/*COMMA CONSTANT*/
            t = ","



            conn.Open("Provider=SQLOLEDB;DSN=DATABASE;Data Source=SQLSERVER;Initial Catalog=EMPLOYEEDATA;Persist Security Info=True;User ID=sa;Password=111111;")
            conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient

            Dim fileName As String = "\\NETWORKFOLDER\EMPLOYEE.txt"




            FileOpen(1, fileName, OpenMode.Output)

            'CONSTANT STRING LOOPS:
            rsConstantPage = conn.Execute("[EMPLOYEEDATA].[dbo].[ConstantVariable] WHERE UniqueID = 1", , ADODB.CommandTypeEnum.adCmdTable)

            'PAGE LOOP:
            Do Until rsConstantPage.EOF

                PrintLine(1, s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & r & ConstantPage & r & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s)

                '**************************************


                    'EMPLOYEE NAME LOOP DETAILS:
                    rsEmployeeNameLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee]", , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployeeNameLoop.EOF

                        lsEmployeeame = rsEmployeeNameLoop.Fields("EmployeeName").Value

                        PrintLine(1, vbCrLf & lsEmployeeName)

                        rsEmployeeNameLoop.MoveNext()
                    Loop

                    ' EMPLOYEE ADDRESS LOOP DETAILS:
                    rsEmployeeAddressLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee]", , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployeeAddressLoop.EOF

                        lsEmployeeAddress = rsEmployeeAddressLoop.Fields("EmployeeAddress").Value

                        PrintLine(1, lsEmployeeAddress)

                        rsEmployeeAddressLoop.MoveNext()
                    Loop

                    ' EMPLOYEE CITY STATE ZIPCODE LOOP DETAILS:
                    rsEmployeeCityStateZipLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee]", , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployeeCityStateZipLoop.EOF

                        lsEmployeeCity = rsEmployeeCityStateZipLoop.Fields("EmployeeCity").Value
                        lsEmployeeState = rsEmployeeCityStateZipLoop.Fields("EmployeeState").Value
                        lsEmployeeZip = rsEmployeeCityStateZipLoop.Fields("EmployeeZipcode").Value

                        PrintLine(1, ls EmployeeCity & t & r & ls EmployeeState & r & ls EmployeeZip)

                        rsEmployeeCityStateZipLoop.MoveNext()
                    Loop

                    ' EMPLOYEE EMPLOYMENT DATE LOOP DETAILS:
                    rsEmployeeEmploymentDateLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee]", , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployeeEmploymentDateLoop.EOF

                        lsEmployeeEmploymentDate = rsEmployeeEmploymentDateLoop.Fields("EmploymentDate").Value


                        PrintLine(1, lsEmployeeEmploymentDate)

                        rsEmployeeEmploymentDateLoop.MoveNext()
                    Loop

                    '******************************************

                    'EMPLOYER NAME LOOP DETAILS:
                    rsEmployerNameLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee], , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployerNameLoop.EOF

                        lsEmployerName = rsEmployerNameLoop.Fields("EmployerName").Value

                        PrintLine(1, vbCrLf & lsEmployerName)

                        rsEmployerNameLoop.MoveNext()
                    Loop

                    ' EMPLOYER ADDRESS LOOP DETAILS:
                    rsEmployerAddressLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee], , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployerAddressLoop.EOF

                        lsEmployerAddress = rsEmployerAddressLoop.Fields("EmployerAddress").Value

                        PrintLine(1, lsEmployerAddress)

                        rsEmployerAddressLoop.MoveNext()
                    Loop

                    ' EMPLOYER CITY STATE ZIPCODE LOOP DETAILS:
                    rsEmployerCityStateZipLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee], , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployerCityStateZipLoop.EOF

                        lsEmployerCity = rsEmployerCityStateZipLoop.Fields("EmployerCity").Value
                        lsEmployerState = rsEmployerCityStateZipLoop.Fields("EmployerState").Value
                        lsEmployerZip = rsEmployerCityStateZipLoop.Fields("EmployerZipcode").Value

                        PrintLine(1, lsEmployerCity & t & r & lsEmployerState & r & lsEmployerZip)

                        rsEmployerCityStateZipLoop.MoveNext()
                    Loop


                            rsConstantPage.Close()
                            rsEmployeeNameLoop.MoveNext()
                        Loop


            FileClose(1)





        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Error")
        Finally

        End Try
    End Sub

Open in new window



SQLTables.xlsx



Please help.

Thank you very much.
File1_12272017.txt
File2_12272017.txt
File3_12272017.txt
Comment
Watch Question
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
Unlock 1 Answer and 18 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE