Reading in a file via VBA
-
I have a 3rd party application (Alterian) that is creating a file and I need to read it back into SQL Server so that the records that were selected can not be selected again. So, my non-technical user has an Access front end to track her file selections and I would like to add a button that will populate the SQL Server with the file that was just created. The file will be a delimited text file. I only need the first field. I think I can use TransferText Method. Is this the way to get the one field back in? How do I define the file so that it reads only the first field?
Thoughts, suggests?
Thanks,
Steve
-
If you go with the TransferText option, there is no way to stop it from reading all fields and all records. So you could put it into a temporary table and just read the first field from that. If the files are large and you are concerned with performance and you really just want the first field of the first record, you could use something like:
Open FileLoc For Input As #FileNum1
Line Input #FileNum1, InputString
to read the first line of the file, and then separate out the first field.
Dick
-
Try using a dts package. Create your source as the text file, your destination as your table and map just the field(s) you need. Get creative and make the whole process dynamic if you need to. You'll be able to do some amazing things using the power of DTS.
Regards,
Matt -
Thanks Matt... I had thought of DTS, but I would have to pass the Path and Filename from the Access Application to the DTS Package. Is there a method to do that, I don't know of one.
-
It's been a while, but i believe that you would simply call a stored proc that calls the DTS Package. You can add two variables, you should be able to get away wtih one that is the full path and filename, to the proc which would be called from access.
Something like exec usp_Update_MyTable "//UNCfilepath/filename.txt"
That variable then gets mapped/used in the DTS to reset/update the path name of the File which you've set as the import source.
Should be something like that. Like I said though it's been a while since I've had to do it. This is from memory as opposed to being able to look at one I've done and give you a step by step.
Let me know if this helps or if you need some additional help. I should be able to did up some documentation I did for a previous client where I did this, but from an asp page instead of access.
Regards,
Matt -
Found the code for the Proc and I have a sripted DTS too if you need it. Proc is below. Hope this helps.
ALTER proc dbo.usp_execute_dts @strTxtFile as varchar(200),
@strMonth as varchar(2),
@strYear as varchar(4),
@strExcelFile as varchar(200),
@strEmail as varchar(100)
as
/***********************************************************************
* Database:
* Procedure Name: usp_execute_dts
* Date: 03/25/2002
* Author: Matthew Simmons
* Procedure Desc: Executes a DTS passing Month and Year parameters
* as well as the text and excel file paths that are
* to be imported.
*
* Parameters: @strTxtFile - the path and file name
* @strMonth - the 2 digit month
* @strYear - the 4 digit year
* @strExcelFile - the path and file name
* @strEmail - email address of the person to be notified of successful run.
*
* Returns: Nothing
*
* Calling
* Mechanism: ASP - Encounter Penalty Processing - Complete.asp
*
************************************************************************
* Version: 2.0 Author: Matthew Simmons
* Date: 10/14/2003 DPSR #:
*************************************************************************
* Description of Requests:
* 1.
* Description of Modifications:
* 1. Removed record counts. Added Transaction and Commit.
* Modified the name of the DTS beign called. Added the
* replace statements.
* Special Comments:
*
* Other modules changed with this request:
*
***********************************************************************/
Set @strTxtFile = Replace(@strTxtFile, '''', '''''')
Set @strMonth = Replace(@strMonth, '''','''''')
Set @strYear = Replace(@strYear, '''','''''')
Set @strExcelFile = Replace(@strExcelFile, '''', '''''')
Set @strEmail = Replace(@strEmail, '''', '''''')
DECLARE @myline as varchar(7000)
--Execute DTS Package
--Begin Transaction Import_Penalty
Begin
SET @myline = 'dtsrun /Spwishbgsql01 /Uxxxxxxxxxx /Pxxxxxxxxxx
/N"Encounter Penalty Calculation Process -Email" /M"XXXXXXXXXX"
/AgvImportTextFileName:8=' + @strTxtFile + ' /AVarMonth:8=' + @strMonth + '
/AVarYear:8=' + @strYear + ' /AgvImportExcelFileName:8=' + @strExcelFile + '
/AglvEmail:8=' + @strEmail + ''
EXEC master..xp_cmdshell @myline
Execute usp_delete_t_penalty_import_files @txtMonth = @strMonth, @txtYear = @strYear
End
Regards,
Matt -
Another alternative to consider is that you could also use openrowset to get just the one column...
..if your comma delimited file looks like this
col1,col2,col3,col4
1,2,3,4
5,6,7,8
9,10,11,12
you would select this..
select col1 FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Temp', 'SELECT * FROM test.txt')
Note: the DefaultDir is where the server would be looking (at it's C
. You could use a unc or mapped drive name if it exists on the db server.Scott
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply