SQL Server Performance

Scripting to import .csv files

Discussion in 'General DBA Questions' started by shekhar157, Apr 19, 2006.

  1. shekhar157 New Member

    Dear All,

    I have the data in given below format in .CSV file

    "RowId""INS_clientType""INS_ins_Type"
    "1""In""Rank"
    "2""Out""Rank"

    Now I have to import these files in my tables which have different structure like

    Insert into test(Row_id, Type, Date, activeyn, ins_type)
    select rowid, Ins_client_type, getdate(), 'y', INS_ins_Type from .CSV file.

    Can any give me the script for that its very urgent.

    Thanx in Advance

    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  2. Madhivanan Moderator

  3. mmarovic Active Member

    Hey, great find! I like these FAQ-like articles.
  4. Madhivanan Moderator

    Nigel has lot of stuffs in his website [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. shekhar157 New Member

    Thnx Madhivanan


    Regards
    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  6. shekhar157 New Member

    Hi,

    For using BCP I have to create the table ealrier before importing data into table. Is there any logic in SQL that we can import the data without creating tables prior so that I can create a proc for that and ask the user to just pass the parameter as path name where all the txt files exists and take this DIR into a temp table and after that on based of that tempDIR table i will take the file name and can import data for all tables automatically. I just need the way by which I dont have need to create the table prior to import the data. Coz there is thousands of .txt files from which I have to import the data. If i create the structure for all the tables it is very much time consuming. Its very urgent requirement.

    Regards

    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  7. mmarovic Active Member

    You can use bulk insert to import data into local temp table. However I am not quite sure I understand your requirements. You had table structure defined in your initial post.
  8. shekhar157 New Member

    Through Bulk Insert I also create a table prior to import the data for that I have to create all the structure according to the text file. I want to import the data in DTS manner like it create the table automatically just need to define the txt file format.

    "No passion in the world is equal to the passion to alter someone else's draft."

  9. satya Moderator

    If the structure is constant then you can take help of FORMATFILE option in BCP or BULK-INSERT, BOL is your best option to check the information.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. shekhar157 New Member

    Dear Satya,

    The format of different files is different. They originally a database tables which has been sent us in .CSV format to migrate in our database.

    Regards

    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  11. satya Moderator

    Then only DTS is the way to achive, as I haven't seen any other approach other than this if the table structure is not constant.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. shekhar157 New Member

    Dear Satya,

    Cant we create a script for DTS which can be run through query analyzer as it automatically take the file name and create the table auto. As this script require at client site. They are not familier with DTS or SQL.

    Regards
    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  13. shekhar157 New Member

    Hey All Gurus

    If there is any way to do this please tell me. Its very urgent and important for me.

    Regards

    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  14. Adriaan New Member

    So you are importing data from different clients. Does this data all end up in one (set of) target table(s), or does it go into different target tables?

    If there is just one target, then you should be able to create one staging table into which you load the client data. One client's data will go into columns 1, 2, 3; another client's data into 2, 5, 6; another into 4, 8, 9 - etc. etc. Add a column for identifying the data format of the source data. You then create a sproc that will process the data, in the way suggested by the "data format" column.

    If you have different (sets of) target tables, then you might be able to do it as described above, probably best with separate sprocs (otherwise you'll probably end up with spaghetti code) and quite probably best with separate staging tables as well.

    There is no quick way around this. Also remember that old saying, "Garbage in, garbage out!"
  15. shekhar157 New Member

    Dear Adriaan,

    We have to migrate the data which we found from the client in deifferent .txt files which contains different table data. My job is to match the .txt files to my database and migrate them into my local database as it dont have the same structure thats why i need script for that bcoz through DTS i have to do the process 1000 time. Other problem is that this practice also have to be done on client site and he is not much aware of sql server. We just can pass them the query code and only they can execute that one.

    Regards
    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  16. shekhar157 New Member

    Hello All,

    Its very urgent issue for me. If there is any way to do this please help me. I am waiting for that.

    Regards

    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  17. Adriaan New Member

    This situation, where you are having to deal with such inconsistent data to be merged into a single data structure, should never have arisen. At the very least you must be given enough time to analyze the data and write code to handle each of the transformations.

    There is no off-the-shelf solution for this type of process. Be precise and document your code, so if you ever need to repeat the whole thing you don't have to re-invent the wheel.
  18. shekhar157 New Member

    Hi All,

    Thanx U all for giving valuable time.

    Regards

    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  19. Adriaan New Member

    Don't hesitate to ask for help here - of course we can't do all the work for you, but we may be able to help you with specific issues.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page