Please help a beginner with data import

stonucca
01-09-2004, 11:43 AM
this is my 1st attempt at VB although I have a little Access exsperience

This is my problem

I have a csv file with 4 colums of data and about 300 lines ish
I want to import it to a database simply by clicking a button

I have found loads of examples but cant understand them as they normally have to many bells and whistles on

the files will be in the same place and go to the same table
not as an update but as a ADD

this is my 1st step
I would like to start off by learning how to manipulate data with forms and sql once it is in the DB

this is just a hobby as Im 72
so any help would be good

You CAN teach an old dog new tricks I just need a start

Thanks in advance

Kev

Bart1123
01-09-2004, 12:51 PM
Hi Kev,

I assume the DB currenlty exists, but if not begin by creating the DB with the Table and Fields you will need.

Then open the .csv file and read it into the DB
If the columns in the csv are Tab delimited then using the Split function should work for you.

you will need to create a connection to the DB also using DAO or ADO.
This example uses ADO. It also assumes that you are using and access db. Credit to Passel for the code to read the csv


Option Explicit
Dim Con As ADODB.Connection
Dim rs As Recordset
Dim sSQL As String
Dim strF As String
Dim strL As String
Dim p As Integer

Public Sub ReadData()

Set Con = New ADODB.Connection
Set rs = New Recordset

Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\myDb.mdb;" & _
"User Id=admin;" & _
"Password="

sSQL = "select * from MyTable"

rs.Open sSQL, Con
rs.AddNew

Open "c:\myfile.csv" For Input As #1
p = 0
Do Until EOF(1)
Line Input #1, strL
strF = Split(strL, vbTab)
p = p + 1
rs!Field1 = strF(0)
rs!Field2 = strF(1)
rs!field3 = strF(2)
rs!field4 = strF(3)
rs.Update
Loop
Close #1
rs.Close
Con.Close
End Sub

as for manipulating the data once it is in the DB search this forum and Google for SQL.

HTH and good luck

Bart

stonucca
01-09-2004, 05:49 PM
I have done everything you have said to the letter

but I get a Compile error:
"User-defined type not defined"
this is on the 1st line - Con As ADOBD.Connection

I have loaded the up to date service pack like I read somewhere
but still no joy

just a couple other little other questions
I was going to use comma as a seporator do I just change the vbTab to a ,

If I want a button to trigger do I just change
"Public Sub ReadData()" to
"Private Sub Command1_Click()"

Thank you for your help it is really nice of you

Kev

Dennis DVR
01-09-2004, 11:41 PM
you will need to add an ADO reference object on your project go to the project menu and select references and select the Microsoft ActievX Data Object 2.x

stonucca
01-10-2004, 06:19 AM
you will need to add an ADO reference object on your project go to the project menu and select references and select the Microsoft ActievX Data Object 2.x

I have resolved that error and selected 2.7 hope that is OK
But I now get another Compile error "Expected array"

**************************************
Option Explicit
Dim Con As ADODB.Connection
Dim rs As Recordset
Dim sSQL As String
Dim strF As String
Dim strL As String
Dim p As Integer
'Public Sub ReadData()
Private Sub Command1_Click()
Set Con = New ADODB.Connection
Set rs = New Recordset
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\GrandChildFund\data.mdb;" & _
"User Id=admin;" & _
"Password="
sSQL = "select * from tblData"
rs.Open sSQL, Con
rs.AddNew

Open "c:\GrandChildFund\datafile.csv" For Input As #1
p = 0
Do Until EOF(1)
Line Input #1, strL
strF = Split(strL, ",")
p = p + 1
rs!FirstName = strF(0)
rs!SurName = strF(1)
rs!PocketMoney = strF(2)
rs!DateGiven = strF(3)
rs.Update
Loop
Close #1
rs.Close
Con.Close

End Sub
**********************************
can anyone see what Im doing wrong

God bless

Kev

Dennis DVR
01-10-2004, 07:46 AM
try this


Dim strF() as String

stonucca
01-10-2004, 08:00 AM
try this


Dim strF() as String


Thanks Duane I now get

Run-time error '3251':
Current Recordset does not support updating. This may be a limitation of the provider,or of the selected locktype.

seems to be coming from "rs.AddNew"

I have search but cant find anything to help
any idea's

Kev

Dennis DVR
01-10-2004, 08:05 AM
before the RS.Open

RS.CursorLocation = adUseClient
RS.LockType = adLockOptimistic
RS.CursorType = adOpenKeySet or adOpenDynamic

don't be confused about the or (adOpenKeySet or adOpenDynamic) you can use either adOpenKeySet or adOpenDynamic i.e

RS.CursorType = adOpenDynamic

does it help you or not do still get the error?

stonucca
01-10-2004, 09:01 AM
1st Thanks for your help

I have even managed to work out how to display a message box to say
that the data entry has finished importing small for some Im sure but very exciting for me

My only problem is that what is being imported is only the last line in the csv file and not the others.

Cant see anything obvious

I have just ordered a book
teach yourself vb but will take a week to get to me

Thanks again

Dennis DVR
01-10-2004, 09:07 AM
1st Thanks for your help

I have even managed to work out how to display a message box to say
that the data entry has finished importing small for some Im sure but very exciting for me

My only problem is that what is being imported is only the last line in the csv file and not the others.

Cant see anything obvious

I have just ordered a book
teach yourself vb but will take a week to get to me

Thanks again

your addnew should be inside the do loop like your update

stonucca
01-10-2004, 09:15 AM
I would like to thank you both for helping
have a really good weekend

God Bless

Kev

Dennis DVR
01-10-2004, 09:19 AM
did it run correctly?

stonucca
01-10-2004, 09:21 AM
You have made an Old man Very Happy

Grifter
01-10-2004, 02:00 PM
Hi Stonucca

I have recently been working on the very same problem.....

Here is my code

Function Import()

On Error GoTo Import_Err

Dim Filename As String
Dim FoundFilesArray() As String
Dim Counter As Integer


'set an initial array bound, that we will modify
ReDim FoundFilesArray(1000)

'bring in all files from a directory that end in .csv and put them in an array
Counter = 0
'This sets the directory to import the files from
ChDir "C:\importfiles\csv"
'this selects all csv files
Filename = Dir$("*.csv")
'this loop puts the found files in an array
Do While myFile <> ""
FoundFilesArray(Counter) = myFile
Filename = Dir$
Counter = Counter + 1
Loop


ReDim Preserve FoundFilesArray(Counter - 1)
For Counter = 0 To UBound(FoundFilesArray)
MsgBox FoundFilesArray(Counter)
'THIS IS THE IMPORTANT LINE OF CODE FOR YOU STONUCCA
'"AttendLog" IS THE NAME OF THE TABLE IN ACCESS
DoCmd.TransferText acImportDelim, "", "AttendLog", FoundFilesArray(Counter), False, ""

Next

Import_Exit:
Exit Function

Import_Err:
MsgBox Error$
Resume Import_Exit

End Function


Giving credit where it is due, MKoslof helped me out with my code.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum