This script reads records from a CSV file of customer names and addresses. The first record of the file is the header (field names); the fields could be in any order, and sometimes have different variations on the field names.
I import the header into a one-dimensional array of field names, and use this loop to assign a variable to each column number:
' Read the first line from the file. TextLine = MyFile.ReadLine ' Split the line into an array arrCustomers = Split(TextLine, ",") For i = 0 To UBound(arrCustomers, 1) Select Case LCase(arrCustomers(i)) case "customer name", "custname", "name" CustName = i case "customer number", "custno","custnum" CustNo = i case "address", "street" Address = i case "city" City = i case "country" Country = i case "state" State = i case else ' list of unused fields s = s & ", " & arrCustomers(i) End Select Next msgbox "These fields are unused: " & s
For instance, if "Customer Name" is in field 3, then
CustName = 3 and
arrCustomers(CustName) is the Customer Name field of the current record.
This loop processes each record:
Do While MyFile.AtEndOfStream <> True ' Read the first line from the file. TextLine = MyFile.ReadLine 'Split the line into an array arrCustomers = Split(TextLine, ",") 'Process the current record msgbox "Our customer " & arrCustomers(CustName) & " lives in " & arrCustomers(City) & ", " & arrCustomers(State) Loop
My main problem is that the Case statements in the first loop must include every possible value for the field names. If the address field is called "Address1" or something else I didn't forsee, the
Address variable becomes 0, and
arrCustomers(Address) becomes the same as whatever's in the first column.
I suspect that there's a well-known algorithm somewhere for loading an array when the fields could be different each time – one that's better than my method. If not, maybe there's a way to map the "unused fields" on the fly?