SQL Import text files [closed]

Question

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:

[DATA]
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²

[RESULTS]
END TEST STOP
Maximum load: 12.345 kN
Maximum strength: 1.234 MPa

[GRAPHINFO]
Axis0=6;0;60;0;0;0
Axis1=3;0.000;25.000;255;0;65535
Graph1=0;1

[GRAPH]
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

[CHECKSUM]
123ABC4D

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:

----------------------------------------------------------------------------------------
|TestNo|TestDate|TestTime|LoadRate|StartLoad|StopLoad|Area|MaxLoad|MaxStrength|Checksum|

My second table looks like below:

------------------
|TestNo|Time|Load|

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
DROP TABLE #TestNo
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)
SELECT
data 
FROM
#TestNo
WHERE [ROW] = 2

--Creates TestDate temp table
DROP TABLE #TestDate
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 = (
                SELECT
                data
                FROM
                #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?

Answer 1

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.