Exporting to a text file

How to export an Access query to a text file without the column heading? The 
query consists of single column and is used to import to an accounting 
system. Thanks. 


0
Paul
5/12/2007 11:51:59 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
1175 Views

Similar Articles

[PageSpeed] 54

Cannot be done with the built-in TransferText action / method. You'd need to 
open a text file via VBA code and write the query's records (one at a time) 
into that file. If interested, post back and I'll provide some sample code.

-- 

        Ken Snell
<MS ACCESS MVP>


"Paul" <paul_mak@shaw.ca> wrote in message 
news:O$MxJCPlHHA.1244@TK2MSFTNGP04.phx.gbl...
> How to export an Access query to a text file without the column heading? 
> The query consists of single column and is used to import to an accounting 
> system. Thanks.
> 


0
Ken
5/13/2007 12:43:57 AM
Yes please...

Thanks


"Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
news:erMdzePlHHA.1624@TK2MSFTNGP06.phx.gbl...
> Cannot be done with the built-in TransferText action / method. You'd need 
> to open a text file via VBA code and write the query's records (one at a 
> time) into that file. If interested, post back and I'll provide some 
> sample code.
>
> -- 
>
>        Ken Snell
> <MS ACCESS MVP>
>
>
> "Paul" <paul_mak@shaw.ca> wrote in message 
> news:O$MxJCPlHHA.1244@TK2MSFTNGP04.phx.gbl...
>> How to export an Access query to a text file without the column heading? 
>> The query consists of single column and is used to import to an 
>> accounting system. Thanks.
>>
>
> 


0
Paul
5/13/2007 1:42:35 AM
Here is a sample subroutine to write data to text file without a header row 
in the text file -- change the value of the strTextFile constant to be the 
path and filename of the textfile to be created, and change the value of the 
strDelim constant to be the delimiter that you want to use to separate the 
field values, and change the value of the strSQL constant to be either the 
name of the table/query you want to export OR to be the SQL statement that 
will provide the data you want to export:


Public Sub WriteRstToTextFileWithoutHeaderRow()

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim intF As Integer
Dim lngLoop As Long
Dim strRecord As String, strTemp As String

Const strTextFile As String = "C:\MyTextFile.txt"
Const strDelim As String = ","
Const strSQL As String = "SELECT * FROM TableName;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

intF = FreeFile

Open strTextFile For Output As #intF

If rst.EOF = False And rst.BOF = False Then
    rst.MoveFirst
    Do While rst.EOF = False
        strRecord = ""
        For lngLoop = 0 To rst.Fields.Count - 1
          ' delimit value with " characters if value contains delimiter
            If InStr(rst.Fields(lngLoop).Value, strDelim) > 0 Then
                strTemp = Chr(34) & Nz(rst.Fields(lngLoop).Value, "") & 
Chr(34)
            Else
                strTemp = Nz(rst.Fields(lngLoop).Value, "")
            End If
            strRecord = strRecord & strTemp & strDelim
        Next lngLoop
        If Len(strRecord) > 0 Then
            strRecord = Left(strRecord, Len(strRecord) - Len(strDelim))
            Print #intF, strRecord
        End If
        rst.MoveNext
    Loop
End If

Close #intF

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub


-- 

        Ken Snell
<MS ACCESS MVP>



"Paul" <paul_mak@shaw.ca> wrote in message 
news:uzq18$PlHHA.492@TK2MSFTNGP04.phx.gbl...
> Yes please...
>
> Thanks
>
>
> "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
> news:erMdzePlHHA.1624@TK2MSFTNGP06.phx.gbl...
>> Cannot be done with the built-in TransferText action / method. You'd need 
>> to open a text file via VBA code and write the query's records (one at a 
>> time) into that file. If interested, post back and I'll provide some 
>> sample code.
>>
>> -- 
>>
>>        Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "Paul" <paul_mak@shaw.ca> wrote in message 
>> news:O$MxJCPlHHA.1244@TK2MSFTNGP04.phx.gbl...
>>> How to export an Access query to a text file without the column heading? 
>>> The query consists of single column and is used to import to an 
>>> accounting system. Thanks.
>>>
>>
>>
>
> 


0
Ken
5/13/2007 5:06:08 AM
Reply:

Similar Artilces:

Default "File Origin" is wrong (Japanese)
Excel2002 /Win2K Hi If I save a spreadsheet to "tab delimited text" format and thne re-open it, always defaults the "file origin" to be "932:Japanese (Shift-JIS)" instead of "1252:Western European (Windows)"! How do I get my MS Excel to default to the correct thing?! Ship Shiperton Henethe ...

Text not copying as text from Word to Excel 2003
I have a column of data in Word that contains info like, K-12, K-6, P, 9-12, 6-8. When I copy the data to Excel, the cells with the letters copy correctly, but the cells with the numbers convert to dates. I've tried pre-formatting the target cells in Excel, I've tried Data | Text to Columns, I've tried preceding the data with a single apostrophe before copying the data from Word. Incidently, this "works" but when I find and replace the apostrophe, the text gets converted to a date! Can anyone help? Thanks in advance. -- Christine "Christine" <Ch...

How I link 1 sheet to other for sharing text information?
I am working in sheet1, suppose I click any text from sheet 1 then related sheet suppose sheet4 should open .How to do sheet to sheet linking? Easiest method is a 'hyperlink' Click on a cell in sheet1 - go to menu - insert - hyperlink, then complete with sheet4 and relevant cell reference from sheet4 (or click on tab for sheet4 and when it opens click on the relevant link cell). Next time you click on the cell in sheet1 it will automatically take you to the linked cell in sheet4. Depends on which version of Excel you're using as to precise method Hope this helps "Excel ...

In Excel, gridlines won't print--File,PageSetup,Sheet,Gridlines--
I want to PRINT gridlines: File,PageSetup,Sheet,Gridlines--but no joy. Gridlines DO show on the monitor but not on the Print Preview or on an actual Print. Perhaps you have "Draft Quality" enabled. Below "Black and White" This will prevent gridlines from printing or show in print preview. Gord Dibben MS Excel MVP On Sat, 8 Jul 2006 14:30:02 -0700, 4most <4most@discussions.microsoft.com> wrote: >I want to PRINT gridlines: File,PageSetup,Sheet,Gridlines--but no joy. >Gridlines DO show on the monitor but not on the Print Preview or on an actual >P...

Uploading file to another drive
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01CAFB28.4A108260 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm trying to create a web page that allows the user to upload a file. = If I set the save path somewhere on drive C:\, it works just fine. = However, I need it to save elsewhere, like drive E:\. When I do this, I = get an error saying "Could not find a part of the path '...' and then is = lists the path. All the pieces of the path are there and if you change = E: to C:, i...

error opening excel file #2
hi! i cannot open this excel file, an microsoft visual basic window appear saying "An error occur while loading 'Sheet32111111111111111111111111'. Do you want to continue loading the project?" i click yes, microsoft window appear and asking if i want to update this workbook? then i click no a Program Error window appear saying " An error log is being created" what happen to the excel file? the excel file is only 2.6Mb Hi jp- Without a lot more detail, one can only guess, but here are a couple of ideas. 1) The file may be damaged or corrupted in some way. Pe...

Delete specific folders from >2Gb .PST-File
My archive-.pst has grown >2GB, although I have installed the latest Service Release - which should prevent just that. Now, Outlook 2000 won't open the file. :-(( scanpst.exe as well refuses to anything with it. However, there is a lot of data in the file I don't really need, like the Journal, Kalender, Notes, To-Do-List, etc. Is there a way to delete these folders from the .pst-file? Or is there a way to run a compression on the file to reduce it's size under 2 GB? Or is there a way to extract just all e-mails from the .pst-file? I just hate the idea to run 2gb152.exe a...

Only Exporting 16384 records with Office '02 and Excel '03
How come it only lets me export this many records with the newer versions?????? My original long answer seems to be lost. In a nutshell, don't use OutPutTo. Instead using TransferSpreadsheet instead. You can readily chose which version of Excel for the export. if memory serves, acSpreadsheetTypeExcel9 will allow 64K of records. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryA", "n:\qryA.xls", True -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Schwimms" wrote: > How com...

"Publisher cannot open the file" error
When I try to open a Publisher 2003 file in Publisher 2007 I am getting a "Publisher cannot open the file" error message. I've seen all the comments about SP2 and the Hotfix but I've tried the Hotfix and uninstalling SP2 but neither helped. I can open some 2003 but not all and the weird thing is that I used to be able to open the files without any issue. Any suggestions would be welcome. I had the same issue (you may have seen my previous post) and ended up submitting a paid incident to Microsoft. In short, if you open certain publisher files from previous versions be...

Help with POPing errors due to messsages with suspect files
Hi: I'm having difficulty POPing mail from an Exchange 2000 server (using an Outlook 2003 client). Generally, everything works fine, however, occasionally, a message that is later confirmed to contain a virus, causes POPing to grind to a halt. Messages received after the suspect message will not download to the client! Both the server and the client are running Symantec anti virus plugins. What gives? Thanks for any help. -- Dab Cut off: yourhead to respond Dab <noThanks@hotmail.com> wrote: > I'm having difficulty POPing mail from an Exchange 2000 server (usin...

Text in toolbar button labels
Hello everybody!! Is it possible to change the text in the labels of standard buttons on the crm toolbars?? (the standard ones, not the added ones in isv config) thank you!!! ...

How do I format text as a hyperlink in Excel
I want up to 1,00o cells in a single Excel worksheet to display as hyperlinks to general web addresses (mainly home pages). Each cell is a different web address. How do I format the Excel cells to display and act as a hyperlink? The formula to use is =HYPERLINK(actual_address, friendly_address) where actual_address is the full link to the website (or other excel sheets/files), and friendly_address is what is shown in the Excel cell - underlined and coloured blue. If friendly_address is missing, the actual_address is shown. Hope this helps. Pete Pete Thanks for this BUT I already hav...

file properties
is there a way to test a file prior to opening it to see if is of a certain type, such as text, doc, and so on. satan wrote: > is there a way to test a file prior to opening it to see if is > of a certain type, such as text, doc, and so on. > > You can look at the file name extension, and that's about all you can do. The file system has no idea what kind of stuff is in the file. -- Scott McPhillips [VC++ MVP] ...

applying power users access to adminstrator files/folders
Hi there! Sorry if this has been posted on the wrong area im very new to all this. I have created a new user account with 'power user' rights. Using that account means I do not have access to files and folders in the administrators accounts though. Is there a way that I can give the new user rights to view and use folders/ files from the administrators? If so I would really appreciate if that come be explained. many thanks, Ben :) Your question may be better answered in a newsgroup appropriate to your problem. This newsgroup is dedicated to the Microsoft Access d...

Orientation of text in an autoshape
In Excel 2003, I have a rectangle that is tall and thin. I want to put some text inside it, but I need the text oriented 90 degrees in the rectangle. Is this possible? If so, how? Thanks, Tonso I will assume a recatngle fro the Drawing Toolbar. Right-click on the edge of the rectangle and "Format Autoshape" and on Alignment tab choose your orientation. Gord Dibben MS Excel MVP On Wed, 21 Apr 2010 12:32:58 -0700 (PDT), Tonso <wthomasss@hotmail.com> wrote: >In Excel 2003, I have a rectangle that is tall and thin. I want to put >some text inside i...

excel.exe
How do I run Chkdsk utility to correct this? I am able to open my files despite this message. Is there any risk to doing this? -- Captaintivo Hi, It's highly unlikely that ChkDsk will have any effect. Could you give us the entire error message exactly? It might clear up by reinstalling Office or Excel. -- If this helps, please click the Yes button Cheers, Shane Devenshire "captaintivo" wrote: > How do I run Chkdsk utility to correct this? I am able to open my files > despite this message. Is there any risk to doing this? > > -- > Captaintivo ...

File Server
Hi, I m using windows 2003 Server for file server.i have atleast 30 computer which is connted with file server through window 2003 Domain controller. 2 windows xp machine always hang 20 to 30 mintue of interval. why its happening. can u help me????????? event id 5 on windows xp machine. Hello Jiwan, Let's start with an unedited ipconfig /all from a problem client and the DC/DNS servers. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ** Please do NOT email, only reply to Newsgroups ...

how do I connect MS Project file (.mpp) to existing Sharept site
How do I connect a MS Project file (.mpp) to an existing SharePoint site? king8599 wrote: > How do I connect a MS Project file (.mpp) to an existing SharePoint site? This group is for discussion of Microsoft Publisher, not SharePoint or Project. I suggest you try one of the groups for Project and/or SharePoint. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

empty files in the deleted folder upon exit
So I am using Outlook in the Public Beta of MS Office 2010, but WHERE is that option for "Emptying files in the deleted folder upon exit"? I am so used to it. Please help! File-> Options-> Advanced-> section Outlook start and exit For an overview of the locations of common options see; http://www.howto-outlook.com/faq/locatefeature.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World An...

Office2007 and IE7 Uploading files
Hi, In the Known Issues section of the installation Readme for the CRM V3C Client, it says: "If you are using Office 2007 and Internet Explorer 7 on the client computer, uploading Microsoft Office 2007 files may fail. Installing Hotfix 929488 on the Microsoft CRM server resolves this issue if you are working online in Microsoft Dynamics CRM client for Outlook." But I searched high and low but couldn't find this hotfix. Does anyone know where it might be? Cheers. Tony ...

Outlook 2010
In Outlook 2003 I had one .pst file with all contacts inside, but used 3 email accounts with no automatic rules for mail delivery. I manually put the emails down in it's respective archive.pst files and want it done this way. In Outlook 2010 when using several email accounts, it's automatically established one .pst file for each email account where the respective email drops into. Is there a way to have it all into one "Personal Folder" (the old way)? (I want to send emails were sender account is the only on showing as sender and not any "sent on behalf ...

File Open doesn't display all directories (List vs Details)
When in Excel, choose File, Open - when in 'LIST' view, I do not get all my subdirectories displayed. When the view is changed to 'DETAILS', all the subdirectories are displayed. Changed back to "LIST" view, and subdirectories go missing again. However, when in Windows Explorer, all subdirectories are correctly displayed. What is causing this? And how to fix? (Windows 2000 with Office 2000 installed, all service packs installed). Oak, Could it be you need to scroll? Look for both horizontal and vertical scroll bars. -- Earl Kiosterud www.smokeylake.com...

How do I disable the wrap text format in Excel
I need to know how to disable the wrap text function after im done with it.I only wanted to extend for a couple of letters without stacking them on top of each other. Format Cells choose alignment tab at the bottom, uncheck wrap text "Dawn M. Gray" wrote: > I need to know how to disable the wrap text function after im done with it.I > only wanted to extend for a couple of letters without stacking them on top of > each other. Wrap Text works only on entire cells, not on pieces of text in a cell. Perhpas re-word your description. There may be something else you can d...

PST file #10
Hi, I am having some problems with Outlook and was hoping somebody could help out. I just got a new PC with outlook. The person who built it for me told me to delete the existing PST file and cut and paste my old PST file in its place so that I could get access to my old emails and contacts. this worked, however I am now having problems when I go to send an email or check a name. The system keeps saying that the contacts file has been moved or deleted? When I go into the contacts icon all the contacts are there, but it can't find them when I go through the address book. Can anyb...

Sending files to a commercial printer
I'm having some problems getting a book to print with proper shades of gray formatting in tables and text boxes. Long story short, my printer has told me that the formatting for gray can be between 10 & 15 %. Could anyone tell me what the RGB values have to be for grey scale to print with these values (10 or 15%)? I can't find a reference anywhere. Currently, I am using 166,166,166. My best guess is that's around 20%, but when I get the material back from the printer, the gray scale, even at that level is washed out. I know, time to look for a new printer. but for t...