I have a directory that will regularly have text files dumped in it that I'm trying to get imported into a single SQL table. Basic format of the text files is as such:

Test: 12345
Test type: Random Test Information - Not Important
Test date: 01/10/2010
Test time: 12:00:00

Channel number: 1
Rate: 12.345 kN/min
Start load: 12.345 kN
Stop load: 12 %
Area: 12345.678 mm²

Maximum load: 12.345 kN
Maximum strength: 1.234 MPa


Time [sec]  Load [kN]
0.050   0.123
0.100   0.456
0.150   0.789
0.200   1.123
0.250   1.456
0.300   1.789
0.350   2.123
0.400   2.456
0.450   2.789
0.500   3.123
0.550   3.456


The 'Graph' section can vary in length so the solution would need to know when to stop.

The data I'm wanting to import is

  • Test
  • Test Date
  • Test Time
  • Load Rate
  • Start Load
  • Stop Load
  • Area
  • Maximum Load
  • Maximum Strength
  • The Graph data
  • Checksum

My first table looks like below:


My second table looks like below:


TestNo will be my primary key to keep the data in check.

Update Now I've got the following

--Import Test Results File into temp TextImport table
DROP TABLE #TextImport
CREATE TABLE #TextImport ( line VARCHAR(8000) )

INSERT  INTO #TextImport
        ( line )
        EXECUTE MASTER..xp_cmdShell 'Type "\\FilePath\Example.txt"' 

--Creates TestNo temp table to parse out data within
CREATE TABLE #TestNo (data VARCHAR(8000), [row] VARCHAR(8000))

--Inserts TestNo into TestData temp table
INSERT INTO #TestNo(data, [row])
SELECT stuff(line, 1, 5+charindex('Test: ', line), ''),ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [ROW] FROM #TextImport

--Inserts TestNo from above into Results table
    --Assumes TestNo is always on Line 2
INSERT INTO [dbo].[Results] (TestNo)

--Creates TestDate temp table
CREATE TABLE #TestDate (data VARCHAR(8000), [row] VARCHAR(8000))

--Inserts TestDate into TestData temp table
INSERT INTO #TestDate(data, [row])
SELECT stuff(line, 1, 10+charindex('Test date: ', line), ''),ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [ROW] FROM #TextImport 

--Inserts TestDate from above into Results table
    --Assumes TestDate is always on Line 4
----Need to pull TestNo from above rather than hard-coding it
UPDATE [dbo].[Results]
SET TestDate = (
                WHERE [ROW] = 4
WHERE TestNo = 12345

So now the big questions are

  1. Can I get the TestNo from elsewhere so I don't have to hardcode it?
    • I feel like I could pull it from #TestNo somehow??
  2. How can I make this now loop through a folder instead of hardcoded and start pulling through files, processing them, then moving onto the next in the folder?
  3. How am I going to deal with Graph data? Time/Load and splitting them apart and pulling them into their table?
  • Each file represents an individual test - One run and done. – Kieren Connell Nov 6 '17 at 21:12

That shouldn't really be done on a single table, it will cause you all sorts of grief. Do it over two tables. Have your first table however you want, but without the "graph" values (time and load). Instead have simply a "graph_id" value or something similar. e.g. (based on your example):

|Heading         |     |
|Test No#        |     |
|Test Date       |     |
|Test Time       |     |
|Maximum Load    |     |
|Maximum Strength|     |
|graph_id        |     |
|Checksum        |     |

Then have another table that stores your graph details (time and load):

|graph_id        |     |
|time            |     |
|load            |     |

Access the data via a simple join.

  • Cheers for the input. I ended up coming to the same conclusion to splitting the data out across two tables. – Kieren Connell Nov 6 '17 at 4:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.