LOAD DATA INFILE

• todd.spayde

  Old Hand

  Points: 304

  More actions

  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.

• Lowell

  SSC Guru

  Points: 323527

  More actions

  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!

• todd.spayde

  Old Hand

  Points: 304

  More actions

  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.