Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    54

    Unanswered: Load positional text file without delimited..

    Hi all!!!!
    Have you idea if it is possible to load data from positional text file without no comma (or other type) delimited??

    I know how do it with DB2:
    LOAD CLIENT FROM "<path><table>" OF ASC MODIFIED BY NULLINDCHAR=Y METHOD L (1 4, 5 6, ecc) MESSAGES "<path><table>.msg" INSERT INTO IDAT0100F (column1, column2, etc);

    and with Oracle:
    sqlldr userid=$USER_D/$PASSWORD_D@$SID_D control=$<path>/ctl/<ctlfile>.ctl bad=$<path>/bad/idat0100f.bad log=$CURPATH_APP/log/<table>.log rows=1000 readsize=65535 bindsize=65535
    and his Oracle ctl file :
    LOAD DATA
    INFILE '<path><positional text file>'
    INTO TABLE <table>
    INSERT
    (column1 POSITION (1:4),
    column2 POSITION (5:6),
    etc ..
    )


    but I don't know how with SQL Server? I have an Express version (2005)..

    thanks in advance
    Fabio

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You will need to create a format file. See BOL on BCP and format files.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SSIS and/or DTS packages can import fixed-width files as easily as delimited.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Posts
    54
    First thank you for your answers!!
    @blindman unfortunately I have Express edition (2005) without DTS :-(
    @rdjabarov I will document immediately on these topics!!

    Thank you very much.
    Fabio

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Even with Express you should still be able to use Management Studio and its wizards to import fixed-width data, though you may not be able to automate the process as a recurring job.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    either create a format file

    or load it to a single column table

    look up bcp or bulk insert
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by petitof
    Hi all!!!!

    INSERT
    (column1 POSITION (1:4),
    column2 POSITION (5:6),
    etc ..
    )
    this would be your format file

    Real format files look like

    EDIT: Anyone got a 2k5 sample?

    Code:
    7.0 
    14 
    1	SQLCHAR	0	26	""	1	HIST_ADD_TS 
    2	SQLCHAR	0	1	""	2	HIST_ADD_TYPE 
    3	SQLCHAR	0	8	""	3	HIST_ADD_BY 
    4	SQLCHAR	0	8	""	4	HIST_ADD_SYSUSER_BY 
    5	SQLCHAR	0	7	""	5	xid 
    6	SQLCHAR	0	42	""	6	lastname 
    7	SQLCHAR	0	15	""	7	firstname 
    8	SQLCHAR	0	1	""	8	IsDeleted 
    9	SQLCHAR	0	255	""	9	orig_admin_system 
    10	SQLCHAR	0	1	""	10	security_user 
    11	SQLCHAR	0	7	""	11	created_by 
    12	SQLCHAR	0	26	""	12	created_date 
    13	SQLCHAR	0	7	""	13	updated_by 
    14	SQLCHAR	0	26	"\r\n"	14	updated_date
    Last edited by Brett Kaiser; 10-21-09 at 14:05.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here's a sample if you need to skip a column in a table that doesn't have data in your file

    Code:
    7.0 
    3 
    1	SQLCHAR	0	255	""	1	Comment 
    2	SQLCHAR	0	26	""	2	ID 
    3	SQLCHAR	0	0	"\r\n"	0	LargeComment
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •