Solved

Export query as csv - Formatting?

Posted on 2006-06-22
18
1,186 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
  • 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
 
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 250 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 150 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now