?
Solved

Export query as csv - Formatting?

Posted on 2006-06-22
18
Medium Priority
?
1,198 Views
Last Modified: 2012-08-13
Hi guys, was wondering if anyone could help.

I've got a query which joins a couple of access tables together:
*********************************************

SELECT CompetitorData.[Entry Number], [Personal Information].Name, [Personal Information].Sex, [Personal Information].Team, CompetitorData.Heat, CompetitorData.Lane, CompetitorData.Wave, [Finish Times].[Finish Time]
FROM [Personal Information] INNER JOIN (CompetitorData INNER JOIN [Finish Times] ON CompetitorData.[Entry Number] = [Finish Times].[Entry Number]) ON ([Personal Information].[Entry Number] = CompetitorData.[Entry Number]) AND ([Personal Information].[Entry Number] = [Finish Times].[Entry Number])
ORDER BY CompetitorData.[Entry Number];


I've then got a function which exports the data:
**********************************

Public Sub export_csv()

On Error GoTo Err_export_csv
    DoCmd.OpenQuery "FillCompetitorTable"
    DoCmd.TransferText acExportDelim, , "FillCompetitorTable", "C:\file.csv"
   

Exit_export_csv:
    Exit Sub

Err_export_csv:
    MsgBox Err.Description
    Resume Exit_export_csv
   
End Sub


I get the results I need in the CSV file but the data is formatted badly.

Snippet:

1,"Name1","M","TeamName",3.00,2.00,"b","00:25:89"
2,"Name2","M","TeamName",3.00,4.00,"b","01:01:23"


I've got an application which uses this data to generate various reports and needs the data to be in the following format:

1,Name1,M,TeamName,3,2,b,00:25:89
2,Name2,M,TeamName,3,4,b,01:01:23


Can anybody suggest a way in which I can do this? It may be a simple change to the query else totally rebuild all of the tables.

Thanks for your help!!
0
Comment
Question by:djdiablo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
18 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16959778
Have u looked at import specifications?

File/getExternal Data/

select file type txt
then advanced

u can specify how to import/export here

save this as a spec
then specify in

DoCmd.TransferText acExportDelim, <<NAMEOFSPEC>>, "FillCompetitorTable", "C:\file.csv"
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16959793
When you manually run an export, there is an advanced tab in the export wizard that allows you to set up how the data is exported. there is an option to select a text qualifier. In this case it's " on your output. You can designate that to [none] then save that as an export spec. That spec name is then plugged into your statement and used to create your data.

 DoCmd.TransferText acExportDelim, SPECNAME , "FillCompetitorTable", "C:\file.csv"
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16959795
(too Slow!!) Hi rock.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 65

Expert Comment

by:rockiroads
ID: 16959872
Yo Jeff!
0
 
LVL 1

Author Comment

by:djdiablo
ID: 16960028
Sorry I can't see an advanced tab!

Not in File>Export     nor
File>get External Data

when i try and export as text I have two fields called "Formatted" and "Autostart" but no wizard appears.


thanks for your prompt responses!
0
 
LVL 1

Author Comment

by:djdiablo
ID: 16960055
p.s. using access 2002 xp
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 total points
ID: 16960429
the first page in the Import Wizard for textfiles

there should be an Advanced button

just select your textfile, u have to start a import before u see the button - big drawback I reckon
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16960438
Its File / Get External Data / Import

then select filetype .txt
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16960473
Here's an optional function

Function ExportAsCSV(strFile as string) As Boolean  '<---Option to pass path and file name or define it below
' This function is to create a CSV file
    Dim rs As New ADODB.Recordset
    Dim strExpFile as string
    Dim strRecord as string
    Dim strSQL as string

    ExportAsCSV = True

' Create the name and directory location of the export file
   
    strExpFile = "PATH & YOURFILE.CSV"
   
    Open strExpFile For Output As #1
    strSQL = "YOUR SQL QUERY GOES HERE"

    rs.Open strSQL, CurrentProject.Connection
    If rs.EOF = True Then
    Close #1
    ExportAsCSV = False
        Exit Function
    End If
' Begin the record loop
    Do Until rs.EOF

' Initialize and build the string
            strRecord = ""
            strRecord = strRecord & rs.Fields("YOUR FIELD1").Value & ","
            strRecord = strRecord & rs.Fields("YOUR FIELD2").Value & ","
            strRecord = strRecord & rs.Fields("YOUR FIELD3").Value & ","
            strRecord = strRecord & rs.Fields("YOUR FIELD4").Value & ","
            strRecord = strRecord & rs.Fields("YOUR FIELD5").Value & ","
            strRecord = strRecord & rs.Fields("YOUR FIELD6").Value & ","

' Output the record to the text file
        Print #1, strRecord  '<---insert a ";" after strRecord if you want streaming output
        rs.MoveNext
    Loop
    Close #1
    rs.Close
    Set rs = Nothing
End Function
0
 
LVL 1

Author Comment

by:djdiablo
ID: 16960834
Thanks,

I have managed to create a spec and put it into the command and it has made absolutely no difference!!

DoCmd.TransferText acExportDelim, EXPORTSPEC, "FillCompetitorTable", "C:\file.csv"

does it need to go in quotes?


thanks - once it's all done I'll do the honours with the points for both of your help!
0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 600 total points
ID: 16961228
Yep

DoCmd.TransferText acExportDelim, "EXPORTSPEC", "FillCompetitorTable", "C:\file.csv"
0
 
LVL 1

Author Comment

by:djdiablo
ID: 16961458
As soon as I put quotes into the argument, I get an error


"The Microsoft Jet database engine could not find the object 'file.csv'. Make sure the object exists and that you spell its name and the path correctly."
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16961461
In the spec, did u change the TextQualifier to be none?
by default its "
0
 
LVL 1

Author Comment

by:djdiablo
ID: 16961518
I've now made a macro which makes the above command and this comes up with the same error.
0
 
LVL 1

Author Comment

by:djdiablo
ID: 16961683
Yes I changed the text qualifier and it made no difference.



Access is winding me insane!
0
 
LVL 1

Author Comment

by:djdiablo
ID: 16961851
Thanks for your help both, I've managed to sort out the problem. It appears as though there is a bug with Access in its schema definitions. When the schema.ini is written the custom specification cannot override it.

In order to overcome the latest problem I had to manually export the query first which re-writes the schema.ini. Now the macro/module works just fine.

http://support.microsoft.com/?kbid=225987
http://www.thescripts.com/forum/thread190407.html

Thanks for your help both.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16961953
Try adding a test to see if the file can be found

dim fName as string
on error resume next
fName = "C:\file.csv"

    Open fName For Input Access Read Shared As 1
     if err <> 0 then
            msgbox"File does not exist"
     end if
     Close 1

DoCmd.TransferText acExportDelim, "EXPORTSPEC", "FillCompetitorTable", fName, False, ""
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16961959
Interesting issue...glad it's going.
J
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question