0

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?

closed as off-topic by LowlyDBA, Max Vernon, mustaccio, McNets, Colin 't Hart Jan 28 at 20:27

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, Max Vernon, McNets
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Each file represents an individual test - One run and done. – Kieren Connell Nov 6 '17 at 21:12
2

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.