How to load .txt file using OPENROWSET?

• sharma-691993

  SSCrazy

  Points: 2194

  More actions

  June 12, 2008 at 10:48 am

  #183434

  Hi

  How to load .txt file using OPENROWSET?

  SELECT *

  INTO LOAD_TABLE

  FROM OPENROWSET('MSDASQL',

  'Driver={Microsoft.Jet.OLEDB.4.0 (*.txt)};DBQ=D:\DATA\DATA.txt')

  It shows syntax error..

• paulchernoch

  SSC-Addicted

  Points: 441

  More actions

  June 12, 2008 at 1:59 pm

  #828105

  Try this syntax to read from junk.txt:

  select * from OpenRowset('MSDASQL',

  'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Temp;Extended properties=''ColNameHeader=True;Format=CSVDelimited;''','select * from junk.txt')

  I shamelessly copied this (with modification) from Vasc's March 2006 post.

  He also commented about how you can create an INI file to hold additional instructions for the data transfer.

  - Paul

• sharma-691993

  SSCrazy

  Points: 2194

  More actions

  June 13, 2008 at 1:05 am

  #828283

  Hi I had used the query

  select * INTO EMP_TABLE from OpenRowset('MSDASQL',

  'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Temp;Extended properties=''ColNameHeader=True;Format=pipeDelimited;''','select * from junk.txt')

  But each row is stored in one column only.

  I am using | pipe sepeartor.

  Based on the data, it would create table with coulmns.

  I did this using CSV file. But pipe seprated .txt file won't work.

• paulchernoch

  SSC-Addicted

  Points: 441

  More actions

  June 13, 2008 at 7:05 am

  #828451

  Try using "Format=Delimited(|)" instead.

  See this website: http://www.connectionstrings.com/?carrier=textfile

  - Paul

• sharma-691993

  SSCrazy

  Points: 2194

  More actions

  June 13, 2008 at 8:23 am

  #828539

  Same way it stored in single column only

• john.savolaine

  SSC Journeyman

  Points: 85

  More actions

  August 26, 2008 at 2:35 pm

  #863394

  I'm a newbie around here - and I don't know if anyone is still paying attention to this thread - but I just found that was able to succeed with a maneuver like this when I used a schema.ini file with the entry Format=Delimited(|) in it, but I did NOT succeed when I used that same format statement as a part of the extended properties of the openrowset connection string.

  Don't know why.....