论坛 › 已解决问题区 › EXCEL的CSV檔案單一儲存格處理(已解決)

yohoboy 发表于 2019-5-12 03:00:38

EXCEL的CSV檔案單一儲存格處理(已解決)

本帖最后由 yohoboy 于 2019-6-1 01:55 编辑

各位大家好:

   今天又來發問順便賺金幣。此次要詢問的是EXCEL 函數處理。
    因有一個檔案為 CSV檔，以 "," 作為分隔。是用來準備匯入SQLITE 資料庫內。
    但是有一個問題，因如果欄位儲存格有多行資料，例如附件檔的CSV檔案中欄位 F4,跟F9 這兩個儲存格
    因有CHR(10),CHR(13) 也就是有 @CRLF不可見字元。
    在匯入SQLITE 時會因為資料不對齊(欄位數不相符)無法匯入，因此參考函數寫法後就用偷雞方式處理。
    資料是可以匯入啦，但是有多行的資料儲存格這欄位資料卻怎麼處理也無法分隔，
    例如F9欄位無法隔離造成連字狀況， 變成6_86_8_16_8_26_8_3想要處理成6_8*6_8_1*6_8_2*6_8_3類似格式的方式
    是否有比較適合的寫法？
    附上原始碼。
#include <FileConstants.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $CSV_Data_Path = FileOpenDialog("選擇檔案", @ScriptDir & "\", "(*.CSV)", $FD_FILEMUSTEXIST + $FD_MULTISELECT)
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $CSV_Data_Path)
Sleep(1000)
;使用EXCEL開啟檔案，發現某欄位儲存格有多行資料例如: F4; F9 等欄位
_Excel_RangeInsert($oWorkbook.Activesheet, "G:G", $xlShiftToRight ) ;插入一欄

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=CLEAN(F1)", "G1") ;寫入清除不可見字元碼像"CRLF" CHR(10) CHR(13)等
_Excel_RangeCopyPaste($oWorkbook.Activesheet, "G1", "G2:G23", Default, $xlPasteFormats) ; 粘贴格式到目标范围範圍大小須定義

Local $sWorkbook = @ScriptDir & "\TEST-DATA-2.CSV"
_Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlCSV, True)

_Excel_BookClose($oWorkbook, True)

Local $CSV_Data_Path = @ScriptDir & "\TEST-DATA-2.CSV"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $CSV_Data_Path)
Local $sRange = "F:F"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除舊欄位不可見字元

chzj589 发表于 2019-5-14 14:53:08

本帖最后由 chzj589 于 2019-5-14 15:21 编辑

yohoboy 发表于 2019-5-13 23:43
感謝回覆，圖上的資料是我最後顯示的結果，只是想要將原本分行的格內資料去除@CRLF後再替換其他符號，也 ...
这样子：
用<字符串管理>函数处理：

For $i = 1 To 26
        ; 读取工作簿中活动工作表指定单元格的数据
        Local $aResult = _Excel_RangeRead($oWorkbook, Default, "F" & $i)
    $iLengthA = StringLen($aResult)
        Local $StripWS = StringStripWS($aResult, 8)
        If $iLengthA > 6 And $iLengthA < 10 Then
                Local $sString = StringMid($StripWS, 1, 3) & "*" & StringMid($StripWS, 4, 5)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $sString, "G4")
        ElseIf $iLengthA > 11 And $iLengthA < 23 Then
                Local $sString = StringMid($StripWS, 1, 3) & "*" & StringMid($StripWS, 4, 5) & "*" & StringMid($StripWS, 9, 5) & "*" & StringMid($StripWS, 14, 5)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $sString, "G9")
        EndIf
Next

yohoboy 发表于 2019-5-12 03:02:06

忘了附上圖片。

chzj589 发表于 2019-5-13 09:21:50

本帖最后由 chzj589 于 2019-5-13 09:23 编辑

yohoboy 发表于 2019-5-12 03:02
忘了附上圖片。


>运行 AU3Check (3.3.14.2) 检查代码.参数: D:\AutoIt3au3 文件: D:\AU3调试\SQLite\SQLite\学生成绩统计\CSV-DATA-INPUT\CSV-DATA-INPUT.au3
+>09:18:53 AU3Check 语法检查结束.rc: 0
>运行:D:\AutoIt3\autoit3_x64.exe(3.3.14.2), 脚本: D:\AutoIt3\autoit3_x64.exe "D:\AU3调试\SQLite\SQLite\学生成绩统计\CSV-DATA-INPUT\CSV-DATA-INPUT.au3"   

yohoboy 发表于 2019-5-13 23:43:46

chzj589 发表于 2019-5-13 09:21
>运行 AU3Check (3.3.14.2) 检查代码.参数: D:\AutoIt3au3 文件: D:\AU3调试\SQLite\SQLite\学生 ...

感謝回覆，圖上的資料是我最後顯示的結果，只是想要將原本分行的格內資料去除@CRLF後再替換其他符號，也就是F9格內最終顯示為6_8*6_8_1*6_8_2*6_8_3，目前可行的做法好像用正則方式去處理，可惜我還沒研究透，所以看看哪個高人可以寫出來。

chzj589 发表于 2019-5-15 11:02:08

chzj589 发表于 2019-5-14 14:53
这样子：
用函数处理：

判断10行以上有BUG，需修改。

chzj589 发表于 2019-5-20 18:50:52

chzj589 发表于 2019-5-15 11:02
判断10行以上有BUG，需修改。

无聊，修改了判断，可对9999行进行判断。

chzj589 发表于 2019-5-20 18:52:47

本帖最后由 chzj589 于 2019-5-20 18:56 编辑

chzj589 发表于 2019-5-15 11:02
判断10行以上有BUG，需修改。
无聊，修改后可对9999行进行判断。

yohoboy 发表于 2019-6-1 01:49:48

本帖最后由 yohoboy 于 2019-6-1 01:52 编辑

最後是採用新增一欄位來處理，比較符合所需，所已附上代碼留作紀念。#include <Array.au3>
#include <FileConstants.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $CSV_Data_Path = FileOpenDialog("選擇檔案", @ScriptDir & "\", "(*.CSV)", $FD_FILEMUSTEXIST + $FD_MULTISELECT)
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $CSV_Data_Path)
Sleep(1000)
Local $sRange = "B:B" ;注意刪除後會遞補所以要邊寫邊測試欄位是否符合
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除SYS
Local $sRange = "F:H"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除報修單位，報修人，維護單位
Local $sRange = "J:M"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除承商處理，派工日期，執行人員，維修人員
Local $sRange = "L:L"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除離場日期
Local $sRange = "N:O"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除里程數，運轉時數
Local $sRange = "Q:Q"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除故障元件
Local $sRange = "Y:Z"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除週轉性商品，轉單日期
Local $sRange = "AB:AB"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除外包總人時
Local $sRange = "AE:AE"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除核準
Local $sRange = "AH:AH"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除RAM
Local $sRange = "AI:AP"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除標註時間，催修時間，斷電時間，斷電人員，是否掛鎖，復電時間，復電人員，斷電副卡已交給承商
Local $sRange = "AJ:AJ"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除轉單理由
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "物料編號", "AJ1") ;增加欄位
_Excel_RangeReplace($oWorkbook, Default, Default, ",", "，") ;查找分隔符號","並替代為"，"注音豆號
_Excel_RangeReplace($oWorkbook, Default, Default, "'", "-") ;查找分隔符號"'"並替代為"_"底線
_Excel_RangeInsert($oWorkbook.Activesheet, "H:H", $xlShiftToRight )
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=CLEAN(G1)", "H1") ;寫入清除不可見字元碼
_Excel_RangeCopyPaste($oWorkbook.Activesheet, "H1", "H2:H200", Default, $xlPasteFormats) ; 粘贴格式到目标范围

_Excel_RangeInsert($oWorkbook.Activesheet, "J:J", $xlShiftToRight )
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=CLEAN(I1)", "J1") ;寫入清除不可見字元碼
_Excel_RangeCopyPaste($oWorkbook.Activesheet, "J1", "J2:J200", Default, $xlPasteFormats) ; 粘贴格式到目标范围

_Excel_RangeInsert($oWorkbook.Activesheet, "M:M", $xlShiftToRight )
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=CLEAN(L1)", "M1") ;寫入清除不可見字元碼
_Excel_RangeCopyPaste($oWorkbook.Activesheet, "M1", "M2:M200", Default, $xlPasteFormats) ; 粘贴格式到目标范围

Local $sWorkbook = @ScriptDir & "\PIDS-CM-INPUT.CSV"
_Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlCSV, True)

_Excel_BookClose($oWorkbook, True)

Local $CSV_Data_Path = @ScriptDir & "\PIDS-CM-INPUT.CSV"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $CSV_Data_Path)
Local $sRange = "G:G"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除舊故障敘述，保留已移除不可見字元欄位
Local $sRange = "H:H"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除舊故障描述補充說明，保留已移除不可見字元欄位
Local $sRange = "J:J"
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftToLeft) ;刪除舊維修處理情形，保留已移除不可見字元欄位

查看完整版本: EXCEL的CSV檔案單一儲存格處理(已解決)