Converting CSV file into an Access Database

Keywords: 	   Converting CSV file into an Access Database  csv2acc

Question:

How can I create one big CSV into an Access database, that can easily be opened, queried and manipulated in any VB app?

Answer:

I used to do this all the time at work and this worked fine on my PC.

I created an Access Database called c:\data\access\LogFile.mdb

then I created a table with the following structure:

Structure for table: Log Table

Field Name Field Type Field Length
ComputerName Text 255
IP_Number Text 255
UserName Text 255
WS_UpdateServer Text 255
PluginExecuted Text 255
TimeStamp Text 255
Status Text 255
ErrorMessage Text 255









importfile = "c:\z\junk.txt"

ih = fileopen(importfile, "read")

gosub startAccess

rs = db.openrecordset("Log Table")
cname = rs.fields("ComputerName")
ipval = rs.fields("IP_Number")
uname = rs.fields("UserName")
wsupd = rs.fields("WS_UpdateServer")
plugx = rs.fields("PluginExecuted")
tstamp= rs.fields("TimeStamp")
status= rs.fields("Status")
emsg  = rs.fields("ErrorMessage")

while @true
   input = fileread(ih)
   if input == "*EOF*" then break
   ;   ignore the header line and import the data...
   if strsub(input, 1, 12) <> "ComputerName"
      rs.addnew
      cname.value = itemextract(1, input, ",")
      ipval.value = itemextract(2, input, ",")
      uname.value = itemextract(3, input, ",")
      wsupd.value = itemextract(4, input, ",")
      plugx.value = itemextract(5, input, ",")
      tstamp.value = itemextract(6, input, ",")
      status.value = itemextract(7, input, ",")
      emsg.value = itemextract(8, input, ",")
      rs.update
    endif
endwhile

fileclose(ih)

objectclose(rs)
objectclose(db)
objectclose(ADB)
objectclose(accessDB)

message("Debug", "All Done")

exit



:startAccess
accessDB = objectopen("Access.Application")
ADB = accessDB.Application
dbname = "C:\Data\Access\LogFile.mdb"
ADB.OpenCurrentDatabase(dbname)
ADB.Visible = @false
ADB.UserControl = @false
db = accessDB.currentdb
return




Article ID:   W15598

File Created: 2003:05:28:10:21:52
Last Updated: 2003:05:28:10:21:52

Database Search

TechHome

Converting CSV file into an Access Database

Question:

Answer:

Structure for table: Log Table

Field Name	Field Type	Field Length
ComputerName	Text	255
IP_Number	Text	255
UserName	Text	255
WS_UpdateServer	Text	255
PluginExecuted	Text	255
TimeStamp	Text	255
Status	Text	255
ErrorMessage	Text	255