Prev: ADSystemInfo and ComputerName
Next: shell.application throw error on asp file
From: Richard Mueller [MVP] on 22 Sep 2009 20:28

"HL0105" <tron9901(a)msn.com> wrote in message
news:17741e26-bc67-4bf6-a2f9-88307d4e5291(a)g1g2000vbr.googlegroups.com...
On Sep 22, 9:47 am, "Richard Mueller [MVP]" <rlmueller-
nos...(a)ameritech.nospam.net> wrote:
> "HL0105" <tron9...(a)msn.com> wrote in message
>
> news:97f6384c-e7ac-44c2-9b43-64fd17f4aff4(a)m11g2000vbl.googlegroups.com...
> On Sep 21, 5:48 pm, "Richard Mueller [MVP]" <rlmueller-
>
> nos...(a)ameritech.nospam.net> wrote:
> > "Highlander" <tron9...(a)msn.com> wrote in message
> >snip...
>
> Thanks Richard your script does the job! Except for one thing - it's
> changing the value of the second field. Looks to be chopping it off
> after a certain number of decimal points.
>
> This is more clearly illustrated if you change the contents of the CSV
> file to the following, and then run your script:
>
> Field1,Field2,Field3
> Software Program One,1.2.3.456789,12/31/2009
> Software Program Two,2.1.234.56789,12/31/2009
> Software Program Two,
> Software Program Three,3.123.456.789,12/31/2009
> Software Program Four,4.12.34.56.789,12/31/2009
> Software Program Five,5.1234567.89,9,12/31/2009
> Software Program Five,,12/31/2009
> Software Program Six,6.123456.78.9,12/31/2009
>
> - Dave
> -----------
>
> The driver assumes the value is numeric. The solution is to create a
> schema.ini file in the folder with the csv file. I used the following
> schema.ini file:
> ===========
> [Example.csv]
> Format=CSVDelimited
>
> Col1="Field1" Text
> Col2="Field2" Text
> Col3="Field3" Text
> ========
> The first line is the name of the file in square brackets. This file told
> the driver that all fields are text. There are many other things you can
> do
> in such a file. I found this link:
>
> http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx
>
> The driver knows to magically look for the schema.ini file and use it if
> the
> specified file name matches.
>
> --
> Richard Mueller
> MVP Directory Services
> Hilltop Lab -http://www.rlmueller.net
> --

The schema.ini file solved the problem. Thanks Richard!

Two more questions.

1. I'm using this code within an HTA, running it from a mapped network
drive. So I keep getting the message "This website uses a data
provider that may be unsafe. If you trust the website, click OK,
otherwise click Cancel."
Is there a way to prevent this annoying message?

2. I actually have the data in an array; I'm writing the array to a
CSV file in order to use your code. Is it possible to use this ADO
code to parse the array directly, instead of parsing the CSV file?

Thanks again!

- Dave
--------------

I don't know of any way to convert an array into a recordset, except to
enumerate all elements and add them to the recordset. I wonder how the data
go into an array. It must have been read from an original source into the
array. Perhaps it should be read into a disconnected recordset instead of an
array. Or perhaps the data can be copied from the array into a disconnected
recordset. The disconnected recordset can be sorted, then enumerated as
before and output as desired.

In the example below the hard part is populating the array. It would be good
if that step could be skipped. Note below that the date is formated as a
date, the other two fields as strings. You can also format numbers.
===========
Option Explicit
Dim arrValues(7, 2), j, adoDataList, strLine, strFlag, strPrevious

Const adVarChar = 200
Const adDBTimeStamp = 135
Const MaxCharacters = 255

' Populate the array.
arrValues(0, 0) = "Software Program One"
arrValues(0, 1) = "1.2.3.456789"
arrValues(0, 2) = #12/31/2009#
arrValues(1, 0) = "Software Program Two"
arrValues(1, 1) = "2.1.234.56789"
arrValues(1, 2) = #12/31/2009#
arrValues(2, 0) = "Software Program Two"
arrValues(3, 0) = "Software Program Three"
arrValues(3, 1) = "3.123.456.789"
arrValues(3, 2) = #12/31/2009#
arrValues(4, 0) = "Software Program Four"
arrValues(4, 1) = "4.12.34.56.789"
arrValues(4, 2) = #12/31/2009#
arrValues(5, 0) = "Software Program Five"
arrValues(5, 1) = "5.1234567.89"
arrValues(5, 2) = #12/31/2009#
arrValues(6, 0) = "Software Program Five"
arrValues(6, 2) = #12/31/2009#
arrValues(7, 0) = "Software Program Six"
arrValues(7, 1) = "6.123456.78.9"
arrValues(7, 2) = #12/31/2009#

' Setup disconnected recordset. Define fields.
Set adoDataList = CreateObject("ADODB.Recordset")
adoDataList.Fields.Append "Field1", adVarChar, MaxCharacters
adoDataList.Fields.Append "Field2", adVarChar, MaxCharacters
adoDataList.Fields.Append "Field3", adDBTimeStamp, MaxCharacters
adoDataList.Open

' Read the array into the disconnected recordset.
For j = 0 To UBound(arrValues, 1)
adoDataList.AddNew
adoDataList("Field1") = arrValues(j, 0)
adoDataList("Field2") = arrValues(j, 1)
adoDataList("Field3") = arrValues(j, 2)
adoDataList.Update
Next

' Sort the disconnected recordset.
adoDataList.Sort = "Field1,Field2,Field3"

' Display sorted values.
adoDataList.MoveFirst
strPrevious = ""
Do Until adoDataList.EOF
strFlag = adoDataList.Fields("Field1").Value
' Output one line for each unique value of Field1.
If (strPrevious <> "") And (strPrevious <> strFlag) Then
Wscript.Echo strLine
End If
' Only the first field will be enclosed in quotes.
strLine = """" & strFlag & """," _
& adoDataList.Fields.Item("Field2").Value _
& "," & adoDataList.Fields.Item("Field3").Value
strPrevious = strFlag
adoDataList.MoveNext
Loop
Wscript.Echo strLine

adoDataList.Close
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--


First  |  Prev  | 
Pages: 1 2
Prev: ADSystemInfo and ComputerName
Next: shell.application throw error on asp file