Utility to data import

[jshanoo]

Hi all,
I have more than 200 dbf files.
I tried DTS but in date field it is failing.

so next option available is to make a utility in vb to import all these into sql database.
i want code some thing like pick each dbf and insert into sql.

Kindly please guide me in these.

regards
John


*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[bastienk]

can you export to csv or tab delimited files...do that and use your db load utils to load each table


Bastien

Cat, the other other white meat

 

[jshanoo]

Hi,
I cannot do with that, bocoz, the application has will executed by laymen, since DTS is not helpful, i have to go with my own application.

What i am looking for....................
a dsn with visual foxpro will be created and it has to read all the dbf files and check all the fields of insert the record to corresponding table in SQL Database.

Regards
John Philip



*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[vb5prgrmr]

To create a dsn you can search this site for that information. It has been answered a couple of times.

As for your dbf files and their sql relation is there any way to tell? Like ARDATA.dbf will update to tblARData?

Then for the dbf's themselves are there any "memo" fields?

What data access method do you want to use for the dbf's, dao, ado, rdo?

For SQL, rdo, ado?

And there are many more questions to be asked because of the lack of info given but these are just a start.

If you really need help coding this application you may want to goto other sites like rentacoder.com and farm it out if you have to but with better information we should be able to help you here.

Good Luck

 

[vb5prgrmr]

jshanoo, any luck?

Good Luck

 

[jshanoo]

Hi Vb5,
Well I am thru with the application only couple of hinderance which work in query analyser but fail with the application
eg below query will work in query analyser
Insert into clasinfo (contract,class,area,priority) values ( '01','99912 ',' ',' ' )

but not within the vb
ConnSQL.Execute strSQLInsVal
' strSQLInsVal is the variable which stores the sql stmt

i dont find any logical reason why its happening like this.
error which i receive is

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string '99912'.

Can any one please help me in this


*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[bastienk]

is the full string enclosed in double or single quotes...should be doubles


Bastien

Cat, the other other white meat

 

[vb5prgrmr]

Should be...
[tt]
strSQLInsVal = "Insert into clasinfo (contract, class, area, priority) values ( '01','99912 ',' ',' ')"
[/tt]

That is if all fields are of varchar/char if any of those fields are numeric in nature then you need to leave the single quotes out.

Good Luck

 

[jshanoo]

Hi , i found out the problem

strSQLInsVal = "Insert into clasinfo (contract, class, area, priority) values ( '01','99912 ',' ',' ')"


in the above string after '99912 ', there i san ascii character after 2.

so it can be inserted only if i prefix and siffix " (double quotes )to the string.

How u please tell me how to do this.
regrads
John


*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[vb5prgrmr]

[tt]
Dim DblQuote As String
DblQuote = """"
[/tt]
yes that is 4 of those "

then concatinate as normal...
[tt]
... & DblQuote & "'99912 '" & DblQuote ...
[/tt]

Good Luck

 

[jshanoo]

Hi
oops may be i have not given clear pictures.

I said prefixing " to the sql string not the value alone.

*** Even the Best, did the Bad and Made the Best ***

John Philip