473,326 Members | 2,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Ascii Line Separated File Format - Export

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
5 3745
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Bix | last post by:
As this is my very first post, I'd like to give thanks to all who support this with their help. Hopefully, this question hasn't been answered (too many times) before... If anyone could explain...
7
by: jamait | last post by:
Hi all, I m trying to read in a text file into a datatable... Not sure on how to split up the information though, regex or substrings...? sample: Col1 Col2 ...
3
by: Thomas Massong | last post by:
Hello, i have a problem with an ixf file and need your help. Is there any way to export such a file to ascii or some other readable format for a Windows PC? Unfortunaly we have no other way...
13
by: - Steve - | last post by:
I've been given a school assignment and while everything else is easy there's one topic I'm completley lost on. I've been given an ASCII file that looks like this. During start-up, the program...
1
by: N | last post by:
Hi, I'm writing a small web service (using C#) which is going to receive a text file, add a line to it and send it back. Input is a string with each line ending with "\r\n". The problem is in the...
9
by: olson_ord | last post by:
Hi, My ascii file is not exactly a comma separated file. The following is a small but complete example of such a file. (This is the ISCAS circuit file format that I need to read in.) ...
1
by: Vic | last post by:
I have a c program which writes mac address entries onto a text file. Text file when opened in vim looks like this index mac 1 ^@^@^Q^@^@^A ascii of (00.00.11.00.00.01) 2 ^@^@^Q^@^@^B...
13
by: =?Utf-8?B?YXVsZGg=?= | last post by:
i have come across a situation in my project where i read a text file with some characters greater than hex 0x7f. i need to write character (0xE0) to a new file as an exception. however when i...
8
by: Sevla | last post by:
Hello, i need to make a search of a text line on a dci/txt file and after i found it, i´d need to export this line to another new file. could it be possible, copying a specific line of this...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.