Post reply

How to load .txt file using OPENROWSET?

  • 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..

  • 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

  • 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.

  • June 13, 2008 at 7:05 am

    #828451

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

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

    - Paul

  • June 13, 2008 at 8:23 am

    #828539

    Same way it stored in single column only

  • 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.....

Viewing 6 posts - 1 through 5 (of 5 total)

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