Uploading Data from Text file with different column name

Question

Post reply

Uploading Data from Text file with different column name

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65111 More actions · Answer 1

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.

DKSINGH SSC Eights! Points: 841 More actions · Answer 2

DKSINGH

SSC Eights!

Points: 841

February 4, 2005 at 9:17 am

#540712

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

DKSINGH SSC Eights! Points: 841 More actions · Answer 3

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

DKSINGH SSC Eights! Points: 841 More actions · Answer 4

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

DKSINGH SSC Eights! Points: 841 More actions · Answer 5

DKSINGH

SSC Eights!

Points: 841

February 6, 2005 at 10:29 pm

#540854

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

David Burrows SSC Guru Points: 65111 More actions · Answer 6

David Burrows

SSC Guru

Points: 65111

February 7, 2005 at 2:20 am

#540863

, 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.

DKSINGH SSC Eights! Points: 841 More actions · Answer 7

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

David Burrows SSC Guru Points: 65111 More actions · Answer 8

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.

DKSINGH SSC Eights! Points: 841 More actions · Answer 9

DKSINGH

SSC Eights!

Points: 841

February 7, 2005 at 5:10 am

#540894

Thanks David... Thanks for helping me...

Its done now.