sql-server - 如何将此格式化的 txt 文件导入 SQL Server 表

Question

我有 txt 文件，它们是一个特定软件的输出，该文件始终具有相同的格式，请参见下文。数据从第 31 行开始，在这个例子中我只放了 TXT 文件的感觉行，我不能改变这种格式，因为它是一个软件的输出。我需要将此文件导入到 SQL Server 中的表中。我该怎么做？

 RECORDED_YEAR                  Col:     1 -     4 Decs:   0 Mult: 1.000000  
 RECORDED_DAY                   Col:     5 -     8 Decs:   0 Mult: 1.000000  
 RECORDED_HOUR                  Col:     9 -    10 Decs:   0 Mult: 1.000000  
 RECORDED_MINUTE                Col:    11 -    12 Decs:   0 Mult: 1.000000  
 RECORDED_SECOND                Col:    13 -    14 Decs:   0 Mult: 1.000000  
 SHOTLINE_NUMBER                Col:    18 -    21 Decs:   0 Mult: 1.000000  
 SHOT_POINT_NO                  Col:    22 -    25 Decs:   0 Mult: 1.000000  
 RECEIVERLINE_NUMBER            Col:    26 -    29 Decs:   0 Mult: 1.000000  
 FIELD_STATION_NUMBER           Col:    30 -    33 Decs:   0 Mult: 1.000000  
 XREC                           Col:    35 -    45 Decs:   2 Mult: 1.000000  
 YREC                           Col:    46 -    56 Decs:   2 Mult: 1.000000  
 ELEV_REC                       Col:    57 -    62 Decs:   2 Mult: 1.000000  
 XSHOT                          Col:    63 -    73 Decs:   2 Mult: 1.000000  
 YSHOT                          Col:    74 -    84 Decs:   2 Mult: 1.000000  
 ELEV_SHOT                      Col:    85 -    90 Decs:   2 Mult: 1.000000  
 TRCHDR3_TILTERROR              Col:    91 -    92 Decs:   0 Mult: 1.000000  
 TRCHDR3_RESISTERROR            Col:    93 -    94 Decs:   0 Mult: 1.000000  
 TRCHDR5_LEAKAGEERROR           Col:    95 -    96 Decs:   0 Mult: 1.000000  
 FIELD_RECORD_NO                Col:    97 -   102 Decs:   0 Mult: 1.000000  
 EXTHDR_SWATHID                 Col:   103 -   106 Decs:   0 Mult: 1.000000  
 DATA_RMSAMPLITUDE              Col:   109 -   119 Decs:   8 Mult: 10000.000000  
 VWUSER_1                       Col:   121 -   125 Decs:   0 Mult: 1.000000  
 CHANNEL_NO                     Col:   127 -   137 Decs:   0 Mult: 1.000000  
 VWUSER_7                       Col:   140 -   142 Decs:   0 Mult: 1.000000  
 VWUSER_8                       Col:   144 -   146 Decs:   1 Mult: 1.000000  
 DATA_MAXFREQ                   Col:   148 -   154 Decs:   3 Mult: 1.000000  
 DATA_MAXABSAMPLITUDE           Col:   156 -   166 Decs:   4 Mult: 10000.000000  
 VWUSER_22                      Col:   168 -   175 Decs:   3 Mult: 1.000000  
 VWUSER_11                      Col:   177 -   181 Decs:   0 Mult: 1.000000  
 VWUSER_12                      Col:   183 -   187 Decs:   0 Mult: 1.000000  
  18 327113458   5090115210965074     0.00       0.00   0.00      0.00       0.00   0.00   0 0 0    12   1  57.74633959     1           1    0 1.0 13.645  3703.4148   0.008        1     1 
  18 327113458   5090115210965075     0.00       0.00   0.00      0.00       0.00   0.00   0 0 0    12   1  35.32746807     1           2    0 1.0 18.519  3493.8994   0.008        1     1            
  18 327113458   5090115210965076     0.00       0.00   0.00      0.00       0.00   0.00   0 0 0    12   1  86.58912033     1           3    0 1.0 22.904  4077.5797   0.008        1     1            
  18 327113458   5090115210965077     0.00       0.00   0.00      0.00       0.00   0.00   0 0 0    12   1  53.32520232     1           4    0 1.0 23.392  5024.1262   0.008        1     1            
  18 327113458   5090115210965078     0.00       0.00   0.00      0.00       0.00   0.00   0 0 0    12   1  87.56771684     1           5    0 1.0 22.417  6922.9585   0.008        1     1    

标签： sql-serverimport

Answer 1

它看起来像固定长度的数据。您可以尝试以下批量插入，然后使用 Substring() 将结果解析到表中

CREATE TABLE #tempTable
(
  RowVal VarChar(Max)
)

BULK INSERT #tempTable
FROM 'c:\Downloads\Fixedtxt.txt' 
WITH 
(
    FIRSTROW = 31,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

SELECT * FROM #tempTable

以下是对代表数据的文本文件的结构化部分的一些解析。

CREATE TABLE #tempTable
(
  RowVal VarChar(Max)
)

BULK INSERT #tempTable
FROM 'c:\Downloads\Fixedtxt.txt' 
WITH 
(
    FIRSTROW = 31,
    ROWTERMINATOR = '\n'
)

SELECT 
   SubString(RowVal,1,5) As f01, 
   SubString(RowVal,6,9) As f02, 
   SubString(RowVal,18,16) As f03, 
   SubString(RowVal,35,8) As f04, 
   SubString(RowVal,44,10) As f05, 
   SubString(RowVal,55,6) As f06, 
   SubString(RowVal,62,9) As f07, 
   SubString(RowVal,72,10) As f08, 
   SubString(RowVal,83,6) As f09, 
   SubString(RowVal,90,3) As f10, 
   SubString(RowVal,94,1) As f11, 
   SubString(RowVal,96,1) As f12, 
   SubString(RowVal,98,5) As f13, 
   SubString(RowVal,104,3) As f14, 
   SubString(RowVal,108,12) As f15, 
   SubString(RowVal,121,5) As f16, 
   SubString(RowVal,127,11) As f17, 
   SubString(RowVal,139,4) As f18, 
   SubString(RowVal,144,3) As f19, 
   SubString(RowVal,148,6) As f20, 
   SubString(RowVal,155,10) As f21, 
   SubString(RowVal,166,7) As f22, 
   SubString(RowVal,174,8) As f23, 
   SubString(RowVal,183,5) As f24 
FROM 
   #tempTable

结果：

f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24
  18    327113458   5090115210965074        0.00          0.00    0.00       0.00         0.00    0.00    0 0   0      12     1  57.74633959        1             1    0    1.0 13.645   3703.4148    0.008        1        1
  18    327113458   5090115210965075        0.00          0.00    0.00       0.00         0.00    0.00    0 0   0      12     1  35.32746807        1             2    0    1.0 18.519   3493.8994    0.008        1        1
  18    327113458   5090115210965076        0.00          0.00    0.00       0.00         0.00    0.00    0 0   0      12     1  86.58912033        1             3    0    1.0 22.904   4077.5797    0.008        1        1
  18    327113458   5090115210965077        0.00          0.00    0.00       0.00         0.00    0.00    0 0   0      12     1  53.32520232        1             4    0    1.0 23.392   5024.1262    0.008        1        1
  18    327113458   5090115210965078        0.00          0.00    0.00       0.00         0.00    0.00    0 0   0      12     1  87.56771684        1             5    0    1.0 22.417   6922.9585    0.008        1        1