help me get data from text file to access

akohl posted this at 17:28 — 13th March 2002.

Joined: Feb 2001

Hi.
I am trying to put into my access database values from a text file
where fields are deliminated with double quotes.
I managed to extract the values from each line.
The problem is assigning them to a
db field.

The following code, with an actual number works:

rs1.fields(5)=strMid

but the following gets an error message

rs1.fields(i)=strMid

Error Type:
Provider (0x80020005)
Type mismatch.

What exactly does this error message mean?

Does it mean that there is a mismatch between the value
and the data type defined in the db table? I seem to
remember a different error message for this problem.
But if that's the problem, how can I tell which
db field is causing the trouble so I can do and
change it's data definitions?

dim i
i=1
do while objTextS.line<=5  ''objTextS.atEndOfStream<>true
	strLine= objTextS.readLine
	startDelPos=1
	endDelPos=1
	endofString=false
	rs1.addnew
	do until endofString=true
		startDelPos=instr(startDelPos,strLine,delchar)
		delNum=delNum+1
		endDelPos=startDelPos+1
		endDelPos=instr(endDelPos,strline,delchar)
		strMid=mid(strLine,startDelPos+1,(endDelPos-startDelPos)-1)

		'set the rs with sql that defines the field in the
		'db in the order that they appear in the text data file
		'so that by forwarding the field number by one each time 
		'the text data file fields line up with the db fields.
		''''''''''''''''''''''''''''''''''''''''''''''''''''''''
		''this condition ensures that the deliminated fields
		''in the text data file which are not to be entered
		''into the db are passed over for assigment to db fields.
		if delNum>12 or delNum<5 then
			if delNum=20 then
				rs1.fields(i)=pubId
				'don't advance the deliminator number since this is the last one.
			else
				rs1.fields(i)=strMid
				i=i+1
			end if
			end if
			startDelPos=endDelPos+1
			if startDelPos>=len(strLine) then
				endofString=true
			end if
			num=num+1
	loop ''end of line
	rs1.update
	delNum=0
	i=1
loop''till end of file
rs1.Close
set rs1=nothing

Andy Kohlenberg
Jerusalem, Israel

Peter J. Boettcher posted this at 21:16 — 13th March 2002.

They have: 812 posts

Joined: Feb 2000

A type mismatch usually means you're doing something wrong with data, either doing calculations on strings, trying to save a string into an integer, stuff like that.

If you want to see where you're going wrong just add a Response.Write strMid or something. If you're strictly just trying to import the data why don't you use the import wizard in Access?

PJ | Are we there yet?
pjboettcher.com

akohl posted this at 02:36 — 14th March 2002.

They have: 117 posts

Joined: Feb 2001

I wasn't aware of the import wizzard.

I just took a look now but had trouble with it. It couldn't seem to work out how the fields were deliminated. So I clicked "advanced" in order to tell it that the fields were deliminated with spaces and strings were qualified with double quotes. The access crashed out and said I had one or more fields in that data source that was too big to import.
When working properly would that wizzard give me controll over the proccess so that I could skip certain fields that I don't want to import, ect..?

Thanks for your suggestion. I hope I can get it to work.
As far as my script is concerned I guess your saying that I just have to review all 20 some db field assignments carefully and that this is the only way to find out which one is causing the trouble, right?

Andy

Andy Kohlenberg
Jerusalem, Israel

akohl posted this at 03:35 — 14th March 2002.

They have: 117 posts

Joined: Feb 2001

I tried again. It said that i had a line of data with over 65,000 characters. Its hard to believe, but possible I suppose. Will I have the same problem when I try to import the data with my asp script?

Curiously, it didn't say that one field was too big to fit into a memo field, It said that the line was too big. I previoulsly understood that I could have three memo fields of 30,000 characters each in the same table. So what gives here?

Andy Kohlenberg
Jerusalem, Israel

Peter J. Boettcher posted this at 13:34 — 14th March 2002.

They have: 812 posts

Joined: Feb 2000

That's the problem with Access, it has many limitations. I think you might be able to get around the 64K by importing through your ASP page but I'm not sure. I'm not sure what the limit is on how many memo fields you can have in a table but I would think each one would have the same 64K limit.

How big is this text file? Access has actual file size limits, something like 1GB or 2GB.

If you have access to a SQL Server then maybe you should try importing the text file there using the tools built into Enterprise Manager. Then maybe it will be easier to export it from there to Access.

PJ | Are we there yet?
pjboettcher.com

akohl posted this at 14:06 — 15th March 2002.

They have: 117 posts

Joined: Feb 2001

The file is 1.1 mb

No I don't have ms sql server. But I have a feeling that I'll be going in that direction eventually. I didn't think I would have to do so even before the project goes on line.

Tell me- If I continue to devlope the site with access will it be easy to convert it to sql server later? Will I have to change the sql on my asp pages?

Andy

Andy Kohlenberg
Jerusalem, Israel

Peter J. Boettcher posted this at 17:44 — 15th March 2002.

They have: 812 posts

Joined: Feb 2000

You shouldn't have to modify anything except your connection string, but you'll probably want to convert all those queries to stored procedures for efficiency.

There might be some minor problems since SQL handles it's datatype a little different, but they should be minimal.

PJ | Are we there yet?
pjboettcher.com

akohl posted this at 11:40 — 18th March 2002.

They have: 117 posts

Joined: Feb 2001

Is it possible to use ms sql server on the webhosting server without having it installed on my own computer? Would would a webhost be able or willing to convert my access db to ms sql server so that I could do all the development on my own computer with access but have the website run on sql server?

Andy Kohlenberg
Jerusalem, Israel

Peter J. Boettcher posted this at 14:05 — 18th March 2002.

They have: 812 posts

Joined: Feb 2000

You don't have to have SQL Server installed on your machine. If your webhost provides a public IP on the SQL Server machine you can use it for development. So on your development machine the connection would point to the SQL Server.

Once the database has been created you can use Enterprise Manager to manage it.

PJ | Are we there yet?
pjboettcher.com

akohl posted this at 15:47 — 20th March 2002.

They have: 117 posts

Joined: Feb 2001

Well, it seems that I'll be getting sql server soon.
So I'm going to leave the problem with the access import wizzard unsolved.
The question now is, should I continue to work on the data import asp script that I started this thread with? Once I get the simple version above to work, I'm going to develop it further so that it will be able to use it to import from one text file into two tables, since some of the data in the file belongs to another table in my db.

Will I be able to do all this with the db program itself and am I therefore wasting my time with this script?

Andy Kohlenberg
Jerusalem, Israel

Peter J. Boettcher posted this at 16:22 — 20th March 2002.

They have: 812 posts

Joined: Feb 2000

Before you spend too much time with your ASP script I would test importing data into SQL Server using Enterprise Manager. It's not guaranteed to work but it is more robust and flexible than the import wizard in Access.

PJ | Are we there yet?
pjboettcher.com