!urgent! comma dilemited file export with null date fields

[abshaw]

This is the first time i am doing an export as comma separated values file out of excel. i did manage to create the file but not what i was required to produce.

our telephone system (ivr) requires (as i am told by the phone guy) a csv file with all the active members loaded into the system at the end of each day. there are five fields in excel sheet

column a --> Member ID
column b --> First name
column c --> Last name
column d --> effective date
column e --> term date

column a b and c are text type data fields
column d and e are date type data fields.

(the column headers are not there in the actual sheet, but to explain, i gave them sample names, the actual sheet only has the data in it)

now lets say the first three lines of data are as follows

10234 jake lumas 01/02/2004 05/02/2004
05435 mary edgar 01/02/2004
3545 james lomaki 01/02/2004 03/05/2004



what the final text file should look like is

"10234","jake","lumas","01/02/2004","05/02/2004"
"05435","mary","edgar","01/02/2004",""
"3545","james","lomaki","01/02/2004","03/05/2004"

in summary even if there is a field missing or the data is not there the output should still export 5 complete fields with only "".


it really doesnot matter how, but i need to get this up and running for tommorow morning, please help as its on my head and i am stuck with it.
thanks for your help in advance

 

[acw]

Re: !urgent! comma dilemited file export with null date fiel

Hi

The following will write a text the data to a text file. I've only worked for a blank in the last column. I have based it on the data sample below, starting in cell A1.

Tony

Sub aaa()
Open "c:\temp\ccc.csv" For Output As 1
range("a1").select
While Not IsEmpty(ActiveCell)
If IsEmpty(ActiveCell.Offset(0, 4)) Then
blank5 = ""
Else
blank5 = WorksheetFunction.Text(ActiveCell.Offset(0, 4).Value, "dd/mm/yyyy")
End If

Write #1, Trim(Str$(ActiveCell.Value)), ActiveCell.Offset(0, 1).Value, ActiveCell.Offset(0, 2).Value, WorksheetFunction.Text(ActiveCell.Offset(0, 3).Value, "dd/mm/yyyy"), blank5
ActiveCell.Offset(1, 0).Select
Wend
Close 1
End Sub

 

[abshaw]

Re: !urgent! comma dilemited file export with null date fiel

great it works perfectly as the code description says

can you please make it put "" for all blanks instead of just the last one field, i really appriciate this, thank you.

 

[abshaw]

Re: !urgent! comma dilemited file export with null date fiel

Guys thanks for all the support i finally figured it out,

for the ones following me here is the final code that works associated with a button on the same sheet, so just click and your file is complete.



Sub Button2_Click()
Dim Blank1 As Variant
Dim Blank2 As Variant
Dim Blank3 As Variant
Dim Blank4 As Variant
Dim Blank5 As Variant


Open "c:\temp\ccc.csv" For Output As 1
Range("a1").Select
While Not IsEmpty(ActiveCell)
If IsEmpty(ActiveCell.Offset(0, 0)) Then
Blank1 = ""
Else
Blank1 = Trim(Str$(ActiveCell.Offset(0, 0).Value))
End If
If IsEmpty(ActiveCell.Offset(0, 1)) Then
Blank2 = ""
Else
Blank2 = ActiveCell.Offset(0, 1).Value
End If
If IsEmpty(ActiveCell.Offset(0, 2)) Then
Blank3 = ""
Else
Blank3 = ActiveCell.Offset(0, 2).Value
End If
If IsEmpty(ActiveCell.Offset(0, 3)) Then
Blank4 = ""
Else
Blank4 = WorksheetFunction.Text(ActiveCell.Offset(0, 3).Value, "dd/mm/yyyy")
End If
If IsEmpty(ActiveCell.Offset(0, 4)) Then
Blank5 = ""
Else
Blank5 = WorksheetFunction.Text(ActiveCell.Offset(0, 4).Value, "dd/mm/yyyy")
End If
Write #1, Blank1, Blank2, Blank3, Blank4, Blank5
ActiveCell.Offset(1, 0).Select
Wend
Close 1
End Sub

 

[Ian Mac]

Re: !urgent! comma dilemited file export with null date fiel

I realize you have the answer but I intrigued as to why your Telecoms people need this as you describe.

The IVR, I'm assuming, is connected to the Switch at your server or an external Switch at your line provider.
Either way, neither needs to have a file exported/imported in this manner.
Whether it be at VDN or Vector level.

Seem to me like a lot of work is being done for '0' reason.
I may be missing some vital information here but every Switch/Dialer/IVR setup I've ever worked with has the capacity to import at the server level.

Just venting steam and hopefully saving you or someone else a needless job.

You can PM me with Switch types etc. and I can look into it for you if you wish.