Adlerm
asked on
DoCmd.TransferTest wrapping output data in double quotes
Hi
I have built a SQL Table (say tblString) that consist of one field - which holds comma separated data - for example: 24/04/2010,$220.00,$22.00,
24/04/2010,$440.00,$44.00,
etc
I need to write these records to an output text (.txt) file which I attempted to do via a
DoCmd.TransferText acExportDelim, "tblString", "C:\OutFile.txt"
This works in the main except the records in the Output.txt file are wrapped in double quotes
for example based on the above sample the records appear as
"24/04/2010,$220.00,$22.00
"24/04/2010,$440.00,$44.00
It is important that the Output.txt records reflect the input record structure - in other words without the double quotes as the next step in the process is to use the Output.txt file as a CSV file to be import into a separate application.
Is there any way I can write the records to the Output.txt file with out the double quotes. I've tried a number of alternatives - Do.Cmd.OutPutTo acOutputReport, DoCmd.OutPutTo acOutputFile etc but I still run into problems - for example DoCmd.OutputReport imbeds blank lines.
I have built a SQL Table (say tblString) that consist of one field - which holds comma separated data - for example: 24/04/2010,$220.00,$22.00,
24/04/2010,$440.00,$44.00,
etc
I need to write these records to an output text (.txt) file which I attempted to do via a
DoCmd.TransferText acExportDelim, "tblString", "C:\OutFile.txt"
This works in the main except the records in the Output.txt file are wrapped in double quotes
for example based on the above sample the records appear as
"24/04/2010,$220.00,$22.00
"24/04/2010,$440.00,$44.00
It is important that the Output.txt records reflect the input record structure - in other words without the double quotes as the next step in the process is to use the Output.txt file as a CSV file to be import into a separate application.
Is there any way I can write the records to the Output.txt file with out the double quotes. I've tried a number of alternatives - Do.Cmd.OutPutTo acOutputReport, DoCmd.OutPutTo acOutputFile etc but I still run into problems - for example DoCmd.OutputReport imbeds blank lines.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi aqux3e
I'm going to award you the points even though your suggestion didn't work - at least you tried - more than can be said for the other EE "experts. It makes you wonder why you pay EE membership fees from the poor response I've got from a couple of questions.
Below is my solution to the problem - I searched the EE question library and came across a similar (if not identical) problem to mine so I lifted the code from the EE question library and adopted it to my requirements. I apologise for not crediting the original author but I simply cut and pasted the code without thinking about who supplied the code - so if look familiar please accepty my thanks .
Public Function ExportMYOBInterfaceDetails
Dim db As Database
Dim rsString As New ADODB.Recordset
Dim strCNN As String
Dim sMyPath As String
Dim sPathFileName As String
Dim myfile As String
strCNN = CNN
rsString.Open "Select * From vu1080_sel_MYOBInterfaceSt
myfile = "MYOB Interface" & inpDateSuffix & inpBatchNo & ".txt"
sPathFileName = inpPathName & myfile
Open sPathFileName For Output As #1
Do Until rsString.EOF
Print #1, rsString!StringDetails
rsString.MoveNext
Loop
rsString.Close
Close #1
End Function
I'm going to award you the points even though your suggestion didn't work - at least you tried - more than can be said for the other EE "experts. It makes you wonder why you pay EE membership fees from the poor response I've got from a couple of questions.
Below is my solution to the problem - I searched the EE question library and came across a similar (if not identical) problem to mine so I lifted the code from the EE question library and adopted it to my requirements. I apologise for not crediting the original author but I simply cut and pasted the code without thinking about who supplied the code - so if look familiar please accepty my thanks .
Public Function ExportMYOBInterfaceDetails
Dim db As Database
Dim rsString As New ADODB.Recordset
Dim strCNN As String
Dim sMyPath As String
Dim sPathFileName As String
Dim myfile As String
strCNN = CNN
rsString.Open "Select * From vu1080_sel_MYOBInterfaceSt
myfile = "MYOB Interface" & inpDateSuffix & inpBatchNo & ".txt"
sPathFileName = inpPathName & myfile
Open sPathFileName For Output As #1
Do Until rsString.EOF
Print #1, rsString!StringDetails
rsString.MoveNext
Loop
rsString.Close
Close #1
End Function
ASKER
The solution offered didn't work but at least "aqux3e" tried. His/Her solution was the only offering I received in approximately 2 days and as far as I'm concerned this is less than acceptable. This appears to be an EE trend as a question I posted the other week was abondoned as not one response was received. If I'm not offering enough points then let me know and I will adjust them. On the other hand if so call EE experts are only picking high point qustions and leaving the rest then they are only in the business for self glory rather that trying to help people who have problems and are seeking help.
ASKER
Yes I have tried that approach and just retried it using your suggestion of using nothing as text qualifier but it still writes out the records with double quotes wrapped around the data string.
I should have also mentioned that the application that imports the comma separated CSV file also expects a blank line to distinguish between different group of records. In other words the out put should look like
AB,24/04/2010,$220.00,$22.
AB,24/04/2010,$440.00,$44.
Blank line
CD,24/04/2010,$1100.00,$11
CD,24/04/2010,$330.00,$33.
where AB and CD distinguish the different groups
I used the semi colon as a separator - as you suggested -but this causes a semi colon to be inserted in the blank line - again not what I want