Файл с фиксированной шириной для всей таблицы sql server

У меня есть текстовый файл с данными в фиксированной ширине. Текстовый файл содержит идентификатор, данные, состояние. ID это просто INT. Штат имеет 2 ЧАР. Данные - это информация о компаниях, которые зарегистрированы в этом штате.

Чтобы поместить их в таблицы сервера SQL, я сначала сбросил текстовый файл в таблицу SQLServer с именем dbo.rawcompanyinfo_delimited

Примечание: для простоты объяснения я показал только 4 столбца в столбце данных. Он имеет 500 столбцов.

CREATE TABLE dbo.rawcompanyinfo_delimited(ID smallint NOT NULL, Data VARCHAR(MAX) NULL, State CHAR(2));

INSERT INTO dbo.rawcompanyinfo_delimited(100,'ABCINC    111  333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(200,'APPLE     213  333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(300,'BTEC      100  123.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(400,'S INC     123  333.0 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(500,'B INC     145  123.2 USD','PA')

У меня также есть таблица сопоставления, которая сообщает мне начальную позицию, длину и имена столбцов, хранящиеся в map.CompaniesLenInfo

CREATE TABLE map.CompaniesLenInfo(Startingposition int not null, Length int not null, columnnames varchar(100) not null)
insert into map.CompaniesLenInfo(1,10,CompanyName)
insert into map.CompaniesLenInfo(11,3,CompanyID)
insert into map.CompaniesLenInfo(15,5,TotalIncome)
insert into map.CompaniesLenInfo(21,3,Currency)

Я написал вложенный курсор, который проходит через map.CompaniesLenInfo, а затем через dbo.rawcompanyinfo_delimited и сохраняет результаты в таблице. как ниже

CREATE TABLE dbo.output(ID INT , CompanyName VARCHAR(10),CompanyID VARCHAR(3),TotalIncome VARCHAR(5),Currency VARCHAR(3)) ;
INSERT INTO dbo.output(100,'ABCINC','111','333.5','USD','PA')
INSERT INTO dbo.output(200,'APPLE','213','333.5', 'USD','PA')
INSERT INTO dbo.output(300,'BTEC'      ,'100',  '123.5', 'USD','PA')
INSERT INTO dbo.output(400,'S INC'     ,'123',  '333.0', 'USD','PA')
INSERT INTO dbo.output(500,'B INC'     ,'145',  '123.2', 'USD','PA')

DECLARE @ID INT,@StartPosition INT,@Len INT;
DECLARE @Data NVARCHAR(MAX), @ColumnName VARCHAR(100),@Val VARCHAR(MAX),@CompanyID CHAR(9),@State_Code  VARCHAR(2);
DECLARE @Currency VARCHAR(10);
DECLARE @FinCursor AS CURSOR;

DECLARE @ParsingCursor AS CURSOR;
SET @FinCursor=CURSOR FAST_FORWARD FOR SELECT ID,Data 
FROM map.CompaniesLenInfo WHERE State='PA';
OPEN @FinCursor;
FETCH NEXT FROM @FinCursor INTO @ID,@Data;
WHILE @@FETCH_STATUS = 0 
BEGIN


  SET @ParsingCursor = CURSOR FAST_FORWARD FOR SELECT StartPosition,Length,columnnames
  FROM dbo.rawcompanyinfo_delimited;
  OPEN @ParsingCursor;
  FETCH NEXT FROM @ParsingCursor INTO @StartPosition, @Len, @ColumnName;
  WHILE @@FETCH_STATUS = 0 
      BEGIN
           SET @Val = SUBSTRING(@Data,@StartPosition, @Len);
           /* Not sure how to insert into dbo.output*/

          FETCH NEXT FROM @ParsingCursor INTO @StartPosition, @Len, @ColumnName;
      END

  CLOSE @ParsingCursor;
  DEALLOCATE @ParsingCursor;


FETCH NEXT FROM @FinCursor INTO @ID,@Data;

END
CLOSE @FinCursor;
DEALLOCATE @FinCursor;

Как я могу вставить в качестве широкой таблицы. Кто-нибудь может предложить любой другой метод, который может быть быстрее?

Источник

3 ответа

Решение

Вот пример с использованием PIVOT:

CREATE TABLE dbo.rawcompanyinfo_delimited (ID smallint NOT NULL, Data VARCHAR (MAX) NULL, State CHAR (2));

INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(100,'ABCINC    111  333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(200,'APPLE     213  333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(300,'BTEC      100  123.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(400,'S INC     123  333.0 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(500,'B INC     145  123.2 USD','PA')

CREATE TABLE CompaniesLenInfo(Startingposition int not null, Length int not null, columnnames varchar(100) not null)
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(1,10,'CompanyName')
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(11,3,'CompanyID')
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(15,5,'TotalIncome')
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(21,3,'Currency')

SELECT *
FROM (
    SELECT r.ID,r.State,SUBSTRING(r.Data,ci.Startingposition,ci.Length) AS val,ci.columnnames 
    FROM rawcompanyinfo_delimited AS r,CompaniesLenInfo AS ci
) AS t PIVOT(MAX(val) FOR columnnames IN (CompanyName,CompanyID,TotalIncome,Currency) ) p

+ ----- + ------- + ------------- + ----------- + --------- ---- + ---------- +
| ID | Государство | CompanyName | CompanyID | TotalIncome | Валюта |
+-----+-------+-------------+-----------+-------------+----------+
| 100 | PA    | ABCINC      | 111       |  333.       | США |
| 200 | PA | ЯБЛОКО | 213 | 333. | США |
| 300 | PA    | BTEC        | 100       |  123.       | США |
| 400 | PA    | S INC       | 123       |  333.       | США |
| 500 | PA    | B INC       | 145       |  123.       | США |
+ ----- + ------- + ------------- + ----------- + --------- ---- + ---------- +
Источник

Используйте инструмент командной строки bcpПрограмма массового копирования. bcp проанализирует и загрузит ваши данные. Вам нужно будет определить "формат файла", чтобы определить, где ваши столбцы фиксированной ширины начинаются и останавливаются. Если вы можете получить данные, разделенные другим символом (например, ~), тогда легче определить разделители столбцов и разделители строк.

Вот ссылка на документ: https://docs.microsoft.com/en-us/sql/relational-databases/import-export/use-a-format-file-to-bulk-import-data-sql-server

Источник

Нет необходимости использовать курсор. Чтобы загрузить данные из таблицы необработанных данных в выходную таблицу, вы можете использовать один (может быть, немного длиннее, если у вас 500 столбцов) оператор INSERT SELECT:

INSERT INTO dbo.output(ID,CompanyName,CompanyID,TotalIncome,Currency)
SELECT r.ID, 
      (SELECT Substring(r.Data, m.startingposition, m.length)
         FROM map.CompaniesLenInfo AS m
        WHERE m.columnnames = 'CompanyName'),
      ...
      (SELECT Substring(r.Data, m.startingposition, m.length)
         FROM map.CompaniesLenInfo AS m
        WHERE m.columnnames = 'Currency')
  FROM dbo.rawcompanyinfo_delimited AS r
Источник
Другие вопросы по тегам