Program Page: データベースとしてのCSVファイルの新規作成

カテゴリー名：　［ADOによるデータベースの新規作成］

2016/08/16

《このページの目次》

1.　JScript
- △　create_csv.js
- △　create_csv02.js
2.　VBScript
- △　create_csv.vbs
- △　create_csv02.vbs

1.　JScript

△　create_csv.js

// CSVをデータベース用として作成。ADOを利用
  // データの書き込みにはレコードセットを利用
var csvFile, HeaderYN, TypeStr;
var fso, csvPath, csvFolder, csvTable, schemaPath;
var ShellObj, DriverStr, ConnStr, CN, RS, sql;

csvFile = "test.csv";
HeaderYN = "Yes";
TypeStr = "氏名 varchar(20),身長 float";

fso = WScript.CreateObject("Scripting.FileSystemObject");
csvPath = fso.GetAbsolutePathName(csvFile);
csvFolder = fso.GetParentFolderName(csvPath);
if (fso.FolderExists(csvFolder) == false) {
    try { fso.CreateFolder(csvFolder); }
    catch(e) {
        WScript.Echo(csvFolder + "が存在しません!");
        WScript.Quit();
    }
}
csvTable = fso.GetFileName(csvPath);
schemaPath = csvFolder + "\schema.ini";
if (fso.FileExists(csvPath))  fso.DeleteFile(csvPath);
if (fso.FileExists(schemaPath))  fso.DeleteFile(schemaPath);
fso = null;

ShellObj = WScript.CreateObject("Wscript.Shell");
if (ShellObj.Environment("Process").Item(
        "PROCESSOR_ARCHITECTURE") == "x86") {  // 32bit版の場合
    DriverStr = "Provider=Microsoft.Jet.OLEDB.4.0;";
} else {  // 64bit版の場合
    DriverStr = "Provider=Microsoft.ACE.OLEDB.12.0;";
}
ShellObj = null;

ConnStr = DriverStr + "Data Source=" + csvFolder + ";" +
    "Extended Properties=\"text;HDR=" + HeaderYN + ";FMT=Delimited;\";";
CN = WScript.CreateObject("ADODB.Connection");
CN.Open(ConnStr);
sql = "create table " + csvTable + " (" + TypeStr + ");";
CN.Execute(sql);  // テーブル作成

RS = WScript.CreateObject("ADODB.Recordset");
sql = "select * from " + csvTable + ";";
RS.Open(sql,CN,0,2,1);
RS.AddNew();
    RS.Fields("氏名").Value = "鈴木csv";
    RS.Fields("身長").Value = 172.3;
RS.Update();
RS.AddNew();
    RS.Fields(0).Value = "高橋csv";
    RS.Fields(1).Value = 168.5;
RS.Update();
RS.AddNew(["氏名", "身長"], ["田中csv", 183.6]);
RS.Update();
RS.Close();
CN.Close();
RS = null;
CN = null;

目次に戻る

△　create_csv02.js

// CSVをデータベース用として作成。ADOを利用
  // データの書き込みにはSQL命令のinsertを利用
var csvFile, HeaderYN, TypeStr;
var fso, csvPath, csvFolder, csvTable, schemaPath;
var ShellObj, DriverStr, ConnStr, CN, sql;

csvFile = "test.csv";
HeaderYN = "Yes";
TypeStr = "氏名 varchar(20),身長 float";

fso = WScript.CreateObject("Scripting.FileSystemObject");
csvPath = fso.GetAbsolutePathName(csvFile);
csvFolder = fso.GetParentFolderName(csvPath);
if (fso.FolderExists(csvFolder) == false) {
    try { fso.CreateFolder(csvFolder); }
    catch(e) {
        WScript.Echo(csvFolder + "が存在しません!");
        WScript.Quit();
    }
}
csvTable = fso.GetFileName(csvPath);
schemaPath = csvFolder + "\schema.ini";
if (fso.FileExists(csvPath))  fso.DeleteFile(csvPath);
if (fso.FileExists(schemaPath))  fso.DeleteFile(schemaPath);
fso = null;

ShellObj = WScript.CreateObject("Wscript.Shell");
if (ShellObj.Environment("Process").Item(
        "PROCESSOR_ARCHITECTURE") == "x86") {  // 32bit版の場合
    DriverStr = "Provider=Microsoft.Jet.OLEDB.4.0;";
} else {  // 64bit版の場合
    DriverStr = "Provider=Microsoft.ACE.OLEDB.12.0;";
}
ShellObj = null;

ConnStr = DriverStr + "Data Source=" + csvFolder + ";" +
    "Extended Properties=\"text;HDR=" + HeaderYN + ";FMT=Delimited;\";";
CN = WScript.CreateObject("ADODB.Connection");
CN.Open(ConnStr);
sql = "create table " + csvTable + " (" + TypeStr + ");";
CN.Execute(sql);  // テーブル作成

sql = "insert into test.csv (氏名,身長)  values('鈴木csv', 172.3);";
CN.Execute(sql);
sql = "insert into test.csv (氏名,身長)  values('高橋csv', 168.5);";
CN.Execute(sql);
sql = "insert into test.csv (氏名,身長)  values('田中csv', 183.6);";
CN.Execute(sql);

CN.Close();
CN = null;

目次に戻る

2.　VBScript

△　create_csv.vbs

' CSVをデータベース用として作成。ADOを利用
    ' データの書き込みにはレコードセットを利用
Option Explicit
Dim CsvFile, HeaderYN, TypeStr
Dim FSO, CsvPath, CsvFolder, CsvTable, SchemaPath
Dim ShellObj, DriverStr, ConnStr, CN, RS, sql

CsvFile = "test.csv"
HeaderYN = "Yes"
TypeStr = "氏名 varchar(20),身長 float"

Set FSO = CreateObject("Scripting.FileSystemObject")
CsvPath = FSO.GetAbsolutePathName(CsvFile)
CsvFolder = FSO.GetParentFolderName(CsvPath)
If FSO.FolderExists(CsvFolder) = False Then
    On Error Resume Next
    FSO.CreateFolder(CsvFolder)
    If FSO.FolderExists(CsvFolder) = False Then
        WScript.Echo CsvFolder & "が存在しません!"
        WScript.Quit
    End If
End If
CsvTable = FSO.GetFileName(CsvPath)
SchemaPath = CsvFolder & "\schema.ini"
If (FSO.FileExists(CsvPath) = True) Then FSO.DeleteFile(CsvPath)
If (FSO.FileExists(SchemaPath) = True) Then FSO.DeleteFile(SchemaPath)
Set FSO = Nothing

Set ShellObj = CreateObject("Wscript.Shell")
If ShellObj.Environment("Process").Item( _
        "PROCESSOR_ARCHITECTURE") = "x86" Then  ' 32bit版の場合
    DriverStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
Else  ' 64bit版の場合
    DriverStr = "Provider=Microsoft.ACE.OLEDB.12.0;"
End If
Set ShellObj = Nothing

ConnStr = DriverStr & "Data Source=" & CsvFolder & ";" & _
    "Extended Properties=""text;HDR=" & HeaderYN & ";FMT=Delimited;"";"
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnStr
sql = "create table " & CsvTable & " (" & TypeStr & ");"
CN.Execute(sql)  ' テーブル作成

Set RS = CreateObject("ADODB.Recordset")
sql = "select * from " & CsvTable & ";"
RS.Open sql,CN,0,2,1
RS.AddNew
    RS.Fields("氏名").Value = "鈴木csv"
    RS.Fields("身長").Value = 172.3
RS.Update
RS.AddNew
    RS.Fields(0).Value = "高橋csv"
    RS.Fields(1).Value = 168.5
RS.Update
RS.AddNew Array("氏名", "身長"), Array("田中csv", 183.6)
RS.Update
RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing

目次に戻る

△　create_csv02.vbs

' CSVをデータベース用として作成。ADOを利用
    ' データの書き込みにはSQL命令のinsertを利用
Option Explicit
Dim CsvFile, HeaderYN, TypeStr
Dim FSO, CsvPath, CsvFolder, CsvTable, SchemaPath
Dim ShellObj, DriverStr, ConnStr, CN, sql

CsvFile = "test.csv"
HeaderYN = "Yes"
TypeStr = "氏名 varchar(20),身長 float"

Set FSO = CreateObject("Scripting.FileSystemObject")
CsvPath = FSO.GetAbsolutePathName(CsvFile)
CsvFolder = FSO.GetParentFolderName(CsvPath)
If FSO.FolderExists(CsvFolder) = False Then
    On Error Resume Next
    FSO.CreateFolder(CsvFolder)
    If FSO.FolderExists(CsvFolder) = False Then
        WScript.Echo CsvFolder & "が存在しません!"
        WScript.Quit
    End If
End If
CsvTable = FSO.GetFileName(CsvPath)
SchemaPath = CsvFolder & "\schema.ini"
If (FSO.FileExists(CsvPath) = True) Then FSO.DeleteFile(CsvPath)
If (FSO.FileExists(SchemaPath) = True) Then FSO.DeleteFile(SchemaPath)
Set FSO = Nothing

Set ShellObj = CreateObject("Wscript.Shell")
If ShellObj.Environment("Process").Item( _
        "PROCESSOR_ARCHITECTURE") = "x86" Then  ' 32bit版の場合
    DriverStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
Else  ' 64bit版の場合
    DriverStr = "Provider=Microsoft.ACE.OLEDB.12.0;"
End If
Set ShellObj = Nothing

ConnStr = DriverStr & "Data Source=" & CsvFolder & ";" & _
    "Extended Properties=""text;HDR=" & HeaderYN & ";FMT=Delimited;"";"
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnStr
sql = "create table " & CsvTable & " (" & TypeStr & ");"
CN.Execute(sql)  ' テーブル作成

sql = "insert into test.csv (氏名,身長)  values('鈴木csv', 172.3);"
CN.Execute(sql)
sql = "insert into test.csv (氏名,身長)  values('高橋csv', 168.5);"
CN.Execute(sql)
sql = "insert into test.csv (氏名,身長)  values('田中csv', 183.6);"
CN.Execute(sql)

CN.Close
Set CN = Nothing

～　以上　～

Program Page: データベースとしてのCSVファイルの新規作成

《このページの目次》

1. JScript

△ create_csv.js

△ create_csv02.js

2. VBScript

△ create_csv.vbs

△ create_csv02.vbs

1.　JScript

△　create_csv.js

△　create_csv02.js

2.　VBScript

△　create_csv.vbs

△　create_csv02.vbs