event integration

[jack]

I am trying to import data to a previously created spreadsheet from a data
collection database that exports in a CSV format. My issue is that I have a
column of events numbered from 001 thru 100 with a corresponding column with
the quantity of daily events that have occurred and the event collection
database only exports the CSV file with data when it has a quantity of
greater than zero for an event. That is, some event numbers do not export
if no event occurred.
Is there a way I can re-order the csv file to include the event numbers with
a value quantity of zero? The events that do not appear in the exported csv
file vary from day to day.
Any suggestions would appreciated.
Jack

 

[Joel]

You can write your own CSV file using the code below.

Sub putcsv()
Const myFileName = "c:\temp\myfile.csv"
Const ForReading = 1, ForWriting = 2, _
ForAppending = 3

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile _
(myFileName, True)

Lastrow = Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 1 To Lastrow

outputline = ""
Lastcol = Cells(RowCount, Columns.Count).End(xlToLeft).Columns
If Lastcol > 0 Then
Do While (Lastcol >= 1) And _
IsEmpty(Cells(RowCount, Lastcol))

Lastcol = Lastcol - 1
Loop

For ColCount = 1 To Lastcol
If ColCount = 1 Then
outputline = Cells(RowCount, ColCount)
Else
outputline = outputline & "," & _
Cells(RowCount, ColCount)
End If
Next ColCount
End If
f.Writeline outputline
Next RowCount
f.Close
End Sub

 

[jack]

Joel,
Thanks for your response. However, I'm not sure how I apply the code you've
provided as a resolution to my problem.
Is there a way to use the code to look for the non-exported numbered events
and insert those event numbers (in numerical order) with a corresponding
event value of zero? Do I need to run the code with my exported csv file in
a spreadsheet?
Let me know if I have really "missed the boat" on this one".
Thanks

 

[Joel]

ICSV files are just text files that you can edit with a text editor like
notepad. Data is seperated with columns and there is a carriage return at
the end of each line. Nothing more.

There are lot sof possibilities for worki g with CSV files

1) for easy changes that you don't need to do frequently you can just modify
in notepad.
2) You can write a macro that reads a CSV file and writes to a 2nd CSV file
without bringing the data into the worksheet
3) Read data into a worksheet. Perform VBA changes like sorting. Then
write out the modify file to a new CSV file

If the data is sequental then you may be able to use method 2 above. Can
you open the CSV file with Notepad and post the data by doing a copy and
paste to a posting. Seeing the data makes it easier to write the macro.

 

[jack]

Joel,
The following is the event listing and following it is the event listing as
exported data from the event acquisition database.
As you note the exported data "skips" one or more consecutive event #s, as
there was no event quantity for that day.
Is there a way to insert the "skipped" event #s (in the exported data) with
a zero quantity so that it can be directly added to the spreadsheet with the
consecutive event #s? Again, this is currently done manually on a daily
basis and the "data skips" for event #s from the event acquisition database
will / may change from day to day.
Any suggestions on how I can automate this process will be appreciated.

Event#
000
001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

Daily exported data to be inserted into spreadsheet with above consecutive
event #s
event # quantity
004 67
005 11
006 30
007 14
008 3
009 2
010 1
011 9
014 5
015 12
016 1
018 73
021 28
022 4
024 2
025 1
026 3
027 23
033 1
035 2
036 1
038 12
040 1
044 5
047 1
051 31
052 5
055 1
056 3
057 2
060 26
066 1
067 1
072 14
073 6
080 6
081 2
082 41
088 8
089 1
091 28
092 1
102 1
103 1
104 1
118 2

 

[Joel]

See if this code works. change the two filenames as necessary. I removed
extra blanks from the data you posted. I only used the 2nd part of the data
that had the events a numbers on the same row. The macro doesn't bring any
of the data into a worksheet, it only reads and write the two files.



Sub fixevents()
Const oldevent = "c:\temp\event.txt"
Const newevent = "c:\temp\newevent.txt"

Const ForReading = 1, ForWriting = 2, _
ForAppending = 3

Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(oldevent, _
ForReading, TristateFalse)
Set fout = fs.CreateTextFile _
(newevent, True)
'read and write header row
readdata = fin.readline
readdata = Trim(readdata)
fout.writeline readdata

'now read rest of file
nextevent = 1
Do While fin.AtEndOfStream <> True
readdata = fin.readline
readdata = Trim(readdata)
If readdata <> "" Then
eventnumber = Val(Left(readdata, _
InStr(readdata, " ") - 1))
If eventnumber <> nextevent Then
Do While eventnumber <> nextevent
writedata = Format(nextevent, "0##") & _
" 0"
fout.writeline writedata
nextevent = nextevent + 1
Loop
End If
fout.writeline readdata
nextevent = nextevent + 1
End If
Loop
fin.Close
fout.Close
End Sub