October 09, 2024, 03:42:10 PM

News:

IonicWind Snippit Manager 2.xx Released!  Install it on a memory stick and take it with you!  With or without IWBasic!


Connecting to a CSV file

Started by Jim Scott, December 29, 2006, 01:37:09 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Jim Scott

I'm just getting started with Ebasic.  Loving what I'm seeing so far.  I could use some help though.

I'm trying to connect to a small CSV file that contains the following;

Client Type,Last Name,First Name,Site Loc.,Member#,CheckIn,CheckOut,Unit #
Member,ADAMS,WILLIAM,53,296252920,26-Dec-2006,1-Jan-2007,41
Member,AMES,ALBION,33,200068864,14-Dec-2006,28-Dec-2006,65

So, it is a little database with a header and two records.
I'm trying to connect using the following Ebasic code;

OPENCONSOLE

pdb = dbConnect("Microsoft Text Driver (*.txt; *.csv)",GetStartPath + "SmallFile.csv","")
IF pdb <> NULL
    PRINT "Connection Established"
    dbDisconnect(pdb)
ENDIF
PRINT "Press any key to exit"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
END

And I can't get it to print "Connection Established".  What am I'm doing wrong here?

Thanks for any help.
Jim Scott

Ionic Wind Support Team

For ODBC to connect to a CSV file you have to also create a schema.ini file that tells the text driver what format the text file is in.  The schema file is a simple text file that exists in the same directory as the CSV file.

As a guess your scheme.ini would look like this:


[SmallFile.csv]
ColNameHeader=True
CharacterSet=ANSI
Format=CSVDelimited
Col1=ClientType Char Width 25
Col2=LastName Char Width 25
Col3=FirstName Char Width 25
Col3=SiteLoc Integer
Col4=MemberNo Interger
Col5=CheckIn Date
Col6=CheckOut Date
Col7=UnitNo Integer


Note that it has been a few years since I've written a schema file by hand, so you may have to tweek it to make it work with your .csv file.  Here is more information from Microsoft:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
Ionic Wind Support Team

Jim Scott

Geeze Paul, do you ever sleep?  Thanks for your prompt attention and great products.

I followed the link you provided and came up with the following Schema.ini file to define my database;

[SmallFile.csv]
ColNameHeader=True
CharacterSet=ANSI
Format=CSVDelimited
Col1=ClientType Char Width 25
Col2=LastName Char Width 25
Col3=FirstName Char Width 25
Col4=SiteLoc Integer
Col5=MemberNo Integer
Col6=CheckIn Date
Col7=CheckOut Date
Col8=UnitNo Integer


It still doesn't see the file.  So, as a sanity check, I run the following code;

OPENCONSOLE
DEF myfile:FILE
DEF ln:STRING
IF(OPENFILE(myfile,GetStartPath + "SmallFile.csv","R") = 0)
   IF(READ(myfile,ln) = 0)
            PRINT ln
   ENDIF
   CLOSEFILE myfile
   PRINT "File read successfully"
ELSE
   PRINT "File could not be opened"
ENDIF
PRINT "Press Any Key To Close"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
END


And this prints the header row to the console.  Any other ideas?  Is there a better way to approach this?

Thanks again
Jim Scott

Ionic Wind Support Team

You can get error messages using dbGetErrorString.   Your connection string may be off too.  I seem to remember having to specify security attributes and extensions like so:


pdb = dbConnect("Microsoft Text Driver (*.txt; *.csv)",GetStartPath + "SmallFile.csv","Extensions=asc,csv,tab,txt; Persist Security Info=False")

Ionic Wind Support Team

Brian

Hi,

This works for me in IB Pro (which is not a million miles away from EBasic!)

pdb=dbConnect("Microsoft Text Driver (*.txt; *.csv)",GETSTARTPATH,"")

Brian

Ionic Wind Support Team

Intersting.  So it looks for any CSV file in the directory then?  Do you use a schema file with it brian?
Ionic Wind Support Team

Ionic Wind Support Team

OK Brian is correct.  You just specify the directory the CSV files are in.  Each CSV file is treated as a separate table in the database.
Ionic Wind Support Team

Jim Scott

Thanks for your help gentlemen.  ok, now I'm a little confused.  I used Microsoft's Data Source Administrator found in Control Panel - Administrative Tools - Data Sorces (ODBC) to create a connection to the data in "SmallFile.csv".  It put the file named schema.ini in my StartPath directory and it looked exactely like the one I list below.

I'll relist it here for exactness.  Sorry if this is getting run into the ground...

[smallfile.csv]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI
Col1=CLIENT_TYPE Char Width 255
Col2=LAST_NAME Char Width 255
Col3=FIRST_NAME Char Width 255
Col4=SITE_LOC Char Width 255
Col5=MEMBER_NUM Char Width 255
Col6=CHECKIN Date
Col7=CHECKOUT Date
Col8=UNIT_NUM Char Width 255


I've tried both forms of dbConnect in the following code to no avail.  Any more ideas?

Def pdb as Pointer
OPENCONSOLE
Print "Looking in " + GetStartPath
'pdb = dbConnect("Microsoft Text Driver (*.txt; *.csv)",GETSTARTPATH + "SmallFile.csv","")
pdb = dbConnect("Microsoft Access Driver (*.mdb)",GETSTARTPATH,"")
Print "pdb Value Returned = ", pdb

IF pdb <> NULL
    PRINT "Connection Established"
    dbDisconnect(pdb)
Else
Print "Connection Not Established"
ENDIF

PRINT "Press any key to exit"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
END


Jim Scott

LarryMc

You didn't change your program properly and follow the previous instructions:

This works just fine on my computer when I put the csv file in the same dir as the exe.
(The dbConnect statement is what Brian said do AND that Paul said was correct.)
And I didn't use (or need) a schema.ini file
Def pdb as Pointer
OPENCONSOLE
Print "Looking in " + GetStartPath

pdb=dbConnect("Microsoft Text Driver (*.txt; *.csv)",GETSTARTPATH,"")

Print "pdb Value Returned = ", pdb

IF pdb <> NULL
    PRINT "Connection Established"
    dbDisconnect(pdb)
Else
Print "Connection Not Established"
ENDIF

PRINT "Press any key to exit"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
END


Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Jim Scott

Sorry, you're right.  Thanks.  My <ctrl>c<ctrl>v bad.  A little too much new information in my ageing mind and too little sleep makes Johnny (me) stupid.  I'm learning a new compiler (I just bought it) and alot of the dbase stuff is new to me as well.

Thanks for your patience.  Now to figure out how to get at the data in the files....

I'll beg your pardon in advance and try to keep the stupid questions to a minimum.

You folks have been great.
Jim Scott

Ionic Wind Support Team

No such thing as a stupid question.  We have all learned from your experience ;)
Ionic Wind Support Team

LarryMc

Jim,

I sure didn't look at it as a "stupid" question and didn't mean for my response to sound that way if it did.

It hasn't been that long since I was trying to get my head around the ODBC stuff.

If it hadn't been for me going through the same exact pains you are I wouldn't have been able to even respond to your question.

Throw your questions out here and someone will ALWAYS give you help (and an awful lot of the time it is the man himself that's offers up the answers).  That's what makes this forum so great.

Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

DominiqueB

December 30, 2006, 07:34:34 AM #12 Last Edit: December 30, 2006, 11:02:18 AM by DominiqueB
Hello,
trying to replicate the prog, i've got a litle prob:
the first record is always ignored ?

*****************************************************
code:

'http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
Def pdb as Pointer
Def hstmt as int
def error as string

def CLIENT_TYPE as string
def LAST_NAME as string
def FIRST_NAME as string
def SITE_LOC as string
def MEMBER_NUM as string
def CHECKIN as DBDATE
def CHECKOUT as DBDATE
def UNIT_NUM as string

OPENCONSOLE
Print "Looking in " + GetStartPath

pdb=dbConnect("Microsoft Text Driver (*.txt; *.csv)",GETSTARTPATH,"")

Print "pdb Value Returned = ", pdb

IF pdb <> NULL
    PRINT "Connection Established"

   hstmt = dbExecSQL(pdb,"SELECT * FROM SmallFile.csv")
   error = dbGetErrorCode(hstmt)
   IF LEN(error)
       PRINT
       PRINT "Error Code: ", error
       PRINT "Error Text: ", dbGetErrorText(hstmt)
       PRINT
   ENDIF
   
   IF hStmt
                  dbBindVariable(hstmt,1,CLIENT_TYPE)
           dbBindVariable(hstmt,2,LAST_NAME)
           dbBindVariable(hstmt,3,FIRST_NAME)
           dbBindVariable(hstmt,4,SITE_LOC)
           dbBindVariable(hstmt,5,MEMBER_NUM)
           dbBindDate(hstmt,6,CHECKIN)
           dbBindDate(hstmt,7,CHECKOUT)
           dbBindVariable(hstmt,8,UNIT_NUM)

       WHILE dbGetNext(hstmt)
         print
           PRINT CLIENT_TYPE, ",", LAST_NAME, " ", FIRST_NAME, ",", SITE_LOC
         print MEMBER_NUM
         print "CHECKIN : ", USING("## ## ####",CHECKIN.day,CHECKIN.month,CHECKIN.year)
         print "CHECKOUT : ", USING("## ## ####",CHECKOUT.day,CHECKOUT.month,CHECKOUT.year)
         print UNIT_NUM
           PRINT
       ENDWHILE
   dbFreeSQL(hstmt)
   ENDIF
   
    dbDisconnect(pdb)
Else
   Print "Connection Not Established"
ENDIF

PRINT "Press any key to exit"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
END

******************************************************
SmallFile.csv :

Client Type,Last Name,First Name,Site Loc,Member#,CheckIn,CheckOut,Unit #
Member   AMES   ALBION   33   200068864   14-12-2006   28-12-2006   65
Member   PAUL   QUIERST   22   305634213   22-08-2006   01-01-2009   38
Member   ADAMS   WILLIAM   53   296252920   09-12-2006   10-12-2007   41

********************************************************
Schema.ini :

[smallfile.csv]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
CharacterSet=ANSI
Col1=CLIENT_TYPE Char Width 255
Col2=LAST_NAME Char Width 255
Col3=FIRST_NAME Char Width 255
Col4=SITE_LOC Char Width 255
Col5=MEMBER_NUM Char Width 255
Col6=CHECKIN Date
Col7=CHECKOUT Date
Col8=UNIT_NUM Char Width 255

***************************************************************

Could one tell me why ?

Dominique

Vikki

December 30, 2006, 10:06:22 AM #13 Last Edit: December 30, 2006, 10:17:13 AM by Vikki
Hi Dominique,

For some reason, the csv file needs a blank line after the column headings and before the first record. Not sure why. But that works here.

Also, you only need to specify the first field in the csv file for it to print the rows completely.

DominiqueB

OK,
with a blank line between the column headings and before the first record, i get all the records.
But the question remains: why do i get the first print with empty result ?

DominiqueB

OK, i've got it !

No need of a blank line, just to do:
the binding of fields and vars must take place before the first dbGetNext(hstmt) !

Determined by reading the help file . . .    ;)

Thank's for the help.
Code above updated accordingly to the statement !

Dominique

Vikki

 ::) Read the help file? hrmmm, what a concept. I should have tried that myself before answering.  ;)

Glad you got it solved!  ;D

Jim Scott

December 30, 2006, 07:26:24 PM #17 Last Edit: January 01, 2007, 08:01:20 AM by Jim Scott
Thanks Paul, Brian, Larry, Dominique, and Vikki. I've learned a bunch in the last few days and am very grateful for the help.  You've shaved months off my learning curve.

I'm going to start a new thread with some cleaned-up code showing what is working so far and one thing that is not working so well.

(oops fixed spelling, Jim)
Jim Scott

Bruce Peaslee

Quote from: Ionic Wind Support Team on December 29, 2006, 02:58:21 AM
You can get error messages using dbGetErrorString. 

I know this thread is old, but this is what I am looking for. If my user tries to open a bitmap file as an Access database, I would like to say more than just "Failed to connect".

dbGetErrorString is not a function and dbGetErrorText requires a SQL handle.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles