Post reply

Uploading Data from Text file with different column name

  • February 3, 2005 at 4:09 am

    #74856

    Hi all,

    Help Required.. I want to upload Data from text file.. The first column will be Table name followed by Column name... Here no of columns may be different accross the table.

    I started doing using BCP... Imported the file into temp file... all columns in single Column.... I am spliting the file using function.... Then i have to import the file into table name but i know how to use that i have my procedure like below.....

    CREATE PROC Sp_EnterTextFile @filename sysname   

    as    

    BEGIN    

       SET nocount ON    

       declare @Tablecol varchar(2550), @Tablename varchar(8000), @othercolumn varchar(2400), @statement varchar(400),@statement1 varchar(2400), @rowlen varchar(200) 

       

       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 @statement='select top 1*  from split (''' + @Tablecol + ''','+ '''|'''+')' 

       --- Statement for getting table Name   

       select @statement1='select * from ' + @statement  

     

       FETCH NEXT FROM pcursor into @Tablecol   

      END   

       

       CLOSE pcursor   

       DEALLOCATE pcursor   

        

       DROP TABLE #tempf    

    END    

     

    This is the script i am using now. The statement in  red will give return me Table name and if i will use 'select *  from split (''' + @Tablecol + ''','+ '''|'''+')'  . i will get all the column, but i don't know how to use. how to use these statement in trigger. Pls. help me out.

    Thanks in advance.

    Raju

  • February 3, 2005 at 6:28 am

    #540346

    Can you provide and example of the file layout please. I am kinda confused on what you are actually doing.

  • February 3, 2005 at 7:15 am

    #540365

    Deja Vu

    Is this is related to the other post?

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=159281

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 3, 2005 at 8:52 pm

    #540577

    Hi

    Pls. find the file. Where A222, a223, A940,.... is the name of the File followed by field name....

    A222|ZS08          |0005      |H350        |GENERAL_REPAIR      |A              |28.02.2004|01.02.2004|0000352836     |000

    A223|ZS16          |0005      |02            |03      |03         |20W50FOL080KLG      |31.12.2001|01.01.2001|0000044759    

    A222|ZS01          |0002      |02      |VG602-11311         |31.12.9999|01.01.2002|0000054528     |

    A940|ZS01          |0002      |03      |01783-03760         |31.12.9999|20.09.2001|0000029494     |

    A940|ZS01          |0002      |03      |04111-06012         |31.12.9999|20.09.2001|0000029495     |

    A940|ZS01          |0002      |03      |04111-16124         |31.12.9999|20.09.2001|0000029496     |

    Raju....

  • February 3, 2005 at 8:56 pm

    #540580

    Yes this post is related to the Other one..

    Thanks

  • February 3, 2005 at 9:17 pm

    #540585

    And would it also be related to this post?

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=159576

    Making it three posts for the same problem.

    Is that some sort of record?

     

    --------------------
    Colt 45 - the original point and click interface

  • February 3, 2005 at 9:22 pm

    #540586

    Ok, so this is a single data record,

    A222|ZS08          |0005      |H350        |GENERAL_REPAIR      |A              |28.02.2004|01.02.2004|0000352836     |000

    Is A222 the destination table  and ZS08 the field? (For sanity sake I hope not :crazy

     

    --------------------
    Colt 45 - the original point and click interface

  • February 3, 2005 at 10:02 pm

    #540588

    yes u r true A222 is table name and zs08|0005|H350..... are fields vale (That Data not filed name)...

    A222 is destination table in db thats rite.. and i have to upload the data

     

    Thanks

    raju

  • February 3, 2005 at 11:05 pm

    #540593

    OK, so can you map out the fields

    A222|ZS08          |0005      |H350        |GENERAL_REPAIR      |A              |28.02.2004|01.02.2004|0000352836     |000
    EG
    Table: A222
Field: ZS08 Value:0005
etc...
    BTW, who thought up the names and file format?
     
     

    --------------------
    Colt 45 - the original point and click interface

  • February 4, 2005 at 12:14 am

    #540607

    Sorry Phill,

    Its like

    Table : A222

    Field 1        field 2      field 2   field 2         and so on

    ZS01          |0002      |02      |VG602-11311         |31.12.9999|01.01.2002|0000054528     |

    ZS01          |0002      |03      |01783-03760         |31.12.9999|20.09.2001|0000029494     |

    This is the value we have to upload.

    Thanks

  • February 4, 2005 at 2:13 am

    #540623

    OK I will elaborate on my answer in the other post here.

    Create a DTS package to

    1. Create a fixed named table (eg IMPORTTEXT)

    2. Import the text file to IMPORTTEXT

    3. Extract the first column from the first row of the table

        DECLARE @TableName varchar(20)

        SELECT TOP 1 @TableName = col1 FROM IMPORTTEXT

    4. Drop the first column if not required

        ALTER TABLE IMPORTTEXT DROP COLUMN col1

    5. Rename the table

        exec sp_rename 'IMPORTTEXT', @TableName

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 4, 2005 at 2:21 am

    #540626

    ooops! Sorry misread the data

    If the data contains many tables than put code in a loop processing each tablename using a cursor or a loop in the data, eg

    1. Create a fixed named table (eg IMPORTTEXT)

    2. Import the text file to IMPORTTEXT

    3. Loop until no records left

        3.1. Create a fixed named table (eg IMPORTTEXT1)

        3.2. Extract the first column from the first row of the table

              DECLARE @TableName varchar(20)

              SELECT TOP 1 @TableName = col1 FROM IMPORTTEXT

        3.3. Insert data from IMPORTTEXT into IMPORTTEXT1 where matching

              tablename

        3.4. Delete data from IMPORTTEXT where matching tablename

        3.5. Rename the table

              exec sp_rename 'IMPORTTEXT1', @TableName

    4. DROP TABLE IMPORTTEXT

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 4, 2005 at 2:41 am

    #540631

    Thanks david, Thanks a ton for help...

    But i Have already done with this but by other way.. I am still in problem... while putting the data into table in DB... I am Data Type Mismatch error... How to handle that Boss.... U ahve any Idea...

    I will Tell you what i did.

    1 BCP to upload Data into temp table... all in one column.

    2. Used cursor to fetch record one by one.

    3. Extract 1st column as table name and fields. using charindex...Left...Replace and all

    4. I have written SQL script to Insert data into table Dynamically but... Finding problem in Inserting Data in table... Data type mismatch...

    One More question any Idea  : How to Convert Char Data type into Datetime. ?????????????

    Thanks for help

    Raju

  • February 4, 2005 at 4:02 am

    #540643

    OK, data type mismatch is as it states, there must be a problem with the data or column matching.

    To convert char date to datetime, use CAST(chardate as datetime). However if the dates are as you posted (28.02.2004) then you will get an error, so you will have to rearrange the data first, eg

    DECLARE @chardate varchar(10)

    SET @chardate = '28.02.2004'

    SELECT CAST(SUBSTRING(@chardate,7,4)+SUBSTRING(@chardate,4,2)+SUBSTRING(@chardate,1,2) as datetime)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 4, 2005 at 4:30 am

    #540645

    David,

    Thanks i got your point. But I want to show you my query beacuse nowhere i am taking column, as indivisual. I am taking whole string.

    See my Query : You will get some idea :-

    SELECT stmt

      FROM (

       SELECT -1 AS pos, 'INSERT INTO ' + @Tablename AS stmt

       UNION ALL

       SELECT ORDINAL_POSITION,

        CHAR(9) + COL_NAME(OBJECT_ID(@Tablename), ORDINAL_POSITION) +

        CASE WHEN ORDINAL_POSITION < (@fieldcount-1) THEN ',' ELSE '' END

       FROM INFORMATION_SCHEMA.COLUMNS

       WHERE TABLE_NAME = @Tablename AND ORDINAL_POSITION < @fieldcount

       UNION ALL

       SELECT @fieldcount , 'Values ('+ @column + ')'

     &nbsp AS st

     ORDER BY pos

    Here @ table name refers MY Table Name

    @ filedcount is total no of field

    @ column is all column like

    and this is how i got field names :

    select @column=SUBSTRING(rtrim(ltrim(@Tablecol)),@cind+1,len(@Tablecol)) --- getting column names

       select @column=rtrim(ltrim(replace(@column,'|',''',''')))--- Replace pipe with ',

       select @fieldcount = LEN(@column) - LEN( REPLACE(@column , ',' , '' )) --- Find Total field

       select @column=SUBSTRING(@column,1,len(@column)-2) --- subtract 2 char from right

       select @column='''' + @column ---- COLUMNS READY FOR UPLOAD

    Pls. suggests me if you have any idea... other wise only one option... i have to Break each field... find schema from information... and then chage datatype and then i can insert... Pls. suggests. is this the right way..

    Thanks

    Raju

Viewing 15 posts - 1 through 15 (of 23 total)

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