[Solved] Issue with ADODB opening CSV file

[vvb]

Hi All,

I have been trying to use ADODB to open a CSV file (only coma separation, no double quotes around each field).

I use the following connection string

$connStr = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' & $csvPath & _
                 ';Extended Properties="Text;HDR=No;FMT=Delimited(,);IMEX=1";'

$objConn.Open($connStr)
$objRS.Open("Select * From " & $csvFile, $objConn, $AD_OPEN_STATIC, $AD_LOCK_OPTIMISTIC, $AD_CMD_TEXT)
$hearderRow = $objRS.GetRows(1)

However the header row does not contain all the columns and using _ArrayDisplay. Columns 0, 3, 4, 5, 6, 7, 8, 9 and 12 are always empty.

However, If I get 2 rows, all the data are present but the header is still missing some columns.

Also, if I say to skip the header row, then there is no data loss.

The following is a sample of the file that produces the error:

TermNumber,DNSName,DNSDomain,DNSClientIP,SrvClientIP,SrvClientMask,SrvIP,SrvMask,SiteID,SiteNumber,SiteEC,DNSCltName,ClientIP,SitePassword
1,myServer,site1.xxx.yyy.zz,10.10.10.10,10.10.10.11,255.255.255.255,10.10.11.11,255.255.255.255,1,2,883242,myClient,10.10.10.12,MyPassword

I thought there must be something wrong with CSV file. I then loaded it into Excel 2003 and saved as .XLS. Using the appropriate connection strings etc, all the header columns are present.

I then created a dummy CSV file as follows:

AAA,BBB,CCC,DD,EEEE,FF,54y,6y75,et3,f34
123,345,dfgsd,56g,dfgh,53gs,t34g,rf23,sd5t,f34
fgd,45,fgd,6y,r3g,34,f23,as4,t34,f4

And when running with the above code, The BBB header is always empty.

I then created another dummy CSV file as follows:

AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL,MMM
111,222,333,444,555,666,777,888,999,000,111,222,333
222,333,444,555,666,777,888,999,000,111,222,333,444

Now, all the header columns are empty.

Anyone know what is going on?

BTW, this was tested on a computer without Office installed and a computer with Office 2010 installed with the same effect.

Cheers,

Vlad

Edited May 9, 2013 by vvb

[water]

Can you please provide the complete script?

$objConn is missing.

[vvb]

Hi Water,

$objCon is:

$objCon = ObjCreate("ADODB.Connection")

I have solved it by creating a schema.ini file with the following:

[MyInfo.csv]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=0
Col1=RecordType Char Width 200
Col2=RecordType Char Width 200
Col3=RecordType Char Width 200
Col4=RecordType Char Width 200
Col5=RecordType Char Width 200
Col6=RecordType Char Width 200
Col7=RecordType Char Width 200
Col8=RecordType Char Width 200
Col9=RecordType Char Width 200
Col10=RecordType Char Width 200
Col11=RecordType Char Width 200
Col12=RecordType Char Width 200
Col13=RecordType Char Width 200
Col14=RecordType Char Width 200

Hmm, it seems that if I want to have headers then I need a schema? Can anyone confirm this?

Also, it seems without a schema that it can't handle alphabets as with the following CSV file:

123,2345,56,567,567,345,76,354,76,345,57,68,546,567
1,myServer,site1.xxx.yyy.zz,10.10.10.10,10.10.10.11,255.255.255.255,10.10.11.11,255.255.255.255,1,2,883242,myClient,10.10.10.12,MyPassword

where the first row is a header and without a schema file, I get all the header entries but not all the second row is represented.

Cheers,

Vlad

Edit: I did not realise that when I said previously that data was not lost, I did not see that it could not interpret proper IP addresses so 192.168.3.4 became 192.1683

Edited May 3, 2013 by vvb

[vvb]

I've modified the schema to just have the following and it works:

[TBS_Installation_Info.csv]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=1

So that it treats every entry as text.

[GreenCan]

May I suggest to use a DSN-less connection like this:

expandcollapse popup

Local $search = InputBox("Lookup", "Give part of a name to look up", "")
; Put your own path here:
Local $sPath_to_database = "G:\GreenCan's Documents\programming\AutoIt\Programs\SQL text delimited files"
; DSN-less connection
Local $connection = "DRIVER={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & $sPath_to_database & ";Extensions=asc,csv,tab,txt;"
ConsoleWrite($connection & @CR)
Local $out = FireSQL($connection, $search)
ConsoleWrite($out & @CR)
Exit

Func FireSQL($connection, $search)
Local $sResult = "", $adoCon, $adoRs, $adoSQL
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open($connection)
If @error Then Exit MsgBox(48, "Error", "error " & @error)
$adoRs = ObjCreate("ADODB.Recordset")
If StringLen($search) > 0 Then
$adoSQL = "SELECT * FROM Countries.csv where instr(`English_Country_Name`,'" & $search & "')>0"
Else
$adoSQL = "SELECT * FROM Countries.csv"
EndIf

;~ MsgBox(1, "SQL", $adoSQL)

$adoRs.CursorType = 2
$adoRs.LockType = 3
$adoRs.Open($adoSQL, $adoCon)

With $adoRs
If .RecordCount Then
While Not .EOF
$sResult = $sResult & "" & .Fields("Continent").Value & "|" & .Fields("alpha_2").Value & "|" & .Fields("English_Country_Name").Value & @CR
.MoveNext
WEnd
EndIf
EndWith

$adoCon.Close
Return $sResult
EndFunc ;==>FireSQL

GreenCan

[vvb]

Hi GreenCa,

It tried it out with cursor type of 3 (static) instead and you are right that it does not need the schema.ini file. However, I can't seem to get the header row. It there a way to have the header row and not use the schema.ini file?

Cheers,

Vlad

[water]

In your first example you had "HDR=No". Could you try with "HDR=Yes"?

[vvb]

Hi Water, no go, the IP address etc are not rendered properly. So I am stuck with having the need for a schema which I have read since that it is a must for text based files.

[water]

Thanks for the feedback. I will add some info about schemas in my ADO tutorial.

[GreenCan]

  On 5/6/2013 at 1:11 AM, 'vvb said:

Hi GreenCa,

It tried it out with cursor type of 3 (static) instead and you are right that it does not need the schema.ini file. However, I can't seem to get the header row. It there a way to have the header row and not use the schema.ini file?

Cheers,

Vlad

Hi Vlad,

Sorry, I missed this one...

You are correct, you will need a schema indeed. A DSN-Less connection won't solve this.

GreenCan