Post reply

LOAD DATA INFILE

  • December 11, 2008 at 8:59 am

    #202880

    I am new here. I did a forum search first but did not find anything that can help me.

    I am converting my database from MySQL to SQL server 2005. Normally I use a LODA DATA INFILE statement to dump a text file into a table in MySQL. This command does not seem to work in SQL 2005. Is there an alternative to this? Here is the command I am using now:

    LOAD DATA INFILE 'C:\PATH\TO\FILE.txt' INTO TABLE 'mytable' FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r'

    There is also a "backslash n" after the "\r" in the code but for some reason the forums don't like that code and they are taking out of the post.

    I hope someone can help me with this.

    Thanks in advance for your help.

  • December 11, 2008 at 9:13 am

    #911178

    replace "slash r" and "slash n" with the command that the forum obviously hates.

    --the TSQL equivilent would be this command: note this assumes the table already exists,

    and the number of columns in the table match the number of fields delimited by the field terminator

    BULK INSERT mytable FROM 'C:\PATH\TO\FILE.txt' '

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = ',' , --typically this is a comma or tab

    ROWTERMINATOR = 'slash n', --or slash r if it's coming from unix/linix

    FIRSTROW = 2 --assuming the first row had column names...if not firstrow=1

    )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 11, 2008 at 9:21 am

    #911182

    Thank you very very much for the fast reply. It works great and does what I need it to.

    Thanks again.

Viewing 3 posts - 1 through 3 (of 3 total)

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