Using ADO to Read from CSV to automate Project

[Akeel Ahmed]

Hi Guys,

I have a csv file that conatins the following:

Case Number,Subject,Project Predecessors
3808,Data Migration:,
4011,Production,Campaigns and opportunities,3808
4012,Campaigns and opportunities,4011-3808

I have a macro in project that uses ADO to load up the csv file : the code
is as follows:

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\"

objConnection.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strPathtoTextFile & ";" & _
"Extensions=asc,csv,tab,txt;"

objRecordset.Open "SELECT * FROM [IO Project Report.csv]", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

I then loop through the records in the recordset and create tasks, assign
resources and set up dependencies.

The issue I am facing is that for the third line item in the CSV where we
have the predessor column with a value of 4011-3808, the ADO recordset does
not return any value (its fine for the previous line item) - I would expect
to see the value 4011-3808 but its empty, I am completely baffled. i have
tried to move the lineitems around but everytime I have a dual keyed item
(i.e. nt a single number like 3808) the recordset returns empty. I am using
a standard statement to retriev the data from the recordset (which wirks fine
for all other items).

Has anyone any idea what I may be doing wrong or what I may not be setting?
I will keep hunting and if I come across a solutiuon I will be sure to post
it up (ps I have tried using a different connection:


objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=CSVDelimited"""

but still no joy.

Thanks in advance for your help.

Akeel

 

[Akeel Ahmed]

Hi Guys,

I found a fix, well a fudge so a proper solution would be awesome. I
htought it would be worthwhile sharing it.

From what I can see ADO will determine the datatypes for the recordset based
upon the first column from the CSV so if you place a dummy column in which is
text based and then not include it in the loop scripts that works. Nasty so
the csv looks like this:


Case Number,Subject,Project Predecessors
Case Number,Subject,Project Predecessors
3808,Data Migration:,
4011,Production,Campaigns and opportunities,3808
4012,Campaigns and opportunities,4011-3808

Notice the repeat of the header column - sometimes as developers the
commercial need requires us to provide solutions which are not technically
perfect - do you think if I keep telling myself that I will feel better - I
am going to keep looking for a better solution as this is a temporary
workaround.

Akeel

 

[Dave]

personally, i would recommend a change to your method. using ado to access
a text file like that leaves you at the mercy of it's default conversions
and all sorts of other stuff. i would guess that ado is guessing that the
last column is a number and so thinks that something is malformed in the
4011-3808 string and gives up. if you really want to use ado see if you can
find a way to tell it to only return strings instead of trying to make
numeric fields or something like that. you might be able to tell what its
doing in the debugger if you look at the recordset after the select
statement and examing each of the field types and properties.

my preference for jobs like this is the use my own code to read the csv file
a line at a time, use 'split' to break the lines apart and then do error
checking and put them into a database or spreadsheet as needed. this gives
you much more direct control over how conversions are done and how the data
is stored.

Hi Guys,

I have a csv file that conatins the following:

Case Number,Subject,Project Predecessors
3808,Data Migration:,
4011,Production,Campaigns and opportunities,3808
4012,Campaigns and opportunities,4011-3808

I have a macro in project that uses ADO to load up the csv file : the code
is as follows:

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\"

objConnection.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strPathtoTextFile & ";" & _
"Extensions=asc,csv,tab,txt;"

objRecordset.Open "SELECT * FROM [IO Project Report.csv]", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

I then loop through the records in the recordset and create tasks, assign
resources and set up dependencies.

The issue I am facing is that for the third line item in the CSV where we
have the predessor column with a value of 4011-3808, the ADO recordset
does
not return any value (its fine for the previous line item) - I would
expect
to see the value 4011-3808 but its empty, I am completely baffled. i have
tried to move the lineitems around but everytime I have a dual keyed item
(i.e. nt a single number like 3808) the recordset returns empty. I am
using
a standard statement to retriev the data from the recordset (which wirks
fine
for all other items).

Has anyone any idea what I may be doing wrong or what I may not be
setting?
I will keep hunting and if I come across a solutiuon I will be sure to
post
it up (ps I have tried using a different connection:


objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=CSVDelimited"""

but still no joy.

Thanks in advance for your help.

Akeel

 

[Akeel Ahmed]

Hi Dave,

Thanks for your reply. I have pondered your reasoning and I think you are
absolutely right - it would be better for me to use the filesystemobject or
something similar to read the file and then use the split function to
differentiate the columns. I cant quite remember why I wanted to use ADO but
I think it was to learn something different - having said that I am
absolutely absorbed by this problem (i just posted a fudge) and can see a
sleepless night ahead - in the end I think regardless of the solution I find
I will probably go back to using FSO as you suggested.

Many Thanks,

Akeel