asked on

Export to text files based on a value

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

Mark Wills

Umm, why not use all SQL Server ?

Why the VB.Net or R for that matter ?

Would you be interested in a T-SQL solution ?

Quenn L

ASKER

I don't know how to use it in T-SQL. Can you show me please?

T-SQL solution is better.

Thank you Mark.

Quenn L

ASKER

@MarkWillis:
No R. Sorry I accidentally add that.

Only VB.net and SQL Server.

Thanks again.

EugeneZ

the next to t-sql best way is SSIS
check this tep-0by-step example
SSIS: Exporting data to a text file using a package
https://decipherinfosys.wordpress.com/2008/07/23/ssis-exporting-data-to-a-text-file-using-a-package/

Quenn L

ASKER

@Eugene Z:

The text files has no Headers and it is a different formatting.

Please see the attachments above.

Thank you for your help.

Quenn L

ASKER

Anyone will help? Thanks.

Mark Wills

Apologies, was interrupted by some business, and then New Year.

Happy New Year to you :)

Will get back into this later tonight my time.

Are there any control breaks ?
If driven by employee - is it possible to have other employers / start dates ?
If driven by employer can we have multiple employees and is there any control break logic/ grouping ?
Can we have multi-pages ?
What is the idea behind dbo.ConstantVariable - seems arbitrary ?

Probably a few more questions, but will await your response.

Quenn L

ASKER

Thank you for responding to me despite of your busy schedules and this holiday. That is very kind of you.

Happy New Year to you also.

Are there any control breaks?
>>>Yes there will be a control break going to the next file name.

If driven by employee - is it possible to have other employers / start dates ?
>>>Any employee can only have one employer. The unique id is the filename. It will loop to the next filename and will create a text file. It will create a text file based on the filename.

If driven by employer can we have multiple employees and is there any control break logic/ grouping ?
>>>There are cases that have multiple employees.

Can we have multi-pages ?
>>>Yes there are multiple pages.

What is the idea behind dbo.ConstantVariable - seems arbitrary ?
>>>This is only constant variable to show in a text file if only one employee, it can be multiple pages if possible if there are more employees in an employer.

Thank you again for you help. I really appreciate this.

Mark Wills

OK, we will do this in a couple of parts. Still a little unsure of a couple of things - like the datatypes/sizes of dbo.Employee

First part is to create a user defined function to do the formatting. We have 1 parameter @filename which we use to pass the Filename we want formatted.

Good thing about user defined functions is we can select from them.

Second part (which I will post after the user defined function has been verified) will be a stored procedure to actually export

So, the first part.... In SSMS paste the following code

-- to change after wards, you can ALTER FUNCTION.... rather than CREATE although, I prefer to DROP and then CREATE
--DROP FUNCTION [dbo].[udf_export_employee];

CREATE FUNCTION [dbo].[udf_export_employee](@filename varchar(50))
RETURNS @textfile table 
	(
	linectr int identity, 
	rundate datetime, 
	[Filename] varchar(50),
	exportrecord varchar (500)
	)
AS
BEGIN
	
	DECLARE @rundate datetime = getdate()
	DECLARE @pagecounter int = 0
	DECLARE @Employee_UniqueID varchar(50)
	DECLARE @Employee_Filename varchar(50)
	DECLARE filename_cursor CURSOR FOR SELECT UniqueID,[Filename] from Employee where [Filename] = @filename

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @pagecounter = @pagecounter + 1

		IF @pagecounter = 1
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ '+ConstantVariable+' ------------------------' 
                              FROM ConstantVariable where UniqueID = @pagecounter
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END
		ELSE
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ Page '+convert(varchar(3),@pagecounter)+' ------------------------' 
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END


		INSERT @textfile SELECT @rundate,@filename,EmployeeName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
    	INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,EmploymentDate from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
    	INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,EmployerName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
    	INSERT @textfile SELECT @rundate,@filename,'                                                       '


		FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename

	END

	CLOSE filename_cursor
	DEALLOCATE filename_cursor

	IF @pagecounter = 0
    	INSERT @textfile SELECT @rundate,@filename,'No Employee Details Exist For '+@filename
	   

	RETURN
END
GO

Open in new window

Now to test the required format, use the UDF with the Filename as the parameter

select * from udf_export_employee ('File1')

select * from udf_export_employee ('File2')

select * from udf_export_employee ('File3')

select * from udf_export_employee ('NoSuchFile')

Open in new window

If you could run and test the above, nd get back to me, we will fix/finesse and then move onto the Stored Procedure

The Stored Procedure will use a utility called BCP to export. It is a command line utility that comes with SQL Server.
It looks like : 'bcp "select exportrecord from YourDB.dbo.udf_export_employee ('File3')" queryout "' + @ExportName + '" -T -c -CACP'
To execute, we will need to enable xp_cmdshell but more on that later.

Quenn L

ASKER

@Mark Willis:

I will try this and get back with you.

Thank you for all your help.

Quenn L

ASKER

@MarkWillis:

Function is really working. WOW.

I never knew you can do it by T-SQL.

I don't know how to run this table in Stored Procedure.

Thank you again.

Mark Wills

OK, so now the Stored Procedure....

But first we have to turn on (allow) a system procedure. We need to first "see" the advanced options of SQL Server, then enable XP_CMDSHELL. This can be considered as a once-off process

--step 1 turn on xp_cmdshell

-- First, we need to allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- Then update the currently configured value for advanced options.
RECONFIGURE
GO
-- Now we can enable the XP_CMDSHELL feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Open in new window

So, once that has been done, we can now focus on the Stored Procedure. You will need to change the PATH (see comments in code) and change YOURDB to your database name (again see Comments).

Now I have used some functions which needs SQL2012 or more recent.
If you dont have 2012, or more recent, you need to let me know so I can adjust. The two newer functions are the FORMAT() and IIF().

-- ok, now the Stored Procedure

-- I always prefer to drop and re-create functions or stored procedures (+other objects)
-- so let's check if our object exists first

IF OBJECT_ID('dbo.usp_Output_Employee') IS NOT NULL
    DROP PROCEDURE dbo.usp_Output_Employee;
GO

-- now we can create our stored procedure
-- it has an optional parameter for specifying a filename

CREATE PROCEDURE dbo.usp_Output_Employee (@filename varchar(50) = NULL)    -- @filname parameter is optional
AS
DECLARE @Path       varchar(50) = 'C:\MRWTEST\'          -- you need to change this
DECLARE @rundate    varchar(30) = FORMAT(getdate(),'MMddyyyy')
DECLARE @sqlcommand varchar(500)
DECLARE @QryoutName varchar(500)
DECLARE @bcpCommand varchar(500)

IF OBJECT_ID('dbo.udf_Export_Employee') IS NOT NULL   -- let's double check the UDF function exists before we start tying to use it
BEGIN

	DECLARE @Employee_Filename varchar(50)
	DECLARE export_cursor CURSOR FOR SELECT distinct[Filename] from Employee where [Filename] = IIF(isnull(@filename,'') > '',@filename,[Filename])

	OPEN export_cursor 
	FETCH NEXT FROM export_cursor INTO @Employee_Filename
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @QryoutName = @Path+@Employee_Filename+'_'+@rundate+'.txt'
		SET @sqlcommand = 'select exportrecord from YOURDB.dbo.udf_export_employee ('+''''+@Employee_Filename+'''' +')'      --  change YOURDB to your database name
		SET @bcpCommand = 'bcp "'+@sqlcommand+'" queryout "' + @QryoutName + '" -T -c -CACP'
		--print @bcpcommand
		EXEC master..xp_cmdshell @bcpCommand

		FETCH NEXT FROM export_cursor INTO @Employee_Filename

	END

	CLOSE export_cursor
	DEALLOCATE export_cursor

END
GO

Open in new window

To test, it is pretty straight forward, just execute the Stored Procedure

exec dbo.usp_Output_Employee 
-- or for a specific filename
exec dbo.usp_Output_Employee @filename='File3'
-- then go check your @path to see the files

Open in new window

BCP stands for Bulk Copy Program and is a command line utility that comes with SQL Server. Thats why we need XP_CMDSHELL so we can run it (launch it) from with SQL Server. You could also run it from the command prompt. See : https://docs.microsoft.com/en-us/sql/tools/bcp-utility

Now the BCP command is being built up using Dynamic SQL and uses a TRUSTED Connection via parameter -T you can change it if needed to use a username and password. The parameters are in the BCP link.

Quenn L

ASKER

@Mark Willis:

Thank you again for this. It is really a WOW. I did not know you can do this. I learned way something.

I followed the steps but when I execute the Stored Procedure, it is showing an error:

output
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
NULL

I am using Microsoft Server 2012 Express Edition and running this in my local machine.

Thank you for your help.

Mark Wills

Could be a couple of things...

Most likely with SQL Express, is that SQL Express installs as an "instance" on your computer.

Sounds like we need to add the parameter-S server_name [\instance_name] for BCP. Using SQLExpress this will be something like :
-S YourComputerName\SQLExpress or -S .\SQLExpress
normally put in front of the -T

Edit your Stored procedure, and change the @BCPCOMMAND line to include the -S , and while editting, also, comment out the XP_CMDSHELL and remove the '--' on the print @bcp IE

		SET @bcpCommand = 'bcp "'+@sqlcommand+'" queryout "' + @QryoutName + '" -S .\SQLExpress -T -c -CACP'     -- might need to replace the -S .\SQLExpress with -S YourComputerName\SQLExpress 
		print @bcpcommand
		--EXEC master..xp_cmdshell @bcpCommand

Open in new window

then recreate the procedure (or use ALTER instead of CREATE)
Then execute the procedure. You should get a few of the BCP lines being printed.

Select and Copy one of those lines, open a command prompt (or a DOS prompt in old terms) and paste the line, press enter.

If you get errors you can modify the line in the command prompt to try the other -S setting.

Dont close anything, we will continue. Tip, in SSMS at the top of the tree on the left hand side. Your Computer Name (as far as SQL is concerned) will be visible. We might need to go into Properties by right clicking on your Computer name to check some properties.

First, try using the -S parameter for BCP

Quenn L

ASKER

@Mark Willis:

It really works in my local machine but when I tried it to the actual server with path is \\Server\Employee Files\EmployeeTextFolder\DailyEmployee\.

DECLARE @Path varchar(100) = '"\\Server\Employee Files\EmployeeTextFolder\DailyEmployee\"' with 1 quote and double quotes it is because "Employee Files" folder has space in it. I cannot change it because this folder existed way before I was here. Do you think this is the issues? I don't really know.

I have another issue error showing:

output
Unknown argument 'Systems\EmployeeTextFolder\DailyEmployee\ "File1463-287253074_01032018.txt -T -c -CACP' on command line.
NULL

I really appreciate your time and help.

Thank you again.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Quenn L

ASKER

Thank you again.

Mark Wills

A pleasure, happy to have been able to help and very happy you have found a solution.

Cheers,
Mark Wills