Loading xml data in excel vba by using XML Object(MSXML2.DOMDocument)

### in excel file ( xlsm , macro excel , enable macro) 

' Declare xml object

  set xmlObj = CreateObject("MSXML2.DOMDocument.6.0")

 ' set loading option with non async

 xmlObj.Async = False

' validate off

xmlObj.validateOnParse = False

' reading file from sample.xml 

xmlObj.Load "sample.xml"

' select all "mynode" from root node recursively ( ref. xml path grammar in detail)

' root node에서 mynode 모두 선택하기 추가적인 부분은 xml path 문법을 참고

Set nodeList = xmlObj.SelectNodes("//mynode")

for index to nodeList.Length -1 

    set myNode = nodeList(index)

    ' do somthing 

    aa = myNode.text

next index

import csv into mysql by load data (skip some column)

It's very general , well known topic.but when we need time to find and use it.

I blogged it to remember. 

load data infile 'aa.csv' into table tbl_name

character set 'UTF8'

fields terminated by ','  optionally enclosed by '"' escaped by '\\'

lines terminated by '\r\n'  starting by ''

ignore 1 lines

 (col1, col2, col3) 
;

case 2: when number of column of table and csv data
  1- when you want drop some column of csv.  ( drop v4, v5)
     ex) csv contents : v1, v2, v3, v4, v5

 load data infile 'aa.csv' into table tbl_name

character set 'UTF8'

fields terminated by ','  optionally enclosed by '"' escaped by '\\'

lines terminated by '\r\n'  starting by ''

ignore 1 lines

 (col1, col2, col3, @dummy1, @dummy2)   -- @dummy just read and drop

How to get list sheetlist from excel file.

' It's not difficult but useful.  Especially when there are many sheets in a excel file.


Sub GetAllList()

Dim all As Sheets

Set all = Application.ActiveWorkbook.Sheets

countOfSheets = all.Count   ' count of sheet

For i = 1 To countOfSheets
    ' sheet1 must be added already. 
    ' insert sheet name into row
    Application.Sheets.Item("Sheet1").Cells(i, 1) = all.Item(i).Name
Next i

End Sub