creating a CSV file from Access database table using VB6

i have an access file which contain 5 tables , each of these 5 tables contain a number of columns
what am trying to do is , to store the tables of these access files as CSV file using VB6 ( Visual Basic 6)

but am stuck since i didnt get used to use VB6 previously

any suggestions

Hi,

Bear with me, when I'm at my work, I have code that does just that. Can't recall it from memory though...

Grtz.©

M.

Thanks alot for your help

well, the Lecturer said that i didnt understand the task

i will explain to you ,

we have about 20 strings and we want to create a CSV file

but we want to create as the following

FirstString, 50 space , SecondString, 50 space, THird String, 30 space and so one

The problem is i dont know how to write to a file by doin this

Hi,

The following code makes opens up a database (put your path there) and then uses a button "cmdExport" to export table names to a CSV file with 50 spaces between the field, spaces(50), field thingie. Don't know what weird requirement you got but it does just that.



Enjoy.

Grtz.©

M.

Option Explicit

Dim strClaimRecord, strDataField, strClaimID, strNextTable As String
Dim intCharPos, intFieldLength As Integer '// keep track of where we are in the data file
Dim intTemp As Integer
Dim dateDataField As Date
Dim strDataFileName, strTextFileName As String
Dim dataFileLocation As String
Dim myAppID As String
Dim dblPauseUntil As Double
Dim fs, f, ts, fs1, f1 '// for file system handling

'// these next vars are for capturing a bit of claim info to print out in the event of a problem
Dim driverForename, driverSurname, otherForename, otherSurname, addressStreet, addressTown, accidentDate As String



Private Sub Form_Load()

processData.Caption = "Loosemores : Process RAC data"
'// show a list of the current data files
'Dir1.Path = "U:\rac data imports\data files\"

' //file locations
dataRacIncoming.DatabaseName = "k:\db\rac incoming.mdb"
dataFileLocation = "K:\"

'// initialise directory and file boxes
'// Dir1 is for the dat files
Dir1.Path = dataFileLocation
File1.Path = Dir1.Path
File1.Pattern = "*.dat"
Label2.Caption = File1.ListCount & " files to process"
process.Caption = "process data"

'//Dir2 is for the txt files
Dir2.Path = dataFileLocation
File2.Path = Dir2.Path
File2.Pattern = "*.txt"
Label3.Caption = File2.ListCount & " files to print"

'// highlight 1st entry in each list
If File1.ListCount > 0 Then
File1.ListIndex = 0
Else
MsgBox "No data files to be processed"
End If

If File2.ListCount > 0 Then
File2.ListIndex = 0
End If

End Sub


Private Sub Dir1_Change()
File1.Path = Dir1.Path
End Sub


Private Sub Dir2_Change()
File2.Path = Dir2.Path
End Sub



'// procedure to print out txt files

Private Sub btnPrint_Click()

Dim x, y As Integer

Set fs1 = CreateObject("Scripting.FileSystemObject")

If File2.ListIndex > -1 Then '// only work if there's a file highlighted

Set f1 = fs1.GetFile(dataFileLocation & File2.FileName)

strTextFileName = dataFileLocation & File2.FileName

'// open notepad, print the txt doc and close
myAppID = Shell("C:\Windows\notepad.exe " & strTextFileName, 1) '// open

SendKeys "%FP", True '// print

dblPauseUntil = CDbl(Now) + (1 / 100000) '// a one second delay to wait for app to print
Do Until CDbl(Now) > dblPauseUntil
DoEvents
Loop

SendKeys "%fx", True '// and close

f1.Delete '// delete the txt file
File2.Refresh
Label3.Caption = File2.ListCount & " files to print"

If File2.ListCount > 0 Then '// reselect the file at head of list
File2.ListIndex = 0
End If

End If


End Sub


Sub readDataFile()
'Dim fs, f, ts '// for file system handling
Const ForReading = 1, ForWriting = 2, ForAppending = 3

Set fs = CreateObject("Scripting.FileSystemObject")

If File1.ListIndex > -1 Then '// only work if there's a file highlighted

'Set f = fs.GetFile("U:\rac data imports\data files\" & File1.FileName)
Set f = fs.GetFile(dataFileLocation & File1.FileName)

'strDataFileName = "U:\rac data imports\data files\" & File1.FileName
strDataFileName = dataFileLocation & File1.FileName

Set ts = f.OpenAsTextStream(ForReading)

'// store contents of datafile on variable
strClaimRecord = ts.Readall

ts.Close

checkBasicDetails
End If

End Sub

Private Sub checkBasicDetails()

'// need to find out if the first two characters are "01" which identifies it as basic details
'// if they ain't then there's a problem with the data file

strDataField = Left$(strClaimRecord, 2)

If strDataField <> "01" Then
MsgBox "problem : this data file is corrupted - please contact SPL"
File1.Refresh

Else '// we're ok
'//open the database
'dataRacIncoming.DatabaseName = "u:\rac data imports\rac incoming.mdb"
'dataRacIncoming.DatabaseName = "g:\access\rac\rac incoming.mdb"

'dataRacIncoming.DatabaseName = "k:\db\rac incoming.mdb"


processBasicDetails '// start processing the data file
End If

End Sub


Private Sub processBasicDetails()

'// procedure to process the basic details section of the data file

dataRacIncoming.RecordSource = "Basic Details" '// attach to the right table
dataRacIncoming.Refresh
dataRacIncoming.Recordset.AddNew '// open the table for a new record

dataRacIncoming.Recordset("record identifier") = "01" '// get the first bit into the table

intCharPos = 3 '// position beyond the Record Identifier (ie "01")
intFieldLength = 8 '// the length of the next field


strDataField = getField(intFieldLength) '// call function to return the datafield

'// the first data field is the Claim ID, and we need to keep this in memory as we will
'// need it for subsequent details, if there are any.
strClaimID = strDataField

'// now we can add the claimID into the table
dataRacIncoming.Recordset("claim ID") = strDataField

'// and put the character position beyond the field
intCharPos = intCharPos + intFieldLength

'// step through the data and get the fields into the DB
intFieldLength = 3
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("user") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("Claim Type Code") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("claim product") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("claim category") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("driver title") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("driver forename") = strDataField
intCharPos = intCharPos + intFieldLength
driverForename = strDataField


intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("driver surname") = strDataField
intCharPos = intCharPos + intFieldLength
driverSurname = strDataField


intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("title") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("forename") = strDataField
intCharPos = intCharPos + intFieldLength
otherForename = strDataField

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("surname") = strDataField
intCharPos = intCharPos + intFieldLength
otherSurname = strDataField

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("street") = strDataField
intCharPos = intCharPos + intFieldLength
addressStreet = strDataField


intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("street2") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("town") = strDataField
intCharPos = intCharPos + intFieldLength
addressTown = strDataField

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("county") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("country") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 8
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("post code") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 5
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("work phone international code") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 5
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("work phone area code") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 16
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("work phone number") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 5
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("home phone international code") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 5
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("home phone area code") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 8
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("home phone number") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 10
strDataField = getField(intFieldLength)
If strDataField <> " " Then
dataRacIncoming.Recordset("accident date") = strDataField
End If
intCharPos = intCharPos + intFieldLength
accidentDate = strDataField

intFieldLength = 5
strDataField = getField(intFieldLength)
If strDataField <> " " Then
dataRacIncoming.Recordset("accident time") = strDataField
End If
intCharPos = intCharPos + intFieldLength

intFieldLength = 64
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("accident street") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("accident area") = strDataField
intCharPos = intCharPos + intFieldLength

'// this next one is the first of the Length identifier fields
intFieldLength = 4
strDataField = getField(intFieldLength)
intCharPos = intCharPos + intFieldLength

intFieldLength = CInt(strDataField) '// got the length of the field now

If intFieldLength > 0 Then
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("accident description") = strDataField
Else
dataRacIncoming.Recordset("accident description") = " "
End If

intCharPos = intCharPos + intFieldLength


'// and the next one is the second...
intFieldLength = 4
strDataField = getField(intFieldLength)
intCharPos = intCharPos + intFieldLength

intFieldLength = CInt(strDataField) '// got the length of the field now

If intFieldLength > 0 Then
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("accident note") = strDataField
Else
dataRacIncoming.Recordset("accident note") = " "
End If

intCharPos = intCharPos + intFieldLength


'// and then back to ordinary fields
intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("RAC member") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("claimant driver") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("insured") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("other vehicles") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("other parties") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("vehicle damage") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("received estimate") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("injury driver") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("injury other") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("loss of earnings") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("vehicle in use") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("hire vehicle") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("other losses") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("witnesses") = strDataField
intCharPos = intCharPos + intFieldLength


intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("police involved") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 1
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("police report") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("police force") = strDataField
intCharPos = intCharPos + intFieldLength

'MsgBox (strDataField)
'// update the table record
dataRacIncoming.Recordset.Update
dataRacIncoming.Refresh

nextJob '// see if there's more data

End Sub

rivate Sub clientsInsuranceDetails()
'// code 04

'// first thing to do is to open the table and write the code and claim i/d

dataRacIncoming.RecordSource = "clients insurance details" '// attach to the right table
dataRacIncoming.Refresh
dataRacIncoming.Recordset.AddNew '// open the table for a new record

dataRacIncoming.Recordset("record identifier") = "04" '// get the first bit into the table
dataRacIncoming.Recordset("claim id") = strClaimID '// get the claim id into the table

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("name") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("street") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("street2") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("town") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("county") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("country") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 8
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("post code") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 40
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("policy number") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("policy type") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 8
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("policy excess") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 30
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("claim reference") = strDataField
intCharPos = intCharPos + intFieldLength


dataRacIncoming.Recordset.Update '// write the data away
dataRacIncoming.Refresh


nextJob '// see if there's more data

End Sub

Private Sub involvedParties()
'// code 05

'// first thing to do is to open the table and write the code and claim i/d

dataRacIncoming.RecordSource = "involved parties" '// attach to the right table
dataRacIncoming.Refresh
dataRacIncoming.Recordset.AddNew '// open the table for a new record

dataRacIncoming.Recordset("record identifier") = "05" '// get the first bit into the table
dataRacIncoming.Recordset("claim id") = strClaimID '// get the claim id into the table

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("involvement") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("title") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("forename") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("suurname") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("street") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("street2") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("town") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("county") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 50
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("country") = strDataField
intCharPos = intCharPos + intFieldLength

intFieldLength = 8
strDataField = getField(intFieldLength)
dataRacIncoming.Recordset("post code") = strDataField
intCharPos = intCharPos + intFieldLength

dataRacIncoming.Recordset.Update '// write the data away
dataRacIncoming.Refresh

nextJob '// see if there's more data

End Sub

Private Sub nextJob()
'// routine to identify the next piece of work to be done on this data

'// find out if there is more data following...
If (intCharPos - 1 = Len(strClaimRecord)) Or (intCharPos - 1 = Len(strClaimRecord) - 2) Then '// there's no more data

File1.Refresh

Dim txtFileName, lengthFileName

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.GetFile(strDataFileName)
lengthFileName = Len(f)

f.Delete '// Delete the dat file.
File1.Refresh

MsgBox "Data processed Ok, and data file cleared"

Label2.Caption = File1.ListCount & " files to process"
Label3.Caption = File2.ListCount & " files to print"


If File1.ListCount > 0 Then
File1.ListIndex = 0
Else
MsgBox "Nowt else to do!"

End If
Label2.Caption = File1.ListCount & " files to process"
Label3.Caption = File2.ListCount & " files to print"

Else '// there is more data

intFieldLength = 2
strNextTable = getField(intFieldLength)
intCharPos = intCharPos + intFieldLength '// put the position beyond the field

Select Case strNextTable

Case "02"
otherInvolvedParties

Case "03"
clientsVehicleDetails

Case "04"
clientsInsuranceDetails

Case "05"
involvedParties

Case Else
fileIsCorrupt
End Select

End If

End Sub


Private Function getField(intFieldLength)
'// function to get a data field out of the file

getField = Mid$(strClaimRecord, intCharPos, intFieldLength)

End Function



Private Sub process_Click()

readDataFile

End Sub

Private Sub fileIsCorrupt()

Dim problemText As String

MsgBox ("Data file " & strDataFileName & " cannot be read. Click OK to Print out problem record...")

Clipboard.SetText (problemText)

myAppID = Shell("C:\Windows\notepad.exe", 1) '// open notepad

SendKeys ("PROBLEM PROBLEM PROBLEM PROBLEM")
SendKeys ("{Enter}")
SendKeys ("{Enter}")
SendKeys ("driver forename : " & driverForename)
SendKeys ("{Enter}")
SendKeys ("driver surname : " & driverSurname)
SendKeys ("{Enter}")
SendKeys ("claimant forename : " & otherForename)
SendKeys ("{Enter}")
SendKeys ("claimant surname : " & otherSurname)
SendKeys ("{Enter}")
SendKeys ("address street : " & addressStreet)
SendKeys ("{Enter}")
SendKeys ("address town : " & addressTown)
SendKeys ("{Enter}")
SendKeys ("accident date : " & accidentDate)
SendKeys ("{Enter}")
SendKeys ("Data File : " & strDataFileName)

SendKeys "%FP", True '// print

dblPauseUntil = CDbl(Now) + (1 / 100000) '// a one second delay to wait for app to print
Do Until CDbl(Now) > dblPauseUntil
DoEvents
Loop

SendKeys "%fx", True '// and close
SendKeys "n", True

f.Delete
File1.Refresh

MsgBox "Problem data printed and data file deleted"

Label2.Caption = File1.ListCount & " files to process"
Label3.Caption = File2.ListCount & " files to print"


End Sub



Private Sub Quit_Click()

End

End Sub

that was the file

basiclly what it does that it takes a DAT file which is flat file which contain alot of text , unorganised one and trying to save

it to a database and it does that perfectly

what i want to do is to run a different procedure which does taking the fields which supposed to be in the database and save

them to a text file and inserting a commas and spaces between them

i know it sound complecated

sorry for botherin u all with me

Hi,

I must say... holy sh*t. If you ask me, you are sitting on a maintenance nightmare. But, anyway, I modified the code so the first line of the file contains the fieldnames, seperated with a comma.

This does exactly what you want. Per table, it exports the fieldnames and the contents in a comma seperated file.

Grtz.©

M.

Thanks alot my friend

but i think you didnt get my problem

just leave the database away

what i want to do is to create a CSV file directly from the Dat file

without going to the tables

thanks

Hi,

I am not going to repeat all the code, but I will give you a method of doing what you want. You will have to adapt the code to your specific needs.



Now, this will do what you want. But as you can see, it repeats all the code in the other subs. That makes maintenance a cream. But alas, your app. I sincerly hope that this is what you mean, otherwise I'm dead in the water.

Grtz.©

M.