By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Ascii Line Separated File Format - Export

P: n/a
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.

Apr 10 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.OpenRecordset("JunkTable", dbOpenSnapshot)
If rst.RecordCount 0 Then
strFile = "C:\Test\Junk.Txt" '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.Count - 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
Apr 10 '08 #2

P: n/a
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.TransferText acExportDelim, specificationname,tablename,...
--
Benny Andersen
Apr 10 '08 #3

P: n/a
On 10 Apr, 14:24, Salad <o...@vinegar.comwrote:
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.OpenRecordset("JunkTable", dbOpenSnapshot)
* * *If rst.RecordCount 0 Then
* * * * *strFile = "C:\Test\Junk.Txt" *'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.Count - 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=FG00Y1M6cDg
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.....
Apr 10 '08 #4

P: n/a
On 11 Apr, 18:38, Salad <o...@vinegar.comwrote:
The Pipe wrote:
On 10 Apr, 22:03, Salad <o...@vinegar.comwrote:
>The Pipe wrote:
>>On 10 Apr, 14:24, Salad <o...@vinegar.comwrote:
>>>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.OpenRecordset("JunkTable", dbOpenSnapshot)
* *If rst.RecordCount 0 Then
* * * *strFile = "C:\Test\Junk.Txt" *'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.Count - 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=FG00Y1M6cDg
>>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...which 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.OpenRecordset("JunkTable", dbOpenSnapshot)
to
* * * * Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
which happens to be a table I have.

I then changed
* * * * strFile = "C:\Test\Junk.Txt" *'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(strFile As String, strTable As String)
and change the rst line to
* * * * Set rst = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)
and remove the lines
* * * * *Dim strFile As String
* * * * strFile = "C:\Test\Junk.Txt" *'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.Txt"
* * * * strTable = "Customers"
* * * * WriteFld strFile, strTable

Bakermanhttp://www.youtube.com/watch?v=ymdssZOAx3Q- 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.

Jun 27 '08 #5

P: n/a
The Pipe wrote:
On 11 Apr, 18:38, Salad <o...@vinegar.comwrote:
>>The Pipe wrote:
>>>On 10 Apr, 22:03, Salad <o...@vinegar.comwrote:
>>>>The Pipe wrote:
>>>>>On 10 Apr, 14:24, Salad <o...@vinegar.comwrote:
>>>>>>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.OpenRecordset("JunkTable", dbOpenSnapshot)
> If rst.RecordCount 0 Then
> strFile = "C:\Test\Junk.Txt" '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.Count - 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=FG00Y1M6cDg
>>>>>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...which 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.OpenRecordset("JunkTable", dbOpenSnapshot)
to
Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
which happens to be a table I have.

I then changed
strFile = "C:\Test\Junk.Txt" '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(strFile As String, strTable As String)
and change the rst line to
Set rst = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)
and remove the lines
Dim strFile As String
strFile = "C:\Test\Junk.Txt" '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.Txt"
strTable = "Customers"
WriteFld strFile, strTable

Bakermanhttp://www.youtube.com/watch?v=ymdssZOAx3Q- 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

Jun 27 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.