May 9, 2006 at 9:22 pm
Yup, I'm a newbie.
We have multiple tables created in a database it is a student database for school.
the student table is set up as follows
CREATE TABLE tblStudents (
stuStudentID VARCHAR(9) PRIMARY KEY,
stuFirstName VARCHAR(15) NOT NULL,
stuLastName VARCHAR(20) NOT NULL,
stuMiddleInitial VARCHAR(1),
stuMajor VARCHAR(10) NOT NULL,
stuElectives INTEGER,
stuPreference1 VARCHAR(10),
stuPreference2 VARCHAR(10),
stuPreference3 VARCHAR(10),
stuStartDate SMALLDATETIME,
stuAddress VARCHAR(20),
stuCity VARCHAR(20),
stuState VARCHAR(2),
stuZip VARCHAR(5),
stuHomePhone VARCHAR(12),
stuAltPhone VARCHAR(12),
stuEmail VARCHAR(30)
)
GO
many different text files that have been exported from excel and access all with different names, but all the same structure, some with all the fields full others like below
"923232605","JOHN","BARNWELL",,"CISPB",32,,,,1/3/2002 0:00:00,"RR 4 BOX 225-B","MELBOURNE","FL","32901","2055824346","3082572259","JNWELL@hotmail.com"
"923232606","VICKI","WALKER",,"CISPB",32,,,,1/3/2002 0:00:00,"RR 8 BOX 175","MELBOURNE","FL","32901","2052864226","3082227830","VALKER@hotmail.com"
"923232607","JULIE","EBLIN",,"CISPB",32,,,,1/3/2002 0:00:00,"RT 1 BOX 296","MELBOURNE","FL","32901","2049904106","3081883401","JEBLIN@hotmail.com"
"923232608","ROBIN","MYERS",,"CISPB",32,,,,1/3/2002 0:00:00,"""RT 4, BOX 136A""","PALM BAY","FL","32950","2046943986","3081538972","RMYERS@hotmail.com"
"923232609","SR","SLATER",,"CISPB",32,,,,1/3/2002 0:00:00,"RTE 1 BOX 50","MELBOURNE","FL","32955","2043983865","3081194543","SLATER@hotmail.com"
"923232610","SHAN-MEI","TSENG",,"CISPB",32,,,,1/3/2002 0:00:00,"URH 949 SHERMAN HALL","PALM BAY","FL","32907","2041023745","3080850114","STSENG@hotmail.com"
right now we have been using the DTS Import/Export wizard but can i make a proceedure that all i have to do is pass the filename to?
Other tables are simular to the student table so if I see 1 proceedure i should be able to figure the rest.
I would really like to include this proceedure in a code that i can call from VB Studio.NET to if at all possible.
May 9, 2006 at 9:38 pm
You might want to look up 'BULK INSERT' in Books On Line (BOL) or on the web.
May 11, 2006 at 6:08 am
As a counterpart to the BULK INSERT idea, I would think you might also be able to do your stored procedure idea and call "BCP.. IN" from xp_cmdshell within the stored procedure.
May 11, 2006 at 6:40 am
Or you could create a DTS package having an activeX script component to find and assign the text filename dynamically using filesystemobjects.
May 11, 2006 at 8:10 am
You can also save the DTS as a Visual Basic file and include it in a VB program.
May 11, 2006 at 9:42 am
If you are already using VB.net, you could read and format the file in your VB program, then do all the inserts from there. BCP with a format file would work well, too.
There are many ways to address the problem. If you want a pure T-SQL solution, you could do something like the following:
1. Create the UDF dbo.fFormatImportedData
2. Create the stored procedure LoadStudentData (calls dbo.fFormatImportedData() and processes the results in a cursor)
3. Run this code:
--TRUNCATE TABLE tblStudents
GO
-- the default table is tblStudents.
--EXEC LoadStudentData 'e:\data\data1.txt', 'tblStudents'
EXEC LoadStudentData 'e:\data\data1.txt'
SELECT * FROM tblStudents
This method is a lot slower than others because it uses several of the "taboo" methods, including cursors and dynamic SQL. Also, it only handles inserts, not updates. Note that the filename path is relative to the SQL Server, not your workstation. Using the example, the server must have a folder named e:\data
DROP FUNCTION dbo.fFormatImportedData
GO
CREATE FUNCTION dbo.fFormatImportedData
(
@S varchar(1000)
)
RETURNS varchar(1000)
AS
BEGIN
-- Fix null columns by changing ,, to ,'', which will only work for character and datetime data
SET @S = Replace(@s, '"', '''')
WHILE CharIndex(',,', @S) > 0
SET @S = Replace(@s, ',,' , ','''',')
-- Fix datetime value by adding quotes
-- Find this character combination: <,><digit> to locate beginning of datetime value
-- Extract token (the datetime), up to the next comma.
-- Make sure the token contains a colon as a doublecheck
-- Add quotes around the datetime in the source string
DECLARE @cpos1 int, @cpos2 int
SET @cpos1 = PatIndex('%,[0-9]%', @S)
WHILE @cpos1 > 0
BEGIN
SET @S = Stuff(@s, @cpos1 + 1, 0, '''')
SET @cpos2 = CharIndex(',', @S, @cpos1 + 1)
IF @cpos2 > 0
SET @S = Stuff(@s, @cpos2, 0, '''')
-- Process next date, if present
SET @cpos1 = PatIndex('%,[0-9]%', @S)
END --WHILE
RETURN @S
END --FUNCTION
DROP PROCEDURE LoadStudentData
GO
CREATE PROCEDURE LoadStudentData
(
@filename varchar(300)
, @table varchar(50)= 'tblStudents'
)
AS
DECLARE @bulkInsertCmd varchar(2000)
, @insertStmt varchar(2000)
-------------------------------------------------------------------------
-- Load the data into a staging table as a single character string
-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'TempStudentImportTable') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE TempStudentImportTable
CREATE TABLE TempStudentImportTable
(
line varchar(2000)
)
SET NOCOUNT ON
IF @filename LIKE '%[=;]%' OR @filename LIKE '%--%'
BEGIN
-- the filename contains invalid characters:'
SET @filename = NULL
RAISERROR ( 'Bad filename passed to uspAERS_LoadDataFile: %s', 10, 1, @filename)
END
SET @bulkInsertCmd = 'BULK INSERT TempStudentImportTable FROM ''' + @filename + ''' WITH ('
+ 'CODEPAGE = ''RAW'' , DATAFILETYPE = ''char'' ,'
+ ' FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'' , FIRSTROW = 1 )'
EXEC (@bulkInsertCmd)
-------------------------------------------------------------------------
-- Convert the data into INSERT statements, then execute one-by-one
-------------------------------------------------------------------------
DECLARE src CURSOR FOR
SELECT 'INSERT tblStudents VALUES ( ' + dbo.fFormatImportedData(t.line) + ' )'
FROM TempStudentImportTable t
OPEN src
FETCH NEXT FROM src INTO @insertStmt
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@insertStmt)
FETCH NEXT FROM src INTO @insertStmt
END -- WHILE
CLOSE src
DEALLOCATE src
DROP TABLE TempStudentImportTable
GO
May 11, 2006 at 10:45 am
You could also call a BCP command using the exec master..xp_cmdshell from within a procedure and pass the file names to it.....something along these lines....
e.g.
declare @sql varchar(512), @filename varchar(50)
-- YOUR FILENAME
set @filename = 'C:\Your_Folder\tblStudents.txt'
-- CREATE A BCP COMMAND STRING
set @sql = 'bcp pubs..tblStudents in ' + @filename + ' -t, -c'
-- EXECUTE IT USING CMDSHELL
exec master..xp_cmdshell @sql
May 11, 2006 at 12:51 pm
The BULK INSERT did the trick for what I first intended, then as indicated above, have VB.NET do the rest if the imports from now on... Thanx alot guys.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply