!urgent! comma dilemited file export with null date fields

abshaw

Board Regular
Joined
Feb 12, 2004
Messages
53
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Board Regular
Joined
Feb 12, 2004
Messages
53
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

Board Regular
Joined
Feb 12, 2004
Messages
53
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

(y) :biggrin:
 

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top