December 14, 2005 at 9:22 pm
I found several versions of how to check if a column exists in the database, but what about if it's being imported from a dbf file? I want to check that a column exists before trying to select it from the dbf file to be imported into the database. If if does not exist, with the exception of a few columns, I want the system to not have an error, and just continue importing other columns that are found. The dbf files will always have some matching columns, but some will not always be there, which ones vary. So I have a list of columns to check for, but I don't want the import to abort just because a non-critical column is missing.
Suggestions?
Here's the context of the code I need to work within:
/* procedure to import a dbf file into a temporary sql server table Project01tmp,
and has to check for columns to see if they exist, and then import them,
otherwise ignore columns that are missing or any extra columns
that are not specified */
CREATE PROCEDURE usp_ImportProj01dbf
AS
SELECT *
INTO Project01tmp
FROM OpenRowSet('MSDASQL',
'Driver=(Microsoft dBASE Driver (*.dbf);
DriverID=277;
DBQ=C:\ProjectFiles',
'SELECT * FROM Proj01');
INSERT INTO Project01tmp
(FirstName, LastName, HomePHone, ....)
SELECT FirstName, LastName, HomePhone, .....
FROM Project01tmp
GO
So, how do I make it do something like:
INSERT INTO Project01tmp
(if exist(FirstName), if exist(LastName), if exist(HomePhone), ....)
etc. ???
Thanks.
December 15, 2005 at 7:22 am
Here's one way that I could think of - this is not tested and you'll have to work on it, but here're the steps:
1) Select into your "Project01tmp" table the relevant dbf table.
2) Get the column names of this temp table and store in a string variable.
3) Use dynamic sql to import only these columns into your sql table.
The basic syntax for doing this would be...
DECLARE @strColNames VarChar(500)
SELECT *
INTO tblSQLTemp
FROM OpenRowSet('MSDASQL', 'Driver=(Microsoft dBASE Driver (*.dbf); DriverID=277;
DBQ=C:\ProjectFiles', 'SELECT * FROM Proj01')
SELECT @strColNames = isnull(@strColNames +', ', '') + name FROM syscolumns where id = object_id('tblSQLTemp')
EXEC('INSERT INTO tblSQLPerm (' + @strColNames + ')
SELECT ' + @strColNames + ' FROM tblSQLTemp')
DROP TABLE tblSQLTemp
You'll have to take into account all the primary key constraints, relationships with other tables etc..
**ASCII stupid question, get a stupid ANSI !!!**
December 15, 2005 at 12:46 pm
I'll see if I can do that. Meanwhile, I have no trouble importing the file with all it's columns to a temporary table. But how do I do an insert where exists from the temp to perm table, since some columns will be missing sometimes?
Basically just modify this code for the two tables handling?
SELECT @strColNames = isnull(@strColNames +', ', '') + name FROM syscolumns where id = object_id('tblSQLTemp')
EXEC('INSERT INTO tblSQLPerm (' + @strColNames + ')
SELECT ' + @strColNames + ' FROM tblSQLTemp')
I'll fiddle around with it.
Thanks kindly.
December 15, 2005 at 1:40 pm
Yes - when you do the "SELECT @strColNames = isnull(@strColNames +', ', '') + name FROM syscolumns where id = object_id('tblSQLTemp')" - it should give you the names of only the columns that are there in your dbf table...so if you use the variable "@strColNames" both times (since I'm assuming that the sql table will always have everything that the dbf table has - only sometimes more - but NEVER less), you should be able to get it to work...
Please post back if it works since feedbacks are always welcome!
**ASCII stupid question, get a stupid ANSI !!!**
December 15, 2005 at 1:56 pm
The dbf file has different columns than the SQL table.
Let's see if I can clarify.
dbf file has the same columns (for example) of
FIRSTNAME, LASTNAME, HOMEPHONE, CELLPHONE, etc.
Sometimes, some non-required columns will NOT be in the dbf file for example:
CELL, FAX, URL, etc. Won't be in the DBF file, but are valid column in the SQL table, however they are not required (can be null).
But the dbf file also has (unpredictable names) other columns, for example:
NU, A_, __, PROVIDER, etc.
That are NOT in the SQL Table, and do not need to be imported, and need to be ignored.
I know this is horrific, I'm trying to get them to greatly improve their whole process, but they were only willing to improve incrementally (getting off Lotus Approach and Excel spreadsheets, and onto MS SQL Server).
However, the next step, once this kludge is functioning sufficiently for them (for now), is to completely integrate their whole process off the central database, and web enable everything, then all the conversion issues, etc. go away. Alas, they weren't willing to do that out right (life would have been much easier).
So, any additional suggestions on this bit would be greatly appreciated. I'm sooooo close to finishing this stage of the project, it's just the dynamic filenames and dynamic column names that have been such a killer.
Thanks kindly.
December 15, 2005 at 2:01 pm
So, when I run this:
DECLARE @strColNames VarChar(500)
SELECT * INTO Project01PreTemp
FROM OpenRowSet('MSDASQL', 'Driver={Microsoft dBASE Driver (*.dbf)};
DriverID=277;
DBQ=C:\ReturnedProjects',
'SELECT * FROM Proj01')
--DECLARE @strColNames VarChar(500)
SELECT @strColNames = isnull(@strColNames +', ', '')
+ name FROM syscolumns where id = object_id('Project01PreTemp')
EXEC('INSERT INTO Project01tmp (' + @strColNames + ')SELECT '
+ @strColNames + ' FROM Project01PreTemp')
It imports (as it already was) just fine from the dbf to the Project01PreTemp temporary table.
But, the second part, generates different, but similar errors:
(54 row(s) affected)
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'A_'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'NU'.
And so does not insert into the more permanent Project01tmp sql table.
Also, is it possible to wildcard the filename?
FROM OpenRowSet('MSDASQL', 'Driver={Microsoft dBASE Driver (*.dbf)};
DriverID=277;
DBQ=C:\ReturnedProjects',
'SELECT * FROM *.dbf') <--- ? Possible to wildcard somehow??? That's a lesser, secondary issue than the columns issue, can live with it being a fixed filename is necessary, but not preferred.
Thanks very much.
December 15, 2005 at 2:08 pm
So, that's why I was trying to see if an "if exists" or "where exists" or is not null, or some such variation would work.
Basically, check for the expected columns that are in Project01tmp (more permanent table), and IF those columns exist in Project01PreTemp (more temporary column), then import the matching columns. If column in dbf/pretemp doesn't exist (matching what's in project01tmp), don't try to import the missing column(otherwise generates and error, and can't finish the process), and meanwhile completely ignore the columns that are "extras" in the dbf/pretemp and not in the project01tmp/perm table.
I hope that helps clarify, and has a workable solution?
Thanks.
December 15, 2005 at 2:10 pm
So really, you need the intersection of column names between the 2 tables, right ? Only include a column in the Insert/Select statements if the column is in *both* tables ? Then adjust your SQL to generate only that subset of columns:
SELECT @strColNames = isnull(@strColNames +', ', '')
+ c1.name
FROM syscolumns As c1
Inner Join syscolumns as c2
On (c1.Name = c2.Name) -- Join on column names being the same
where c1.id = object_id('Project01PreTemp')
And c2.id = object_id('Project01tmp')
December 15, 2005 at 2:18 pm
To wildcard the filenames, you're going to have to use dynamic SQL to generate the appropriate OpenRowSet statement.
You can use DTS. Or you can use T-SQL.
Here's a T-SQL example:
-- A temp table to hold filenames
Create Table #DBF_Files (filename varchar(255))
-- Populate it, using xp_cmdshell, to run a "Dir /B *.dbf" in your folder
Insert Into #DBF_Files
exec master..xp_cmdshell 'Dir /B C:\YourFolder\*.DBF'
-- Process only the 1st file
Declare @CurrFilename varchar(255)
Select @CurrFilename = Top 1 * from #DBF_Files
If @CurrFilename Is Not Null
Begin
-- Build dynamic SQL here to OpenRowset on the @CurrFilename
End
Obviously you'll make this more robust, potentially opening a cursor on #DBF_Files, processing each available file and deleting/moving it when done ... this code is just for example sake
December 15, 2005 at 2:24 pm
Exactly! 😉
Thanks, I'll try this out.
December 15, 2005 at 2:26 pm
Yes I'll improve as best I can. 😉
But it's great to have a clearer idea of how to handle it.
I'll first make sure the dynamic columns issue works with a static filename, then when that's resolved, add the dynamic filename capability.
Thank you very much for your help.
I'll post back if it works, and any tweaks made (if any needed).
Thanks again!
December 15, 2005 at 2:42 pm
Hmmm. Well. Regarding the dynamic columns:
it runs now without any errors, and imports the dbf file into Project01PreTemp (as it did originally), and no errors, but nothing is actually ending up in the Project01tmp (more permanent) column. So it must not be finding an actual match?
I'll stare at it for a bit. Is it a difference between "=" and "==" ?
Here is what I have put together so far, maybe I munged it somewhere:
/* Need the intersection of column names between the 2 tables,
Only include a column in the Insert/Select statements if the
column is in *both* tables, Then adjust your SQL to generate
only that subset of columns */
DECLARE @strColNames VarChar(500)
SELECT * INTO Project01PreTemp
FROM OpenRowSet('MSDASQL', 'Driver={Microsoft dBASE Driver (*.dbf)};
DriverID=277;
DBQ=C:\ReturnedProjects',
'SELECT * FROM Proj01')
SELECT @strColNames = isnull(@strColNames +', ', '')
+ c1.name
FROM syscolumns As c1
Inner Join syscolumns as c2
On (c1.Name = c2.Name) -- Join on column names being the same
where c1.id = object_id('Project01PreTemp')
And c2.id = object_id('Project01tmp')
Anything jump out?
Thanks.
December 15, 2005 at 2:49 pm
I'm sorry, I'm so tired and bleary eyed (and almost out of time on this stage of the project), I'm probably overlooking something obvious like renaming a variable?
December 15, 2005 at 2:52 pm
You're executing the dynamic SQL like this:
EXEC('INSERT INTO Project01tmp (' + @strColNames + ')SELECT '
+ @strColNames + ' FROM Project01PreTemp')
Try adding a PRINT statement to debug the contents of what's being generated:
Declare @sql varchar(1000)
Select @sql = 'INSERT INTO Project01tmp (' + @strColNames + ')SELECT '
+ @strColNames + ' FROM Project01PreTemp'
-- Debug!!
Print @sql
Exec(@SQL)
December 15, 2005 at 3:02 pm
oh good - PW to the rescue - hope it all works out 'cos I gotta run now...will check again tomorrow to see if this has been resolved!
Good luck!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply