Help with a csv file

[joeythelips]

Hi,

I have an excel file with 8 fields:
Meterref
Customername
Meterno
Meterseq
Multiplier
Reading
Readingtype
Readingdate

I am currently using the following code to csv this file onto a floppy disk (thru access)
The code is working fine.
However, I would like the above field names to be included in the csv file, and have them separated by semi-colons (
Can someone please help me with this?

Thanks,

Joe


Sub UpdateRef()
Dim sSql As String
sSql = "update REFERENCE1 set Successful = 1 where filename = '" & Ref & "'"
CurrentDb.Execute sSql
If DLookup("[SuccessFul]", "Reference1&quot = 1 Then
MsgBox "Successful!"
Else
MsgBox "Unsuccessful!"
End If

End Sub
Sub CSVReads(readfilename)

Dim firstdate As String
Dim Nextdate() As String
Dim wscsv As Workspace
Dim dbcsv As Database
Dim rscsv As Recordset
Dim numrecords As Integer
Dim i As Integer
Dim numfields As Integer
Dim fieldname() As String
Dim allotherfields As String
Dim numcommas As Integer
Dim READFILENAME3 As String
Dim filename1 As String
Dim j As Integer
Dim sTemp As String
Dim fieldvar() As String
Dim meterref(), customername(), meterno(), meterseq(), Multiplier(), Reading(), readingtype(), readingdate(), RDSUM(), Billed(), numbills(), repeats(), RealDate(), DateReceived(), RefID() As String

'Assumes the Date system settings are in the format of day/month/year
Set wscsv = DBEngine.Workspaces(0)
Set dbcsv = wscsv.OpenDatabase("P:\infosynergi.mdb&quot
Set rscsv = dbcsv.OpenRecordset("readingsbills&quot

numrecords = 0
numfields = 0
If rscsv.EOF = True And rscsv.EOF = True Then 'in case no records, just exits
Exit Sub
End If
Close #1
Close #2
numfields = rscsv.Fields.Count




ReDim fieldname(1 To numfields)

For i = 0 To numfields - 1
fieldname(i + 1) = rscsv.Fields(i).Name ' puts the fieldnames in to an array
Next i

numrecords = rscsv.RecordCount 'count the number of records

'ReDim Nextdate(1 To numrecords) ' sets how many records the array is to have
'ReDim fieldvar(1 To numfields)
ReDim meterref(1 To numrecords)
ReDim customername(1 To numrecords)
ReDim meterno(1 To numrecords)
ReDim meterseq(1 To numrecords)
ReDim Multiplier(1 To numrecords)
ReDim Reading(1 To numrecords)
ReDim readingtype(1 To numrecords)
ReDim readingdate(1 To numrecords)

rscsv.MoveFirst

Do
For i = 1 To numrecords

'Nextdate(i) = rscsv!Date1

If rscsv(fieldname(1)) <> &quot;&quot; Then
meterref(i) = rscsv(fieldname(1))

End If
If rscsv(fieldname(2)) <> &quot;&quot; Then
customername(i) = rscsv(fieldname(2))
End If
If rscsv(fieldname(3)) <> &quot;&quot; Then
meterno(i) = rscsv(fieldname(3))
End If
If rscsv(fieldname(4)) <> &quot;&quot; Then
meterseq(i) = rscsv(fieldname(4))
End If
If rscsv(fieldname(5)) <> &quot;&quot; Then
Multiplier(i) = rscsv(fieldname(5))
End If
If rscsv(fieldname(6)) <> &quot;&quot; Then
Reading(i) = rscsv(fieldname(6))
End If

If rscsv(fieldname(7)) <> &quot;&quot; Then
readingtype(i) = rscsv(fieldname(7))


End If

If Not IsDate(rscsv(fieldname(8))) Then 'sorts in case you get some weird dates i.e. 020901
If Not IsNumeric(Left(rscsv(fieldname(8)), 1)) Then

Else
readingdate(i) = rscsv(fieldname(8))
readingdate(i) = Left(readingdate(i), 2) & &quot;-&quot; & Mid(readingdate(i), 3, 2) & &quot;-&quot; & Right(readingdate(i), 2)
End If
Else
readingdate(i) = rscsv(fieldname(8))
End If


rscsv.MoveNext



Next i
Loop Until rscsv.EOF = True
On Error GoTo errhandle


Open &quot;a:\RE&quot; & readfilename & &quot;.csv&quot; For Append As #2

errhandle:
If Error() = &quot;Disk Not Ready&quot; Then
MsgBox Error() + &quot; Insert Disk in Drive A&quot;
Resume
End If

For i = 1 To numrecords

Print #2, Trim(Str(meterref(i))) & &quot;;&quot; & Trim(customername(i)) & &quot;;&quot; & Trim(Str(meterno(i))) & &quot;;&quot; & Trim(Str(meterseq(i))) & &quot;;&quot; & Trim(Str(Multiplier(i))) & &quot;;&quot; & Trim(Reading(i)) & &quot;;&quot; & Trim(readingtype(i)) & &quot;;&quot; & Trim(Format(readingdate(i), &quot;dd-mmm-yy&quot) ' put a comma here followed by eg descripion(i) etc.

Next i
Close #2
rscsv.Close
dbcsv.Close
wscsv.Close



End Sub

 

[ErikZ]

I don't understand, you're loading an Excel file into Access so you can save it as a CSV file?

Why don't you just save it directly from Excel as a CSV?

 

[joeythelips]

Yes,

Its a long story but it is necessary!
Any ideas?

 

[ErikZ]

Sure. Before doing all of the above, create the file on the floppy, copy the string:

&quot;Meterref;Customername;Meterno;Meterseq;Multiplier;Reading;Readingtype;Readingdate&quot;

to it. Close the file.

It looks like the code above appends to the file, so when it runs, it will open up the file and add all the data.