SQL 2008 R2 - Import Flatfile

• RolfW

  SSC Rookie

  Points: 28

  More actions

  November 15, 2012 at 7:41 am

  #270305

  Hello there,

  I want to create a script, which loads a flat file in a table.

  The flat file look like this:

  65262891001000100000

  571719690010001000000

  538999100010082201673

  with \CR\LF at the end.

  Here is my table:

  CREATE TABLE [dbo].[CTITEL](

  [Col001] [char](8) NULL,

  [Col002] [char](21) NULL)

  So I would like to import from 0-7 (Col001) and 8-29 (Col002).

  Could you tell me, how this work? I have no practices on SQL with Microsoft. At the moment I think, that "Bulk insert" should be the way it works.

  Perhaps someone of you could help me.

  Thank you.

  Kind Regards,

  RolfW

  P.S: I hope thi sis the right forum for this.

• Arthur Kirchner

  Ten Centuries

  Points: 1239

  More actions

  November 15, 2012 at 1:49 pm

  #1559519

  Hi there,

  BULK INSERT works fine, see the details here: http://msdn.microsoft.com/en-us/library/ms188365.aspx

  To split the strings, you can help yourself with a temporary table.

  What confused me as a newbie was the file path:

  "

  data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \\Systemname\ShareName\Path\FileName. For example, \\SystemX\DiskZ\Sales\update.txt.

  "

  Here is a basic example:

  CREATE TABLE #tmp (Col NVARCHAR(32))

  BULK INSERT #tmp FROM 'C:\PathTo\flatfile.txt'

  INSERT [dbo].[CTITEL]

  SELECT

  SUBSTRING(Col, 1, 8),

  SUBSTRING(Col, 9, 21)

  FROM #tmp

  DROP TABLE #tmp

• RolfW

  SSC Rookie

  Points: 28

  More actions

  November 16, 2012 at 2:22 am

  #1559693

  Hello Arthur,

  thanks a lot. Worked fine.

  Kind regards,

  RolfW