chemin's blog

'作者:鄧哲閔 聽說是咖啡與網路資訊安全實驗室4381
'E-mail:b9410104@cat.hfu.eud.tw
'程式目的：CSV轉SQL
'更新歷程：
'2008/01/22　1.修正前次版本空白部分造成的寫入錯誤
'　　　　　　　2.新增可設定欲抓取數量的變數抓取

'EX.****************************************************************
'sample.txt 的內容
'
'1 ,2,3,4,5,444
'1,2,3,4,5,aaaaaaa
'1,2,3,4 ,5,ugtiog
'1,2,3,4,5,gyfi
'1,2,3,4, 5,gyuf


'如果建立table名稱為new table的話，sample2.txt 內容為
'
'INSERT INTO `new table` VALUES('1','2','3','4','5','444');
'INSERT INTO `new table` VALUES('1','2','3','4','5','aaaaaaa');
'INSERT INTO `new table` VALUES('1','2','3','4','5','ugtiog');
'INSERT INTO `new table` VALUES('1','2','3','4','5','gyfi');
'INSERT INTO `new table` VALUES('1','2','3','4','5','gyuf');

'*******************************************************************

Dim ForReading, ForWriting, ForAppending
Dim TristateTrue, TristateFalse, TristateUseDefault
Dim intTableName, intValue, strTest


'*******************************************************************************
'如果你拿到這支程式，只是想轉格式，其他都搞不清楚的話
'只要改這個格子裡的變數就可以了
strFile1 = "sample.txt" '來源檔
strFile2 = "sample2.txt" '目地檔
strTableName = "new table" '設定我們要的資料表名稱
intValue = 6 '指定我們所要抓取value數量的多少
'*******************************************************************************

intValue = intValue -1

'Opens a file for reading only
ForReading = 1
'Opens a file for writing. If the file already exists, the contents are overwritten.
ForWriting = 2
'Opens a file and starts writing at the end (appends). Contents are not overwritten.
ForAppending = 8

'Opens the file as Unicode
TristateTrue = -1
'Opens the file as ASCII
TristateFalse = 0
'Use default system setting
TristateUseDefault = -2

Set ff = CreateObject("Scripting.FileSystemObject")
Set fs = CreateObject("Scripting.FileSystemObject")

'設定來源檔
Set f = fs.OpenTextFile(strFile1, ForReading, False, TristateUseDefault)

'設定目地檔
Set r = ff.OpenTextFile(strFile2, ForAppending, True, TristateUseDefault)

'如果還未到檔案結尾，持續執行程式
Do While f.AtEndOfStream <> True

'從來源檔中讀取一行
strLine = f.readline

'利用","，作分割並寫入array
arrItem = split(strLine,",")

'抓取設定的value數量並且去掉字串多餘的空白
arrReplace = Trim(arrItem(0))
For intTemp=1 To intValue
arrItem(intTemp) = Trim(arrItem(intTemp))
arrReplace = arrReplace & "," & arrItem(intTemp)
Next

'利用","，再次作分割並寫入array
arrValue = split(arrReplace,",")

'代換成SQL的格式
strReplace = "INSERT INTO" & " `" & strTableName & "` " & "VALUES" & "('"
strReplace = strReplace & join(arrValue,"','")
strReplace = strReplace & "');"

'把代換好的字串寫入目地檔
r.WriteLine(strReplace)
Loop

'--close file object
f.Close
r.Close