{"id":318,"date":"2007-10-23T00:00:00","date_gmt":"2007-10-23T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/importing-text-based-data-workbench\/"},"modified":"2021-09-29T16:22:14","modified_gmt":"2021-09-29T16:22:14","slug":"importing-text-based-data-workbench","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/importing-text-based-data-workbench\/","title":{"rendered":"Importing Text-based data: Workbench"},"content":{"rendered":"<h3>Contents<\/h3>\n<ul id=\"contents\">\n<li><a href=\"#first\">Introduction<\/a><\/li>\n<li><a href=\"#second\">Fast Import with the Quirky Update technique<\/a><\/li>\n<li><a href=\"#third\">CSV Importing- Comma-delimited and Comedy-Limited.<\/a><\/li>\n<li><a href=\"#fourth\">Unrotating a CSV Pivot-table on import<\/a><\/li>\n<li><a href=\"#fifth\">Further Reading<\/a><\/li>\n<\/ul>\n<h3 id=\"first\">Introduction<\/h3>\n<p>It is hard to estimate the enormous number of unnecessary and unmaintainable SSIS and DTS files that are written merely to import data from text into SQL Server. For performance, and for the sanity of the DBA, it is usually better to allow SQL Server to import text and to pummel it into normalised relational tables, rather than rely on procedural techniques.<\/p>\n<p>There are many ways to read text into SQL Server including, amongst others, <code>BCP, BULK INSERT, OPENROWSET, OPENDATASOURCE, OPENQUERY<\/code>, or by setting up a linked server.<\/p>\n<p>Normally, for reading in a table from an external source such as a text file, one would use an <a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/aa276850(SQL.80).aspx\">OpenRowSet<\/a>, which can be referenced in the <code>FROM<\/code> clause of a query as though it were a table name. This is a topic that would take too long to tackle in this workbench, though we&#8217;ll show you an example of its use for reading in a CSV file. Perhaps one day we&#8217;ll do an OpenRowSet Workbench!&#8230;<\/p>\n<h3 id=\"second\">Fast import with the Quirky Update technique<\/h3>\n<p>So, you think you&#8217;re good at importing text-based data into SQL Server? A friend of ours made that mistake too, recently, when he tried to get a highly paid consultancy job in London. The interviewer guided him to an installation of SQL Server and asked him to import a text file. It had a million rows in it which were rather poorly formatted. As our friend stared at the data, his confident laugh turned to a gurgle of panic, as he suddenly realised that he wasn&#8217;t looking at simple columnar data, or delimited stuff, but something else, and something that looked tricky. Our friend realised too late that it was a &#8216;curved ball&#8217; and floundered embarassingly. Let&#8217;s simulate a few of the million rows just so you can see the problem.<\/p>\n<pre>frizbees     59787   654 c\r\ncricket bats     807453   9245 c\r\nstumps    80675   1348 s\r\ntennis rackets    74009   34  t\r\nwoggle 74009   34  t\r\nRunning shoes 4570   132  c\r\nfootball shorts and shirt (small, medium or large) 5928 132 c\r\n<\/pre>\n<p>There are, of course, several different approaches to turning this sort of mess into a table. we can <code>BCP<\/code> or <code>BULK INPUT<\/code> it into an imput table, in order to pummel it into shape. Actually, where record-lengths are short, one can do it even more simply this way.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">CREATE TABLE #Textimport ( line VARCHAR(8000) )\r\n\r\nINSERT  INTO #textImport\r\n        ( line )\r\n        EXECUTE MASTER..xp_cmdShell 'Type MyFile.TXT'   \r\n\r\n\/*\r\nBut for this exercise... we'll just create a sample '\r\n*\/\r\nDROP TABLE #import\r\nCREATE TABLE #import\r\n  (\r\n    line VARCHAR(8000),\r\n    firstone INT,\r\n    secondone INT,\r\n    thirdone INT\r\n  )\r\nINSERT  INTO #import  ( line )\r\n        SELECT  'frizbees     59787   654 c'\r\nUNION ALL\r\n        SELECT  'cricket bats     807453   9245 c'\r\nUNION ALL\r\n        SELECT  'stumps    80675   1348 s'\r\nUNION ALL\r\n        SELECT  'tennis rackets    74009   34  t'\r\nUNION ALL\r\n        SELECT  'woggle 74009   825  t'\r\nUNION ALL\r\n        SELECT  'Running shoes 4570   132  c'\r\nUNION ALL\r\n        SELECT  \r\n      'football shorts and shirt (small, medium or large) 5928 132 c'\r\n<\/pre>\n<p>And so the answer to the interview question was perfectly simple. With a million rows, one daren&#8217;t hang about, so here is a solution that does the trick quickly without a cursor in sight. Can you spot a neater method? Neither Phil nor I can.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">DECLARE \r\n  @first INT,\r\n  @second INT,\r\n  @third INT\r\n\r\nUPDATE  #import\r\nSET     @first = firstone = PATINDEX('%[0-9][0-9]%', line),\r\n        @second = secondone = @first + PATINDEX('%[^0-9][0-9]%',\r\n                         SUBSTRING(line, @first + 1, 2000)) + 1,\r\n        thirdone = @second + PATINDEX('%[^0-9][a-z]%',\r\n                         SUBSTRING(line, @second + 1, 2000)) + 1\r\n\r\nSELECT  [product] = CONVERT(VARCHAR(50), RTRIM(\r\n                         SUBSTRING(line, 1, firstone - 1))),\r\n        [sales] = CONVERT(INT, RTRIM(\r\n                         SUBSTRING(line, firstone,secondone - firstone))),\r\n        [Salesman_id] = CONVERT(INT, RTRIM(\r\n                         SUBSTRING(line, secondone, thirdone - secondone))),\r\n        [type] = CONVERT(CHAR(1), RTRIM(\r\n                         SUBSTRING(line, thirdone, 2000)))\r\nFROM    #import\r\n<\/pre>\n<p>Which gives:<\/p>\n<pre>product                                            sales   S_id    type\r\n-------------------------------------------------- ------- ------- ----\r\n\r\nfrizbees                                           59787    654    c\r\ncricket bats                                       807453   9245   c\r\nstumps                                             80675    1348   s\r\ntennis rackets                                     74009    34     t\r\nwoggle                                             74009    825    t\r\nRunning shoes                                      4570     132    c\r\nfootball shorts and shirt (small, medium or large) 5928     132    f\r\n<\/pre>\n<p>Of course, this needs a bit of explanation. What we are doing is to use the &#8216;Quirky Update&#8217; syntax in Sybase and SQL Server to allow us to update some special columns in the import table that tell us the column positions of the various pieces of data for each row, as they will be different in every row.<\/p>\n<p>The first column is terminated by the number (number of sales), so we need to use <code>PATINDEX<\/code> to tell us where this is. Then we have to look for the next number. The trouble with <code>PATINDEX<\/code> is that one cannot specify the start (or end) position of the search, so you have to use <code>SUBSTRING<\/code> for that. Finally we need to find that pesky character at the end.<\/p>\n<p>Now we have the column positions we can then parse it all neatly with a select statement.<\/p>\n<p>You&#8217;ll see that it would work even with spurious characters in the way such as [ ], and so on.<\/p>\n<p>Sometimes, one gets strange delimiters in data. Here is an example of how one might input a file from a monitoring system.<\/p>\n<pre>[stop-cock opened] &lt;&lt;&lt;&lt;(Matt)&gt;&gt;&gt;&gt;&gt;   [12\/3\/2007 12:09:00] \r\n [stop-cock closed] &lt;&lt;&lt;(Tony)&gt;&gt;&gt;&gt;   [12\/3\/2007 12:10:00] \r\n#not authorised [stop-cock opened] &lt;(Timothy)&gt;   [12\/3\/2007 13:21:00] \r\n [stop-cock closed] &lt;&lt;(Dave)&gt;&gt;&gt;   [12\/3\/2007 13:30:00] \r\n [stop-cock opened] &lt;&lt;&lt;&lt;(Matt)&gt;&gt;&gt;&gt;&gt;   [12\/3\/2007 15:18:00] \r\n#post-sign-off [stop-cock closed] &lt;&lt;&lt;(Matt)&gt;&gt;&gt;&gt;   [12\/3\/2007 15:20:00] \r\n<\/pre>\n<pre class=\"lang:tsql theme:ssms2012\">CREATE TABLE #importDelimited\r\n  (\r\n    line VARCHAR(8000),\r\n    firstone INT,\r\n    secondone INT,\r\n    thirdone INT,\r\n    fourthone INT,\r\n    fifthone INT,\r\n    Sixthone INT\r\n  )\r\nINSERT  INTO #importDelimited  ( line )\r\n        SELECT  ' [stop-cock opened] &lt;&lt;&lt;&lt;(Matt)&gt;&gt;&gt;&gt;&gt;   [12\/3\/2007 12:09:00] '\r\nUNION ALL\r\n        SELECT  ' [stop-cock closed] &lt;&lt;&lt;(Tony)&gt;&gt;&gt;&gt;   [12\/3\/2007 12:10:00] '\r\nUNION ALL\r\n        SELECT  '#not authorised [stop-cock opened] &lt;(Timothy)&gt;   [12\/3\/2007 13:21:00] '\r\nUNION ALL\r\n        SELECT  ' [stop-cock closed] &lt;&lt;(Dave)&gt;&gt;&gt;   [12\/3\/2007 13:30:00] '\r\nUNION ALL\r\n        SELECT  ' [stop-cock opened] &lt;&lt;&lt;&lt;(Matt)&gt;&gt;&gt;&gt;&gt;   [12\/3\/2007 15:18:00] '\r\nUNION ALL\r\n        SELECT  '#post-sign-off [stop-cock closed] &lt;&lt;&lt;(Matt)&gt;&gt;&gt;&gt;   [12\/3\/2007 15:20:00] '\r\n\/* OK, here is a bit of luck! The delimitors show us where the fields are. They may be inconsistent but that doesn't worry us. Heaven only knows what was going through the mind of the programmer who came up with this data format.*\/\r\nDECLARE \r\n  @first INT,\r\n  @second INT,\r\n  @third INT,\r\n  @Fourth INT, \r\n  @Fifth INT \r\n\r\nUPDATE  #importDelimited \r\nSET     @first = firstone = CHARINDEX('[', line),\r\n        @second = secondone = CHARINDEX(']',line,@first+1),\r\n        @third = thirdone = CHARINDEX('(',line,@second+1),\r\n        @fourth = fourthone = CHARINDEX(')',line,@third+1),\r\n        @fifth = fifthone = CHARINDEX('[',line,@fourth+1),\r\n       Sixthone = CHARINDEX(']',line,@fifth+1)\r\n                                      \r\nSELECT \r\n   CONVERT(VARCHAR(20),SUBSTRING(line,firstone+1,secondone-firstone-1)),\r\n   CONVERT(VARCHAR(10),SUBSTRING(line,thirdone+1,fourthone-thirdone-1)),\r\n   CONVERT(DATETIME,SUBSTRING(line,fifthone+1,sixthone-fifthone-1),103)\r\n\r\nFROM #importDelimited\r\n\/*\r\n-------------------- ---------- -----------------------\r\nstop-cock opened     Matt       2007-03-12 12:09:00.000\r\nstop-cock closed     Tony       2007-03-12 12:10:00.000\r\nstop-cock opened     Timothy    2007-03-12 13:21:00.000\r\nstop-cock closed     Dave       2007-03-12 13:30:00.000\r\nstop-cock opened     Matt       2007-03-12 15:18:00.000\r\nstop-cock closed     Matt       2007-03-12 15:20:00.000\r\n\r\n(6 row(s) affected)\r\n<\/pre>\n<h3 id=\"third\">CSV Importing- Comma-delimited and Comedy-Limited.<\/h3>\n<p>CSV, if done properly, is actually a very good way of representing a table as an ASCII file, even though its use has now been overtaken by XML. CSV is different from a simple comma-delimited format. The simple use of commas as field separators is often called &#8216;Comedy Limited&#8217;, because it is so incredibly useless and limiting.<\/p>\n<p>The real CSV allows commas or linebreaks in fields: well anything actually. It is described in <a href=\"http:\/\/www.creativyst.com\/Doc\/Articles\/CSV\/CSV01.htm\">The Comma Separated Value (CSV) File Format<\/a>, or <a href=\"http:\/\/www.csvreader.com\/csv_format.php\">CSV Files<\/a><\/p>\n<p>BCP is not a good way of reading CSV files; Unless you use a Format file, it will only do &#8216;comedy-limited&#8217; files. A much better method is to use ADODB provider MSDASQL, which does it properly.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">SELECT *\r\nFROM\r\n     OPENROWSET('MSDASQL',--provider name (ODBC)\r\n        'Driver={Microsoft Text Driver (*.txt; *.csv)};\r\n          DEFAULTDIR=C:\\;Extensions=CSV;',--data source\r\n        'SELECT * FROM sample.csv')\r\n<\/pre>\n<p>This assumes that the first row is the header, so you may need to add a first row.<\/p>\n<p>The ODBC TEXT driver will not output a table as a CSV file, unfortunately. The reason for this is mysterious. It would have been very useful.<\/p>\n<p>Sometimes, for a special purpose where a simple method like this won&#8217;t do, you have to develop a TSQL way. Sometimes, for example, you will find that records are separated by &#8216;[]&#8217; markers, or that comment or header lines are inserted with a prepended &#8216;#&#8217;. Sometimes quotes are &#8216;escaped&#8217; by a &#8216;\\&#8217; character.<\/p>\n<p>The first stage is to read the entire file into a SQL Server variable. Reading text into a VARCHAR(MAX) is very easy in SQL Server 2005. (For other ways in SQL Server 7 and 2000, see <a href=\"http:\/\/www.simple-talk.com\/sql\/t-sql-programming\/reading-and-writing-files-in-sql-server-using-t-sql\/\">Reading and Writing Files in SQL Server using T-SQL<\/a><\/p>\n<pre class=\"lang:tsql theme:ssms2012\">DECLARE @CSVfile VARCHAR(MAX) \r\nSELECT  @CSVfile = BulkColumn \r\nFROM    OPENROWSET(BULK 'C:\\sample.csv', SINGLE_BLOB) AS x\r\nSELECT @CSVfile\r\n<\/pre>\n<p>For this test, we&#8217;ll put the CSV file in a VARCHAR(MAX) variable.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">SET NOCOUNT ON\r\nDECLARE @CSVFile VARCHAR(MAX)\r\n\r\nSELECT  @CSVFile = '\r\nTony Davis,,,,\r\nRev D. Composition,02948 864938,10TH 7TH,\"The Vicarage,\r\nBlakes End,\r\nShropshire\",\r\nPhil Factor,04634 845976,FD4 5TY,\"The Lighthouse,\r\nAdstoft,\r\nNorfolk\",Phil@notanemail.com\r\nPolly Morphick,04593 584763,,\"\"\"The Hollies\"\",\r\nClumford High Street,\r\nChedborough,\r\nHants DF6 4JR\",Polly@NotAnEmail.com\r\nSir Relvar Predicate CB,01549 69785,FG10 6TH,\"The Grange,\r\nSouthend Magna,\r\nEssex.\",'\r\n<\/pre>\n<p>\/*here is the XML version by comparison<\/p>\n<pre class=\"lang:xml theme:vs2012\">&lt;document&gt;\r\n &lt;row&gt;\r\n  &lt;Col0&gt;Tony Davis&lt;\/Col0 &gt;\r\n  &lt;Col1&gt;&lt;\/Col1 &gt;\r\n  &lt;Col2&gt;&lt;\/Col2 &gt;\r\n  &lt;Col3&gt;&lt;\/Col3 &gt;\r\n  &lt;Col4&gt;&lt;\/Col4 &gt;\r\n &lt;\/row&gt;\r\n &lt;row&gt;\r\n  &lt;Col0&gt;Rev D. Composition&lt;\/Col0 &gt;\r\n  &lt;Col1&gt;02948 864938&lt;\/Col1 &gt;\r\n  &lt;Col2&gt;10TH 7TH&lt;\/Col2 &gt;\r\n  &lt;Col3&gt;The Vicarage,\r\nBlakes End,\r\nShropshire&lt;\/Col3 &gt;\r\n  &lt;Col4&gt;&lt;\/Col4 &gt;\r\n &lt;\/row&gt;\r\n &lt;row&gt;\r\n  &lt;Col0&gt;Phil Factor&lt;\/Col0 &gt;\r\n  &lt;Col1&gt;04634 845976&lt;\/Col1 &gt;\r\n  &lt;Col2&gt;FD4 5TY&lt;\/Col2 &gt;\r\n  &lt;Col3&gt;The Lighthouse,\r\nAdstoft,\r\nNorfolk&lt;\/Col3 &gt;\r\n  &lt;Col4&gt;Phil@notanemail.com&lt;\/Col4 &gt;\r\n &lt;\/row&gt;\r\n &lt;row&gt;\r\n  &lt;Col0&gt;Polly Morphick&lt;\/Col0 &gt;\r\n  &lt;Col1&gt;04593 584763&lt;\/Col1 &gt;\r\n  &lt;Col2&gt;&lt;\/Col2 &gt;\r\n  &lt;Col3&gt;\"The Hollies\",\r\nClumford High Street,\r\nChedborough,\r\nHants DF6 4JR&lt;\/Col3 &gt;\r\n  &lt;Col4&gt;Polly@NotAnEmail.com&lt;\/Col4 &gt;\r\n &lt;\/row&gt;\r\n &lt;row&gt;\r\n  &lt;Col0&gt;Sir Relvar Predicate CB&lt;\/Col0 &gt;\r\n  &lt;Col1&gt;01549 69785&lt;\/Col1 &gt;\r\n  &lt;Col2&gt;FG10 6TH&lt;\/Col2 &gt;\r\n  &lt;Col3&gt;The Grange,\r\nSouthend Magna,\r\nEssex.&lt;\/Col3 &gt;\r\n  &lt;Col4&gt;&lt;\/Col4 &gt;\r\n &lt;\/row&gt;\r\n&lt;\/document&gt;<\/pre>\n<pre class=\"lang:tsql theme:ssms2012\">DECLARE @StartOfRecord INT,\r\n  @RecordNo INT,\r\n  @FieldNo INT,\r\n  @WhatsLeftInText VARCHAR(MAX),\r\n  @DelimiterType VARCHAR(20),\r\n  @EndOfField INT,\r\n  @Delimiter VARCHAR(8),\r\n  @eat INT,\r\n  @jj INT,\r\n  @jjmax INT,\r\n  @Escape INT,\r\n  @MoreToDo INT\r\nDECLARE @OurTable TABLE (Field INT, record INT,Contents VARCHAR(8000))\r\n\r\n\r\n\r\nSELECT  @CSVFile = LTRIM(@CSVfile),\r\n       @StartOfRecord = 1, \r\n       @RecordNo = 1, @FieldNo = 1, @MoreToDo = 1\r\n--iterate for each field \r\nWHILE @MoreToDo = 1\r\n  BEGIN\r\n   --identify the delimiter for this field \r\n    SELECT  @Delimiter = SUBSTRING(LTRIM(@CSVfile), @StartOfRecord, 1),\r\n            @eat = 0\r\n    IF @Delimiter = ',' \r\n      SELECT  @DelimiterType = 'Field'\r\n    ELSE \r\n      IF @Delimiter IN ( CHAR(13), CHAR(10) )\r\n--The end of record delimiters are sometimes other characters such as a semicolon       \r\n        SELECT  @DelimiterType = 'RecordEnd'\/* Records are separated with CRLF (ASCII 13 Dec or 0D Hex and ASCII 10 Dec or 0A Hex respectively) for Windows, LF for Unix, and CR for Mac*\/\r\n      ELSE \r\n        IF @Delimiter LIKE '\"' \r\n          SELECT  @DelimiterType = 'Complex'\r\n        ELSE \r\n          SELECT  @DelimiterType = 'RecordStart'\r\n    IF @DelimiterType = 'Field' \r\n      BEGIN --this starts with a comma\r\n        SELECT  @eat = 1\r\n        --check to see if it is quotes-delimited\r\n        IF ( SUBSTRING(LTRIM(@CSVfile), @StartOfRecord + @eat, 1) = '\"' ) \r\n          SELECT  @eat = 2, @DelimiterType = 'Complex'\r\n      END\r\n    --let's work on the remaining text rather than the whole file'  \r\n    SELECT  @WhatsLeftInText = STUFF(@CSVFile, 1, @StartOfRecord + @eat - 1,\r\n                                     '')    \r\n    IF @DelimiterType IN ( 'Field', 'RecordStart' ) \r\n      BEGIN--and we will get the end of the simple field\r\n        SELECT  @EndOfField = PATINDEX('%[,' + CHAR(13) + CHAR(10) + ']%',\r\n                                       @WhatsLeftInText)\r\n        IF @EndOfField = 0 --of not there then we are at the end of the file\r\n          SELECT  @EndOfField = LEN(@WhatsLeftInText), @MoreToDo = 0 \r\n      END                            \r\n    ELSE\r\n      IF @DelimiterType = 'Complex'  --this is where it gets tricky!\r\n        BEGIN\r\n          SELECT  @jj = 1, @jjMax = LEN(@WhatsLeftInText), @escape = 0\r\n          WHILE @jj &lt;= @jjMax\r\n            BEGIN\r\n              IF ( SUBSTRING(@WhatsLeftInText, @jj, 1) = '\"' ) \r\n                BEGIN --walk over double 'escaped' quotes\r\n--The double quote char is sometimes replaced with a single quote or apostrophe    \r\n                  SELECT  @escape = CASE @escape\r\n                                      WHEN 1 THEN 0\r\n                                      ELSE 1\r\n                                    END\r\n                END\r\n              ELSE \r\n                IF @Escape = 1 \r\n                  BREAK--then it was a  quote by itself\r\n              SELECT  @jj = @jj + 1    \r\n            END\r\n          SELECT  @EndOfField = @jj - 1, @eat = @eat + 1\r\n          IF @jj &gt; @jjMax \r\n            SELECT  @MoreToDo = 0 --reached end of file\r\n        END\r\n    IF @EndofField = 0 \r\n      SELECT  @EndOfField = 1--prevent invalid parameter\r\n    IF @DelimiterType = 'RecordEnd' --The last record in a file may or\r\n    -- may not be ended with an end of line character\r\n      SELECT  @RecordNo = @RecordNo + 1, @FieldNo = 1,\r\n              @StartOfRecord = @StartOfRecord + 2\r\n    ELSE \r\n      BEGIN\r\n       INSERT INTO @OurTable (Field,Record,contents)\r\n        SELECT  @FieldNo, @RecordNo,\r\n               --turn paired quotes into single quotes\r\n                CASE WHEN @DelimiterType = 'Complex'\r\n                     THEN REPLACE(SUBSTRING(@WhatsLeftInText, 1,\r\n                                            @EndOfField - 1), '\"\"', '\"')\r\n                     ELSE SUBSTRING(@WhatsLeftInText, 1, @EndOfField - 1)\r\n                END\r\n--sometimes, Non-printable characters in a field are escaped with one of\r\n--several  character escape sequences such as \\### and \\o### (Octal),\r\n-- \\x## (Hex), \\d### (Decimaal), and \\u#### (unicode)\r\n        SELECT  @FieldNo = @FieldNo + 1,\r\n                @StartOfRecord = @StartOfRecord + @eat + @EndOfField - 1\r\n      END\r\n  END\r\n       \r\nSELECT  [name]=t1.contents, \r\n       [phone]=t2.contents,\r\n       [Postcode]=t3.contents,\r\n       [Address]=t4.contents,\r\n       [Email]=t5.contents\r\nFROM    @ourtable t1 \r\n  INNER JOIN @ourtable t2 \r\n   ON t1.field = 1 AND t2.field = 2 AND t1.record = t2.record\r\n  INNER JOIN @ourtable t3 \r\n    ON   t3.field = 3 AND t1.record = t3.record\r\n  INNER JOIN @ourtable t4 \r\n    ON   t4.field = 4 AND t1.record = t4.record\r\n  INNER JOIN @ourtable t5 \r\n    ON   t5.field = 5 AND t1.record = t5.record\r\n    <\/pre>\n<h3 id=\"fourth\">Unrotating a CSV Pivot-table on import<\/h3>\n<p>We&#8217;ll end up with one of Phil&#8217;s real life routines that is used to get daily exchange rate information for a multi-currency ecommerce site. This gets a text file which is in Comedy-limited format (comma-separated) which is gotten from the Bank of Canada&#8217;s internet site. There are several comment lines starting with a <code>#<\/code> character and the first non-comment line contains the headings.<\/p>\n<pre>Date (\/\/),10\/01\/2007,10\/02\/2007,10\/03\/2007,10\/04\/2007,10\/05\/2007,10\/08\/2007,10\/09\/2007\r\nClosing Can\/US Exchange Rate,0.9914,0.9976,0.9984,0.9974,0.9818,N\/A,N\/A\r\nU.S. Dollar (Noon),0.9931,1.0004,0.9961,0.9983,0.9812,NA,0.9846\r\nArgentina Peso (Floating Rate),0.3114,0.3145,0.3131,0.3123,0.3072,NA,0.3083\r\nAustralian Dollar,0.8868,0.8848,0.8846,0.8867,0.8828,NA,0.8836\r\n..etc...\r\n<\/pre>\n<p>And we want to &#8216;unpivot&#8217; it into back into a table in the format &#8230;..<\/p>\n<pre>Date                    currency                       rate\r\n----------------------- ------------------------------ --------\r\n2007-10-01 00:00:00.000 Closing Can\/US Exchange Rate   0.991400\r\n2007-10-01 00:00:00.000 U.S. Dollar (Noon)             0.993100\r\n2007-10-01 00:00:00.000 Argentina Peso (Floating Rate) 0.311400\r\n2007-10-01 00:00:00.000 Australian Dollar              0.886800\r\n2007-10-01 00:00:00.000 Bahamian Dollar                0.993100\r\n2007-10-01 00:00:00.000 Brazilian Real                 0.546100\r\n2007-10-01 00:00:00.000 Chilean Peso                   0.001949\r\n2007-10-01 00:00:00.000 Chinese Renminbi               0.132300\r\n<\/pre>\n<p>You&#8217;ll see that it is simple to start an archive of daily currency fluctuations with something like this:<\/p>\n<p>To start with we will need to install <code>CURL<\/code> on the server. <code>CURL<\/code> is extraordinarily useful as a way of getting text into SQL Server from awkward places such as secure FTP sites, or simply from internet sites. Then we will need a couple of utility functions which as provided below. You&#8217;ll see how easy it is to &#8216;unpivot&#8217; a pivot table back into a data table!<\/p>\n<p>(this was originally in one of Phil&#8217;s blogs)<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">CREATE PROCEDURE spGetLatestCanadianExchangeRates\r\n\r\n--allow the whereabouts of the CSV file to be specified\r\n@WhereFrom VARCHAR(255)\r\n='http:\/\/www.bankofcanada.ca\/en\/markets\/csv\/exchange_eng.csv'\r\nAS\r\n\/*\r\nNote on the exchange rates:\r\nThe daily noon exchange rates for major foreign currencies are\r\npublished every business day at about 1 p.m. EST. They are \r\nobtained from market or official sources around noon, and show \r\nthe rates for the various currencies in Canadian dollars \r\nconverted from US dollars. The rates are nominal quotations -\r\nneither buying nor selling rates - and are intended for \r\nstatistical or analytical purposes. Rates available from financial\r\ninstitutions will differ.\r\n*\/\r\nDECLARE @Command VARCHAR(8000) \r\n       \r\n--the command line sent to xp_cmdshell\r\n\r\nSELECT @Command='curl -s -S \"'+@wherefrom+'\"'\r\n\r\nCREATE TABLE #rawCSV (LineNumber INT IDENTITY(1,1),\r\n       LineContents VARCHAR(8000))--for the output\r\n\r\nINSERT INTO #rawCSV(LineContents)\r\n       EXECUTE MASTER..xp_cmdshell @Command--get the data\r\n--find the column headings \r\n       --(indicator will vary from file to file)\r\nDECLARE @Headings VARCHAR(8000) \r\n       --the headings for the columns in the CSV file\r\nSELECT @headings= LineContents \r\n       FROM #rawCSV WHERE LineContents LIKE 'date %'\r\n\r\n--and then it is one SQL Call thanks to a couple of \r\n                               --utility functions\r\nSELECT [Date]=CONVERT(DATETIME,item,101), \r\n       [currency]=CONVERT(VARCHAR(50),\r\n                       dbo.ufsElement(linecontents,1,',')),\r\n       [rate]=CONVERT(numeric(9,6),\r\n                       dbo.ufsElement(linecontents,SeqNo,',')\r\n) \r\nFROM \r\n       (SELECT SeqNo,Item FROM dbo.ufsSplit(@Headings,',') \r\n       WHERE item NOT LIKE 'Date%'\r\n       )f--a table of the headings, with their order\r\nCROSS JOIN\r\n     (SELECT LineContents FROM #rawCSV WHERE lineContents NOT LIKE '#%' \r\n           AND lineContents NOT LIKE 'Date%')g\r\nWHERE ISNUMERIC(dbo.ufsElement(linecontents,SeqNo,','))&gt;0\r\n\r\n\r\nGO\r\n\r\n--and here are the utility functions--------------------------------\r\n\r\nCREATE FUNCTION dbo.ufsSplit\r\n(\r\n@StringArray VARCHAR(8000),\r\n@Delimiter VARCHAR(10)\r\n)\r\nRETURNS\r\n@Results TABLE\r\n   (\r\n   SeqNo INT IDENTITY(1, 1),\r\n   Item VARCHAR(8000)\r\n   )\r\n--splits a string into a table using the specified delimitor. Works like 'Split' in most languages\r\n--delimiters can be multi-character\r\nAS\r\nBEGIN\r\n\r\nDECLARE @Next INT\r\nDECLARE @lenStringArray INT\r\nDECLARE @lenDelimiter INT\r\nDECLARE @ii INT\r\n\r\nSELECT @ii=1, @lenStringArray=LEN(\r\n@StringArray), @lenDelimiter=LEN(@Delimiter)\r\n\r\nWHILE @ii&lt;=@lenStringArray\r\n   BEGIN\r\n   SELECT @next=CHARINDEX(@Delimiter, @StringArray + @Delimiter, @ii)\r\n   INSERT INTO @Results (Item)\r\n   SELECT SUBSTRING(@StringArray, @ii, @Next - @ii)\r\n   SELECT @ii=@Next+@lenDelimiter\r\n   END\r\nRETURN\r\nEND\r\n\r\nGO\r\n--------------------------------------------------------------------------\r\nCREATE FUNCTION dbo.ufsElement\r\n\r\n( \r\n@String VARCHAR(8000),\r\n@which INT,\r\n@Delimiter VARCHAR(10) = ',' \r\n) \r\n--splits a string to get at the nth component in the string using the specified delimiter\r\n--delimiters can be multi-character\r\nRETURNS VARCHAR(8000) AS \r\n\r\nBEGIN \r\nDECLARE @ii INT\r\nDECLARE @Substring VARCHAR(8000)\r\n\r\nSELECT @ii=1, @Substring=''\r\n\r\nWHILE @ii &lt;= @which \r\n   BEGIN \r\n\r\n   IF (@String IS NULL OR @Delimiter IS \r\nNULL )\r\n      BEGIN\r\n      SELECT @Substring=''\r\n      BREAK \r\n      END\r\n\r\n   IF CHARINDEX(@Delimiter,@String) = 0 \r\n      BEGIN \r\n      SELECT @subString = @string\r\n      SELECT @String=''\r\n      END \r\n   ELSE\r\n      BEGIN\r\n      SELECT @subString = SUBSTRING( @String, 1, CHARINDEX( @Delimiter, @String )-1)\r\n      SELECT @String = SUBSTRING\r\n( @String, CHARINDEX( @Delimiter, @String )+LEN(@delimiter),LEN(@String))\r\n   END\r\n   SELECT @ii=@ii+1\r\nEND \r\n\r\nRETURN (@subString) \r\nEND\r\n<\/pre>\n<p>So, we hope we&#8217;ve given you a few ideas on how to deal with importing text into a database without resorting to a whole lot of scripting. We&#8217;ve only tackled a few examples and steered clear of thorny topics such as BCP, DTS and SSIS. We&#8217;d be interested to hear of any sort of text-based format that you feel would be too hard for TSQL to deal with.<\/p>\n<h3 id=\"fifth\">Further Reading<\/h3>\n<ul class=\"reference-list\">\n<li><a href=\"http:\/\/www.nigelrivett.net\/ImportTextFiles.html\"><strong>Importing text files<\/strong> Author Nigel Rivett<\/a><\/li>\n<li><a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms190479.aspx\"><strong>Adding a linked server<\/strong><\/a><\/li>\n<li><a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms162802.aspx\"><strong>BCP<\/strong><\/a><\/li>\n<li><a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms188365.aspx\"><strong>Bulk Insert<\/strong><\/a><\/li>\n<li><a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms190312.aspx\"><strong>OPENROWSET<\/strong><\/a><\/li>\n<li><a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms179856.aspx\"><strong>OPENDATASOURCE<\/strong><\/a><\/li>\n<li><a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms188427.aspx\"><strong>OPENQUERY<\/strong><\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Robyn and Phil return with some fresh ideas about how to import text files into SQL Server, without resorting to DTS or SSIS scripting. They go on to show how much can be done in TSQL &hellip;<\/p>\n","protected":false},"author":221812,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4151,4306,4252,4190,4829,4460],"coauthors":[6813,6814],"class_list":["post-318","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-server","tag-ssis","tag-t-sql-programming","tag-tsql","tag-tsql-sql-import-text-into-sql-server","tag-workbench"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/318","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221812"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=318"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/318\/revisions"}],"predecessor-version":[{"id":67047,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/318\/revisions\/67047"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=318"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=318"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=318"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=318"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}