how to do Delimited text file without automatic new line creation

[KitKatePuzzle]

I tried to export query text file for SAP by having new record continue to previous one without going to new line.

This is my current result..

0001|John|4/5 Telecommunication com.
0002|Henry|3 Rama2
0003|Paul|2 Siemenset com.

This is what I want it to be
0001|John|4/5 Telecommunication com.0002|Henry|3 Rama20003|Paul|2 Siemenset com.

 

[the_net_2.0]

I don't think this is possible Kate, not from the wizard anyhow. Others who are more experienced may know of another way.

If all else fails, what you can do is loop through each record in your table and concat every field value from record #1 to the end. I just looked up the string data type in VBA help in Access, and variable length strings can contain up to 2 billion characters. I may be mistaken but I believe one character = one byte. That might be a bit of an estimate though, as I'm not astute with that sort of knowledge.

Anyway though, if that is your last option, once the string value contains all of your information, you can simply stream it into a text file, which is only about 10 lines of code, if that.

 

[DCrake]

How are you delimiting the fields/records? You SAP system is still want to know that. Also what happens if there is nothing in a field?

To get every all fields and alll records into on long string you will have to use recordsets and do Whiles and loops

Dim Rs As DAO.Recordset
Dim StrData As String

Set Rs = CurrentDb.OpenRecordset("TablName")

Do Until Rs.EOF
     For X = 0 To Rs.Fields.Count -1
        StrData = StrData & "|"
     Next X
     Rs.MoveNext
Loop

Rs.Close
Set Rs = Nothing

One issue you may haveis that you exced the max length of the string 32,000 characters. To overcome this you will need to append your string to either a text file or a memo field in a table. Other than that do it in chunks of records.

 

[the_net_2.0]

thank you for providing the loop david. by the way, I absolutely love your office: http://www.xcraftlimited.co.uk/Humour.html

That was a good laugh. thank you! Boy that probably keeps your overhead down to a marginal 'nothing'!

 

[DCrake]

It actually went out to tender, but in the end we did it in house, cost saving exercise.

 

[gemma-the-husky]

you can obviously use a recordset to create a text file manually.

pretty easy once you get used to it.

 

[KitKatePuzzle]

Thank you all...