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
- 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??
- 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?
- How am I going to deal with Graph data? Time/Load and splitting them apart and pulling them into their table?