Pipe Delimited CSV file from Q

txgeekgirl

Registered User.
Local time
Today, 14:23
Joined
Jul 31, 2008
Messages
187
I need to export query results to a PIPE delimited CSV file. In the 2003 days, you could specify this in the export. With 2010, I have tried to set up a Saved Export but there are no delimiter specifications.

Can anyone help? :banghead:
 
Since it really isn't a CSV (comma separated values) file, I think you would need to write it yourself using code like this:

Code:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer
Dim strFilePath As String
Dim intCount As Integer
Dim strHold
 
strFilePath = "C:\MyFolder\MyPath.csv"
 
Set db = CurrentDb
 
Set rst = db.OpenRecordset("QueryNameHere", dbOpenForwardOnly)
 
Set intFile = FreeFile
 
Open strFilePath For Output As #intFile
 
Do Until rst.EOF
   For intCount = 0 to rst.Fields.Count - 1
     strHold = strHold & rst(intCount).Value & "|"
   Next
   If Right(strHold, 1) = "|" Then
      strHold = Left(strHold, Len(strHold) - 1)
   End If
   Print #intFile, strHold
   rst.MoveNext
Loop
 
Close intFile
rst.Close
Set rst = Nothing
 
Hi Bob!

OK - I need the query to export to a file - even a txt would be fine - pipe delimited. Is this the only option in 2010?
 
Hi Bob!

OK - I need the query to export to a file - even a txt would be fine - pipe delimited. Is this the only option in 2010?
Don't know if you can export to a pipe delimited text file but the code I gave you should work to create the file from the query. It just may take a little longer than a normal export.
 
Just tested Bob's code

no need for Set on
Set intFile = FreeFile
just use
intFile =FreeFile

works great. (using acc2003)
 
Are you kidding me Bob - You are the awesomest air code writer!
 
OK - Bob - you are back up on deck! (apporpriate for the soon ending baseball season)

This is building my query results into a series of stacked strings where instead of each record being on a line, it stacking them
FirstRecord
FirstRecordSecondRecord
FirstRecordSecondRecordThirdRecord

I attempted to add a vbnewline but that just added a space between.

3067|90939|REG|51|51|100
3067|90939|REG|51|51|1002374|90949|SAL|90.5|92.5|97.8378
3067|90939|REG|51|51|1002374|90949|SAL|90.5|92.5|97.83782374|90949|PTO|2|92.5|2.1622
3067|90939|REG|51|51|1002374|90949|SAL|90.5|92.5|97.83782374|90949|PTO|2|92.5|2.16220152|01993|REG|39|39|100
 
OK - Bob - you are back up on deck! (apporpriate for the soon ending baseball season)

This is building my query results into a series of stacked strings where instead of each record being on a line, it stacking them
FirstRecord
FirstRecordSecondRecord
FirstRecordSecondRecordThirdRecord

I attempted to add a vbnewline but that just added a space between.

3067|90939|REG|51|51|100
3067|90939|REG|51|51|1002374|90949|SAL|90.5|92.5|97.8378
3067|90939|REG|51|51|1002374|90949|SAL|90.5|92.5|97.83782374|90949|PTO|2|92.5|2.1622
3067|90939|REG|51|51|1002374|90949|SAL|90.5|92.5|97.83782374|90949|PTO|2|92.5|2.16220152|01993|REG|39|39|100


So what is the code you currently have which is doing this?

Never mind - I know the answer - see my next post below.
 
Never mind my last question. I know what is happening. You need to change this part:
Code:
   Print #intFile, strHold
   rst.MoveNext
Loop

to this:
Code:
   Print #intFile, strHold
   rst.MoveNext
[B][COLOR=red]   strHold = vbNullString[/COLOR][/B]
Loop
 
Thanks for the update.
I went back to my original query (test) and checked it in detail.
I was getting all the records with pipes,
but didn't check/notice the repeat of info.

Made the change, and checked more thoroughly, and all is well.
 

Users who are viewing this thread

Back
Top Bottom