Jump to content
Sign in to follow this  
vvb

[Solved] Issue with ADODB opening CSV file

Recommended Posts

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 by vvb

I'll be AutoIting my entire system soon.

Share this post


Link to post
Share on other sites
water

Can you please provide the complete script?

$objConn is missing.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by vvb

I'll be AutoIting my entire system soon.

Share this post


Link to post
Share on other sites
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.


I'll be AutoIting my entire system soon.

Share this post


Link to post
Share on other sites
GreenCan

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

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


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites
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


I'll be AutoIting my entire system soon.

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.


I'll be AutoIting my entire system soon.

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
GreenCan

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


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×