Ascii Line Separated File Format - Export

[The Pipe]

Hello

I have a table that I need to export to a .asc file format. I have had
several attempts at getting this to work but with no luck. Basically
the file would need to have every record displayed on a separate line
- If you like a CrLF delimited file. The unfortunate thing is that the
file is to be then imported into a third party piece of software and
as such I have no other alternative but to use this file format.

Does anyone have any bright ideas as to how I might achieve this?

Any pointers would be appreciated.

[Salad]

Re: Ascii Line Separated File Format - Export

The Pipe wrote:

Hello
>
I have a table that I need to export to a .asc file format. I have had
several attempts at getting this to work but with no luck. Basically
the file would need to have every record displayed on a separate line
- If you like a CrLF delimited file. The unfortunate thing is that the
file is to be then imported into a third party piece of software and
as such I have no other alternative but to use this file format.
>
Does anyone have any bright ideas as to how I might achieve this?
>
Any pointers would be appreciated.
>

You mention you want a record displayed on a separate line then mention
a CrLF delimited file. Did you want a field per line?
123 Tom 1/1/2008
is 1 record per line. Or do you want
123
Tom
1/1/2008
as in a field per line?

If the second, you'll have to roll your own. The code below will get
you close.

Sub WriteFld()
Dim strFile As String
Dim rst As Recordset
Dim i As Integer

Set rst = CurrentDb.OpenR ecordset("JunkT able", dbOpenSnapshot)
If rst.RecordCount 0 Then
strFile = "C:\Test\Junk.T xt" 'output filename
Open strFile For Output As #1

rst.MoveFirst
Do While Not rst.EOF

'loop thru each field in the recordset and print value
For i = 0 To rst.Fields.Coun t - 1
Print #1, rst(i).Value
Next
rst.MoveNext
Loop

Close #1 'close output file
End If

rst.Close
Set rst = Nothing
End Sub

Traffic

http://www.youtube.com/watch?v=FG00Y1M6cDg

[Benny Andersen]

Re: Ascii Line Separated File Format - Export

On Thu, 10 Apr 2008 03:28:09 -0700 (PDT), The Pipe wrote:

Hello
>
I have a table that I need to export to a .asc file format. I have had
several attempts at getting this to work but with no luck. Basically
the file would need to have every record displayed on a separate line
- If you like a CrLF delimited file. The unfortunate thing is that the
file is to be then imported into a third party piece of software and
as such I have no other alternative but to use this file format.
>
Does anyone have any bright ideas as to how I might achieve this?
>
Any pointers would be appreciated.

Try to experiment with the import wizard - making some table from a text
file of wanted format. Notice the import declaration facility - the
oppetunity to save a conversion specification.

That declaration can be used the other way around, by vba:

DoCmd.TransferT ext acExportDelim, specificationna me,tablename,.. .


--
Benny Andersen

[The Pipe]

Re: Ascii Line Separated File Format - Export

On 10 Apr, 14:24, Salad <o...@vinegar.c omwrote:

The Pipe wrote:

Hello

>

I have a table that I need to export to a .asc file format. I have had
several attempts at getting this to work but with no luck. Basically
the file would need to have every record displayed on a separate line
- If you like a CrLF delimited file. The unfortunate thing is that the
file is to be then imported into a third party piece of software and
as such I have no other alternative but to use this file format.

>

Does anyone have any bright ideas as to how I might achieve this?

>

Any pointers would be appreciated.

>
You mention you want a record displayed on a separate line then mention
a CrLF delimited file.  Did you want a field per line?
        123 Tom 1/1/2008
is 1 record per line.  Or do you want
        123
        Tom
        1/1/2008
as in a field per line?
>
If the second, you'll have to roll your own.  The code below will get
you close.
>
Sub WriteFld()
     Dim strFile As String
     Dim rst As Recordset
     Dim i As Integer
>
     Set rst = CurrentDb.OpenR ecordset("JunkT able", dbOpenSnapshot)
     If rst.RecordCount 0 Then
         strFile = "C:\Test\Junk.T xt"  'output filename
         Open strFile For Output As #1
>
         rst.MoveFirst
         Do While Not rst.EOF
>
            'loop thru each field in the recordset and print value
             For i = 0 To rst.Fields.Coun t - 1
                 Print #1, rst(i).Value
             Next
             rst.MoveNext
         Loop
>
         Close #1    'close output file
     End If
>
     rst.Close
     Set rst = Nothing
End Sub
>
Traffichttp://www.youtube.com/watch?v=FG00Y1M 6cDg

Thanks Salad

I appreciate that my requirements were, at best, described in a
somewhat loose manner (Time is of the essence and all that). It was
the "second" one that I was after - one field per line - I shall give
your "get you close" code a trial as soon as I am able. I kinda get
the concept - whatever happens I am sure I will learn something new.

I will be sure to post the results.

Pipe

PS Top link (Traffic) - Reminds me of something.....

[The Pipe]

Re: Ascii Line Separated File Format - Export

On 11 Apr, 18:38, Salad <o...@vinegar.c omwrote:

The Pipe wrote:

On 10 Apr, 22:03, Salad <o...@vinegar.c omwrote:

>

>The Pipe wrote:

>

>>On 10 Apr, 14:24, Salad <o...@vinegar.c omwrote:

>

>>>The Pipe wrote:

>

>>>>Hello

>

>>>>I have a table that I need to export to a .asc file format. I have had
>>>>several attempts at getting this to work but with no luck. Basically
>>>>the file would need to have every record displayed on a separate line
>>>>- If you like a CrLF delimited file. The unfortunate thing is that the
>>>>file is to be then imported into a third party piece of software and
>>>>as such I have no other alternative but to use this file format.

>

>>>>Does anyone have any bright ideas as to how I might achieve this?

>

>>>>Any pointers would be appreciated.

>

>>>You mention you want a record displayed on a separate line then mention
>>>a CrLF delimited file.  Did you want a field per line?
>>      123 Tom 1/1/2008
>>>is 1 record per line.  Or do you want
>>      123
>>      Tom
>>      1/1/2008
>>>as in a field per line?

>

>>>If the second, you'll have to roll your own.  The code below will get
>>>you close.

>

>>>Sub WriteFld()
>>   Dim strFile As String
>>   Dim rst As Recordset
>>   Dim i As Integer

>

>>   Set rst = CurrentDb.OpenR ecordset("JunkT able", dbOpenSnapshot)
>>   If rst.RecordCount 0 Then
>>       strFile = "C:\Test\Junk.T xt"  'output filename
>>       Open strFile For Output As #1

>

>>       rst.MoveFirst
>>       Do While Not rst.EOF

>

>>          'loop thru each field in the recordset and print value
>>           For i = 0 To rst.Fields.Coun t - 1
>>               Print #1, rst(i).Value
>>           Next
>>           rst.MoveNext
>>       Loop

>

>>       Close #1    'close output file
>>   End If

>

>>   rst.Close
>>   Set rst = Nothing
>>>End Sub

>

>>>Traffichtt p://www.youtube.com/watch?v=FG00Y1M 6cDg

>

>>Thanks Salad

>

>>I appreciate that my requirements were, at best, described in a
>>somewhat loose manner (Time is of the essence and all that). It was
>>the "second" one that I was after - one field per line - I shall give
>>your "get you close" code a trial as soon as I am able. I kinda get
>>the concept - whatever happens I am sure I will learn something new.

>

>About all that needs to be changed is the table/query name...you could
>pass that as an argument...and the output filename...whic h could also be
>passed as an argument.  Beyond that?  I suppose you could add an error
>routine.

>

>>I will be sure to post the results.

>

>That'd be nice.

>

>>Pipe

>

>>PS Top link (Traffic) - Reminds me of something.....- Hide quoted text -

>

>- Show quoted text -- Hide quoted text -

>

>- Show quoted text -

>

I have given this a go but Access keeps calling runtime error 3001 -
Invalid Argument.

>

I have done a bit of investigation but have come up with nothing - any
ideas?

>
No, I don't.
>
The program should stop on the line it fails on.  It it doesn't, comment
on the OnError routine temporarily.
>
I changed the line
        Set rst = CurrentDb.OpenR ecordset("JunkT able", dbOpenSnapshot)
to
        Set rst = CurrentDb.OpenR ecordset("Table 1", dbOpenSnapshot)
which happens to be a table I have.
>
I then changed
        strFile = "C:\Test\Junk.T xt"  'output filename
to
        strFile = "C:\Table1. Txt"  'output filename
and then ran the code.  It ran as expected and it created Table1.Txt as
the output file in the format you desire.
>
I suppose you could make it generic and have the sub be
        Sub WriteFld(strFil e As String, strTable As String)
and change the rst line to
        Set rst = CurrentDb.OpenR ecordset(strTab le, dbOpenSnapshot)
and remove the lines
         Dim strFile As String
        strFile = "C:\Test\Junk.T xt"  'output filename
>
Then from a program or immediate window w/o the Dim statements you could
call the routine like this.
        Dim strFile As String
        Dim strTable As String
>
        strFile = "C:\Junk.Tx t"
        strTable = "Customers"
        WriteFld strFile, strTable
>
Bakermanhttp://www.youtube.com/watch?v=ymdssZO Ax3Q- Hide quoted text -
>
- Show quoted text -

Finally got to try this at home and has worked a treat!

I put in a little extra that catches any nulls and ouputs them as zero
length strings (The output file contained the text "Null" wherever
there was one from the source).

Can't thank you enough for your help with this salad.

[Salad]

Re: Ascii Line Separated File Format - Export

The Pipe wrote:

On 11 Apr, 18:38, Salad <o...@vinegar.c omwrote:
>

>>The Pipe wrote:
>>

>>>On 10 Apr, 22:03, Salad <o...@vinegar.c omwrote:

>>

>>>>The Pipe wrote:

>>

>>>>>On 10 Apr, 14:24, Salad <o...@vinegar.c omwrote:

>>

>>>>>>The Pipe wrote:

>>

>>>>>>>Hello

>>

>>>>>>>I have a table that I need to export to a .asc file format. I have had
>>>>>>>severa l attempts at getting this to work but with no luck. Basically
>>>>>>>the file would need to have every record displayed on a separate line
>>>>>>>- If you like a CrLF delimited file. The unfortunate thing is that the
>>>>>>>file is to be then imported into a third party piece of software and
>>>>>>>as such I have no other alternative but to use this file format.

>>

>>>>>>>Does anyone have any bright ideas as to how I might achieve this?

>>

>>>>>>>Any pointers would be appreciated.

>>

>>>>>>You mention you want a record displayed on a separate line then mention
>>>>>>a CrLF delimited file. Did you want a field per line?
>>>>> 123 Tom 1/1/2008
>>>>>>is 1 record per line. Or do you want
>>>>> 123
>>>>> Tom
>>>>> 1/1/2008
>>>>>>as in a field per line?

>>

>>>>>>If the second, you'll have to roll your own. The code below will get
>>>>>>you close.

>>

>>>>>>Sub WriteFld()
>>>>> Dim strFile As String
>>>>> Dim rst As Recordset
>>>>> Dim i As Integer

>>

>>>>> Set rst = CurrentDb.OpenR ecordset("JunkT able", dbOpenSnapshot)
>>>>> If rst.RecordCount 0 Then
>>>>> strFile = "C:\Test\Junk.T xt" 'output filename
>>>>> Open strFile For Output As #1

>>

>>>>> rst.MoveFirst
>>>>> Do While Not rst.EOF

>>

>>>>> 'loop thru each field in the recordset and print value
>>>>> For i = 0 To rst.Fields.Coun t - 1
>>>>> Print #1, rst(i).Value
>>>>> Next
>>>>> rst.MoveNext
>>>>> Loop

>>

>>>>> Close #1 'close output file
>>>>> End If

>>

>>>>> rst.Close
>>>>> Set rst = Nothing
>>>>>>End Sub

>>

>>>>>>Trafficht tp://www.youtube.com/watch?v=FG00Y1M 6cDg

>>

>>>>>Thanks Salad

>>

>>>>>I appreciate that my requirements were, at best, described in a
>>>>>somewhat loose manner (Time is of the essence and all that). It was
>>>>>the "second" one that I was after - one field per line - I shall give
>>>>>your "get you close" code a trial as soon as I am able. I kinda get
>>>>>the concept - whatever happens I am sure I will learn something new.

>>

>>>>About all that needs to be changed is the table/query name...you could
>>>>pass that as an argument...and the output filename...whic h could also be
>>>>passed as an argument. Beyond that? I suppose you could add an error
>>>>routine.

>>

>>>>>I will be sure to post the results.

>>

>>>>That'd be nice.

>>

>>>>>Pipe

>>

>>>>>PS Top link (Traffic) - Reminds me of something.....- Hide quoted text -

>>

>>>>- Show quoted text -- Hide quoted text -

>>

>>>>- Show quoted text -

>>

>>>I have given this a go but Access keeps calling runtime error 3001 -
>>>Invalid Argument.

>>

>>>I have done a bit of investigation but have come up with nothing - any
>>>ideas?

>>
>>No, I don't.
>>
>>The program should stop on the line it fails on. It it doesn't, comment
>>on the OnError routine temporarily.
>>
>>I changed the line
> Set rst = CurrentDb.OpenR ecordset("JunkT able", dbOpenSnapshot)
>>to
> Set rst = CurrentDb.OpenR ecordset("Table 1", dbOpenSnapshot)
>>which happens to be a table I have.
>>
>>I then changed
> strFile = "C:\Test\Junk.T xt" 'output filename
>>to
> strFile = "C:\Table1. Txt" 'output filename
>>and then ran the code. It ran as expected and it created Table1.Txt as
>>the output file in the format you desire.
>>
>>I suppose you could make it generic and have the sub be
> Sub WriteFld(strFil e As String, strTable As String)
>>and change the rst line to
> Set rst = CurrentDb.OpenR ecordset(strTab le, dbOpenSnapshot)
>>and remove the lines
> Dim strFile As String
> strFile = "C:\Test\Junk.T xt" 'output filename
>>
>>Then from a program or immediate window w/o the Dim statements you could
>>call the routine like this.
> Dim strFile As String
> Dim strTable As String
>>
> strFile = "C:\Junk.Tx t"
> strTable = "Customers"
> WriteFld strFile, strTable
>>
>>Bakermanhtt p://www.youtube.com/watch?v=ymdssZO Ax3Q- Hide quoted text -
>>
>>- Show quoted text -

>
>
Finally got to try this at home and has worked a treat!
>
I put in a little extra that catches any nulls and ouputs them as zero
length strings (The output file contained the text "Null" wherever
there was one from the source).
>
Can't thank you enough for your help with this salad.
>

You're welcome. Compliments are our pay and are appreciated.

This group is par excellent in assisting people.

CDMA Made Me Do It.

http://www.youtube.com/watch?v=HzeZhCt5PVA