T-SQLを使用してCSVファイルをインポートし、列に分割する方法

SQL Server 2008でCSVファイルを列に分割する方法

CSVファイルは、カンマ区切りでデータを格納する一般的なファイル形式です。SQL Server 2008では、T-SQLを使用してCSVファイルをインポートし、各フィールドを個別の列に分割することができます。これにより、CSVファイルのデータを構造化されたテーブルに格納し、分析や処理を容易にすることができます。

方法

CSVファイルを列に分割するには、以下の手順を実行します。

1. CSVファイルをテキストファイルとしてインポートする

BULK INSERT MyTable
FROM 'C:\path\to\mycsvfile.csv'
WITH (
    DATAFILETYPE = 'csv',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

上記の例では、MyTableという名前のテーブルにCSVファイルをインポートします。FIRSTROWオプションは、ヘッダー行をスキップする行数を指定します。FIELDTERMINATORオプションは、フィールドを区切る文字を指定します。ROWTERMINATORオプションは、行を区切る文字列を指定します。

1. 各フィールドを個別の列に分割する

SELECT
    SUBSTRING(field1, 1, CHARINDEX(',', field1) - 1) AS column1,
    SUBSTRING(field2, CHARINDEX(',', field1) + 2, CHARINDEX(',', field2) - 1) AS column2,
    ...
    SUBSTRING(fieldn, CHARINDEX(',', fieldn - 1) + 2, LEN(fieldn)) AS columnn
FROM MyTable

上記の例では、MyTableテーブルの各フィールドを個別の列に分割します。SUBSTRING関数を使用して、各フィールドの部分文字列を抽出します。CHARINDEX関数を使用して、カンマの位置を特定します。

補足

• 上記の方法は、カンマ区切りのCSVファイルにのみ適用されます。
• フィールドの長さが異なる場合、SUBSTRING関数は予期しない結果になる可能性があります。各フィールドの最大長さを考慮した方法で分割する必要があります。
• より複雑なCSVファイルの場合、PIVOT表関数を使用して分割することができます。

上記以外にも、SQL Server 2008でCSVファイルを列に分割する方法があります。ニーズに合った方法を選択してください。

CSVファイル:

CustomerName,Age,City
Alice,30,Seattle
Bob,25,New York
Charlie,40,Chicago

T-SQLコード:

-- CSVファイルをテキストファイルとしてインポートする
BULK INSERT Customers
FROM 'C:\path\to\mycsvfile.csv'
WITH (
    DATAFILETYPE = 'csv',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

-- 各フィールドを個別の列に分割する
SELECT
    CustomerName,
    Age,
    City
FROM Customers

出力:

CustomerName  Age City
-----------  --- --------
Alice         30 Seattle
Bob           25 New York
Charlie       40 Chicago

説明:

• 最初のBULK INSERTステートメントは、Customersという名前のテーブルにCSVファイルをインポートします。
• FIRSTROWオプションは、ヘッダー行をスキップするように設定されています。
• FIELDTERMINATORオプションは、フィールドを区切る文字をカンマに設定しています。
• 2番目のSELECTステートメントは、Customersテーブルの各フィールドを個別の列に分割します。

• このコードは、SQL Server 2008で動作します。

SQL Server 2008でCSVファイルを列に分割するその他の方法

SELECT
    *
FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=C:\path\to\mycsvfile.csv;HDR=YES',
    'SELECT * FROM [Sheet1$]'
) AS ExcelData

上記の例では、OPENROWSET関数を使用して、Excelドライバーを介してCSVファイルにアクセスします。HDR=YESオプションは、ヘッダー行を処理するように設定します。

STRING_SPLIT関数を使用する

DECLARE @csv NVARCHAR(MAX);

-- CSVファイルの内容を文字列変数に格納する
SELECT @csv = N'CustomerName,Age,City
Alice,30,Seattle
Bob,25,New York
Charlie,40,Chicago'

-- STRING_SPLIT関数を使用して各行を分割する
SELECT
    value
FROM STRING_SPLIT(@csv, ',');

-- 各フィールドを個別の列に分割する
WITH temp AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY x) AS row_num,
        value
    FROM STRING_SPLIT(@csv, ',')
)
SELECT
    MIN(value) AS column1,
    SUBSTRING(value, CHARINDEX(',', value) + 2, LEN(value) - CHARINDEX(',', value) - 1) AS column2
FROM temp
GROUP BY row_num
ORDER BY row_num;

上記の例では、STRING_SPLIT関数を使用してCSVファイルを文字列の配列に分割します。その後、ROW_NUMBERウィンドウ関数を使用して各行に番号を付け、SUBSTRING関数を使用して各フィールドを個別に抽出します。

XMLデータ型を使用する

DECLARE @xml XML;

-- CSVファイルの内容をXMLデータ型に変換する
SELECT @xml = CONVERT(XML, (
    SELECT *
    FROM OPENROWSET (
        'Microsoft.ACE.OLEDB.12.0',
        'Excel 12.0 Xml;Database=C:\path\to\mycsvfile.csv;HDR=YES',
        'SELECT * FROM [Sheet1$]'
    ) AS ExcelData
));

-- XMLデータをXPathを使用して処理する
SELECT
    x.value('local-name(.)') AS column_name,
    x.value('text()') AS column_value
FROM @xml.nodes('/Row') AS r
CROSS APPLY r.nodes('*') AS x
ORDER BY r.@Index;

上記の例では、CONVERT関数を使用してCSVファイルの内容をXMLデータ型に変換します。その後、XPathを使用してXMLデータを処理し、各フィールドを個別に抽出します。

各方法の比較

最適な方法を選択する

使用するCSVファイルの形式、処理するデータ量、パフォーマンス要件などに応じて、最適な方法を選択する必要があります。