Import CSV File Without Looping

[ryanf]

Is it possible to import a comma separated text file without using similar code to this:

Do While Not EOF(1)
Line Input #1, s
Input #1 strPRODUCT
With rsDGMain
.AddNew
.Fields("PRODUCT") = strPRODUCT
.Update
End With
Loop

[Ecniv]

Not unless you use DAO/Jet Docmd.TransferText.


Vince

[ryanf]

Unfortunately (or fortunately ) I am using ADO - is there no way to import directly using ADO?

Please could you post your solution for the DAO/Jet Docmd.TransferText way?

Thanks in advance
Ryan

[Ecniv]

Um,

The DAO/Jet (can't remember which) way is essentially just that.

Docmd.TransferText acImport,"Filename"..... and other options

ADO - not that I know of.


I found the looping one was best - how come you don't want to use it ?

Dim strIncoming as string, strFilename as string
 
'---- set filename first
'---- open recordset too
 
Close #1
Open strfilename For Input As #1
Do until EOF(1)
  line input #1, strIncoming
  rst.Addnew
  rst("field")=strincoming
  rst.update
Loop
rst.close
Close #1

Vince

[ryanf]

I found the looping one was best - how come you don't want to use it ?

I was hoping for a faster way to import because my files (two of them in the DB) are updated every week and there are about 90000 records with 9 fields so it takes a while to import them using that way.

It also kills my PC in that its uses all available RAM - is there a way to limit the amount of system resources your app uses in VB?

Other than that looping works great.

[Ecniv]

This may seem silly but why arey ou updating 90k records each week from a text file?

Wouldn't it be easier to link to the source of the text?

Perhaps looking at the process you have to follow at the moment and changing it might be better.


Vince

[ryanf]

It's not silly at all (the suggestion that is) - I just found it to more efficient (faster) than updating the DB (the way I was doing it anyway which was searching the entire DB for the unique identifier, then updating and looping/repeating the process)

How could you link to the source of the text? I didn't understand what you meant there?

There must be a faster way than I am doing it now - I just can't think of anything and haven't heard of anything yet

[Ecniv]

(1)
It's not silly at all (the suggestion that is) - I just found it to more efficient (faster) than updating the DB (the way I was doing it anyway which was searching the entire DB for the unique identifier, then updating and looping/repeating the process)

(2)
How could you link to the source of the text? I didn't understand what you meant there?

There must be a faster way than I am doing it now - I just can't think of anything and haven't heard of anything yet

1) So you were looping through the records and pulling a specific field to store where and for what purpose?
Now you export all the options then reimport them all....?

2)I mean where you get the text file from - the source of the data in the text file



Yeah there should be a faster way - it really depends on why you are getting the field and putting it somewhere.

If its used as a lokup for data of a certain type I think that the db design might need looking at, as you are storing the same text data more than once... when you could use a look up table for this data and figure the best way of allowing users to add to it. Just an idea. Might have no relevance to the situation.


What I am asking is what purpose will the list of the field serve being separated?

Vince

[ryanf]

The app is for browsing certain codes specific to the healthcare industry which identify certain medicines/materials. If a user types in the name of a medicine they will retrieve its code.

My DB is made up of two tables - one which contains all medicines and another that contains all materials (bandages etc). In my app they are browsed as two separate recordsets.

So to answer 1:
Would be to update product information or the above mentioned codes etc.

and 2:
At the moment the client will be sent the latest files, the app will clear the DB with "DELETE * From tblMedicine" and then import from the new text file.

I think my DB is OK in that there is no reduntant data or fields.

The data the clients get from us is not very easy for them to get or maintain, that's why we sell it.

Thanks for the help!

[Ecniv]

Surely the codes aren't goign to change much, so storing them once, and have a status field to whether they are active/discontinued?

Just an idea...



Also you 'could' put the lot in one table if you have a field of type:

Example:

tblMainData
ID - Auto
Code - Text - 10
CategoryID - Number
Description - memo
Status - Yes/No (default Yes)
....Other fields that you need per item....

tblLUCategories
CategoryID - Auto
Category - Text - 30

This would make searching a little longer perhaps - but 90k records should still be pretty fast.


Vince

[ryanf]

You are right - the codes don't change much so I'll give your suggestions some thought (not today though-I've given my brain the day off).

Thanks for your help/advice Vince - much appreciated!

Cheers
Ryan

[ryanf]

You are right - the codes don't change much so I'll give your suggestions some thought (not today though-I've given my brain the day off).

Thanks for your help/advice Vince - much appreciated!

Cheers

Ryan

[ryanf]

You are right - the codes don't change much so I'll give your suggestions some thought (not today though-I've given my brain the day off).

Thanks for your help/advice Vince - much appreciated!

Cheers

Ryan

[ryanf]

You are right - the codes don't change much so I'll give your suggestions some thought (not today though-I've given my brain the day off).

Thanks for your help/advice Vince - much appreciated!

Cheers

Ryan