Uploading Data from Text file with different column name
-
OK try this
Create a udf
CREATE FUNCTION dbo.udf_get_string
(@str varchar(1000), @num int, @delim char(1))
RETURNS varchar(100)
AS
BEGIN
DECLARE @start int,@end int
SET @end = 0
WHILE (@num > 0)
BEGIN
SET @start = @end + 1
SET @end = CHARINDEX(@delim,@str+@delim,@start)
SET @num = @num - 1
END
RETURN SUBSTRING(@str,@start,@end-@start)
END
The run this query
SET @Tablename = dbo.udf_get_string(@Tablecol,1,'|')
SET @fieldcount = LEN(@Tablecol) - LEN(REPLACE(@Tablecol,'|',''))
SELECT stmt
FROM (
SELECT 1 AS , 0 AS [pos], 'INSERT INTO ' + @Tablename AS stmt
UNION ALL
SELECT 2, ORDINAL_POSITION,
CHAR(9) + COL_NAME(OBJECT_ID(@Tablename), ORDINAL_POSITION) +
CASE WHEN ORDINAL_POSITION < @fieldcount THEN ',' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Tablename AND ORDINAL_POSITION <= @fieldcount
UNION ALL
SELECT 3, 0, 'Values ('
UNION ALL
SELECT 4, n.number,
CASE WHEN c.DATA_TYPE = 'varchar'
THEN ''''+dbo.udf_get_string(@Tablecol,number+1,'|')+''''
ELSE dbo.udf_get_string(@Tablecol,number+1,'|')
END
+ CASE WHEN n.number < @fieldcount THEN ',' ELSE '' END
FROM master.dbo.spt_values n
INNER JOIN INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_NAME = @Tablename AND c.ORDINAL_POSITION = n.number
WHERE n.type = 'P'
AND n.number > 0
AND n.number <= @fieldcount
UNION ALL
SELECT 5, 0, ')'
) AS st
ORDER BY ,pos
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David
This is amazing.... Let me try this tommorow on SQL DB. This is really Great idea. I will reply again ... if not done...This will help me a lot.
Thanks again
-
Hi David,
The Idea was pretty Good... But still have problem...when i executed the statement made by with some changes... The output i got is like this....
INSERT INTO TableA(
Field1,
Field2,
Field3, ---- Dot after Every statement
) Values (
'ZS08 ',
28.02.2004,
01.02.2004, ---- Dot after Every statement
)
I tried it by using fieldcount -1, But for some tables its ok but for most of it was like the above.
And can u pls. explain why u used this master.dbo.spt_values ?
One morething Boss, How to convert the one in format like "28.02.2004" in "28/02/2004" according to my script.
Thanks
-
Hi David,
The Idea was pretty Good... But still have problem...when i executed the statement made by with some changes... The output i got is like this....
INSERT INTO TableA(
Field1,
Field2,
Field3, ---- , after Every statement
) Values (
'ZS08 ',
28.02.2004,
01.02.2004, ---- , after Every statement
)
How to remove (,) from the string ????
I tried it by using fieldcount -1, But for some tables its ok but for most of it was like the above.
And can u pls. explain why u used this master.dbo.spt_values ?
One morething Boss, How to convert the one in format like "28.02.2004" in "28/02/2004" according to my script.
Thanks
-
Hi,
Finally My query is Ready... But i can't able to excute the Same from My SP.... The Query is almost same as answered by David...
Thanks all for Help
Raju
-
, after Every statement This would indicate that you have more data columns than columns in the table
How to convert the one in format like "28.02.2004" in "28/02/2004" Use REPLACE as in REPLACE([date],'.','/')
Far away is close at hand in the images of elsewhere.
Anon. -
Thanx David,
The is now ready with no error and doing what i want.... But I don't know how to execute it.
the script is ...
alter PROC Sp_EnterTextFile @filename sysname
as
BEGIN
SET nocount ON
declare @Tablecol varchar(2550), @Tablename varchar(200),@stmt varchar(8000), @column varchar(2400), @statement varchar(400),@statement1 varchar(2400), @cind as int , @fieldcount as int, @tmpfieldcount as int
CREATE TABLE #tempf (line varchar(8000))
EXEC ('bulk INSERT #tempf FROM "' + @filename + '"')
DECLARE pcursor CURSOR FOR
SELECT * FROM #tempf
OPEN pcursor
FETCH NEXT FROM pcursor into @Tablecol
WHILE @@FETCH_STATUS = 0
BEGIN
select @cind=CHARINDEX('|',rtrim(ltrim(@Tablecol))) --- finding 1st column Before Pipe
select @Tablename= dbo.udf_get_string(@Tablecol,1,'|') --- Getting Table name
select @column=SUBSTRING(rtrim(ltrim(@Tablecol)),@cind+1,len(@Tablecol)) --- getting column names
--select count(*) from dbo.Split (@column,'|') as int ---- Taking Column Count
--SELECT stmt
--FROM (
SELECT 'INSERT INTO ' + @Tablename + '(' AS stmt
UNION ALL
SELECT
CHAR(9) + COL_NAME(OBJECT_ID(@Tablename), ORDINAL_POSITION) +
CASE WHEN ORDINAL_POSITION < (select count(*) from dbo.Split (@column,'|') as int) THEN ',' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Tablename AND ORDINAL_POSITION <= (select count(*) from dbo.Split (@column,'|') as int)
UNION ALL
SELECT ') Values ('
UNION ALL
SELECT
CASE c.DATA_TYPE
WHEN ('char') THEN ''''+dbo.udf_get_string(@Tablecol,number+1,'|')+''''
WHEN ('varchar') THEN ''''+dbo.udf_get_string(@Tablecol,number+1,'|')+''''
WHEN ('datetime') THEN REPLACE(rtrim(ltrim(dbo.udf_get_string(@Tablecol,number+1,'|'))) , '.' , '/' )
----dbo.udf_get_string(@Tablecol,number+1,'|')
ELSE
dbo.udf_get_string(@Tablecol,number+1,'|')
end
+ CASE WHEN n.number < (select count(*) from dbo.Split (@column,'|') as int) THEN ',' ELSE '' END
FROM master.dbo.spt_values n
INNER JOIN INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_NAME = @Tablename AND c.ORDINAL_POSITION = n.number
WHERE n.type = 'P'
AND n.number > 0
AND n.number <= (select count(*) from dbo.Split (@column,'|') as int)
UNION ALL
SELECT ')'
FETCH NEXT FROM pcursor into @Tablecol
END
CLOSE pcursor
DEALLOCATE pcursor
DROP TABLE #tempf
END
-
Replace the code with this
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO ' + @Tablename + '('
SELECT @sql = @sql +
COL_NAME(OBJECT_ID(@Tablename), ORDINAL_POSITION) +
CASE WHEN ORDINAL_POSITION < (select count(*) from dbo.Split (@column,'|') as int) THEN ',' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Tablename AND ORDINAL_POSITION <= (select count(*) from dbo.Split (@column,'|') as int)
SET @sql = @sql + ') Values ('
SELECT @sql = @sql +
CASE c.DATA_TYPE
WHEN ('char') THEN ''''+dbo.udf_get_string(@Tablecol,number+1,'|')+''''
WHEN ('varchar') THEN ''''+dbo.udf_get_string(@Tablecol,number+1,'|')+''''
WHEN ('datetime') THEN REPLACE(rtrim(ltrim(dbo.udf_get_string(@Tablecol,number+1,'|'))) , '.' , '/' )
ELSE
dbo.udf_get_string(@Tablecol,number+1,'|')
end
+ CASE WHEN n.number < (select count(*) from dbo.Split (@column,'|') as int) THEN ',' ELSE '' END
FROM master.dbo.spt_values n
INNER JOIN INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_NAME = @Tablename AND c.ORDINAL_POSITION = n.number
WHERE n.type = 'P'
AND n.number > 0
AND n.number <= (select count(*) from dbo.Split (@column,'|') as int)
SELECT @sql = @sql + ')'
EXEC sp_executesql @sql
This will only work if the query (columns and data) length is less than 4000 chars.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David... Thanks for helping me...
Its done now.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply