By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,903 Members | 2,036 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,903 IT Pros & Developers. It's quick & easy.

Exporting queried results to fixed width text file

P: 97
Hi,

I have the following query to export queried results into a text file, comma delimited. Now I need to export it into a text file, but with fixed length, not with commas. Can anybody help me on this? Thanks in advance!

Expand|Select|Wrap|Line Numbers
  1.       strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\Access_data\\miha2008]." & _
  2.                 outfilename & " FROM queryoutput;"
  3.          Debug.Print strSQL
  4.          DoCmd.RunSQL strSQL
  5.  
Apr 22 '08 #1
Share this Question
Share on Google+
18 Replies


Expert Mod 2.5K+
P: 2,545
Hi loisk. Exporting to a fixed-width text file can be done using VB code. An example of how it can be done is shown in post 2 of this thread:

http://bytes.com/forum/thread781345.html

-Stewart
Hi,

I have the following query to export queried results into a text file, comma delimited. Now I need to export it into a text file, but with fixed length, not with commas. Can anybody help me on this? Thanks in advance!

Expand|Select|Wrap|Line Numbers
  1.  strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\Access_data\\miha2008]." & _
  2. outfilename & " FROM queryoutput;"
  3. Debug.Print strSQL
  4. DoCmd.RunSQL strSQL
  5.  
Apr 23 '08 #2

P: 97
Hi loisk. Exporting to a fixed-width text file can be done using VB code. An example of how it can be done is shown in post 2 of this thread:

http://bytes.com/forum/thread781345.html

-Stewart
Thank you, Stewart!
There were something that I don't understand in the link you suggested due to my lack of VB knowledge. So I am wondering if there is anyway that I can obtain the result I want by editing the line above, something like...[Txt;FMT=Fixed;HDT=YES;DATABASE=.....]?
Apr 23 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi loisk. Ahh, if only it was that simple... Whilst there is more than one way to export data, including to a delimited text file format (have a look at the options available using DoCmd.OutputTo, for instance), if you truly need a fixed-width text file (non-deliimited) you will have to do this using VB code as listed in the thread I linked.

If you think about it, to use a fixed-width format you must define the width every field will take when output, and the format which will be used to represent all non-text data types: dates, numerics, booleans, currency, long integers, doubles and so on. Each and every field requires a bespoke format - not complex to code, but not easy to do any other way.

Access can export to a fixed-width text format, using the Export wizard to do so (which will create the format for each field). Perhaps this is your best option if you are unable to code a solution.

-Stewart
Apr 23 '08 #4

P: 97
Hi loisk. Ahh, if only it was that simple... Whilst there is more than one way to export data, including to a delimited text file format (have a look at the options available using DoCmd.OutputTo, for instance), if you truly need a fixed-width text file (non-deliimited) you will have to do this using VB code as listed in the thread I linked.

If you think about it, to use a fixed-width format you must define the width every field will take when output, and the format which will be used to represent all non-text data types: dates, numerics, booleans, currency, long integers, doubles and so on. Each and every field requires a bespoke format - not complex to code, but not easy to do any other way.

Access can export to a fixed-width text format, using the Export wizard to do so (which will create the format for each field). Perhaps this is your best option if you are unable to code a solution.

-Stewart
Hi Stewart,

I will try to do according to your suggestion, but meanwhile I have tried the following and, somehow, it worked for me.

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * INTO [Text;FMT=Fixed;HDR=YES;DATABASE=D:\\Access_data\\miha2008]." & _
  2.                 outfilename & " FROM queryoutput;"
  3.  
  4.          Debug.Print strSQL
  5.          DoCmd.RunSQL strSQL
The output text file reads like this....

"Caseid","Batch","Outcome","Spanish","Saqlang","Ty pecomp","mailq3","intvtrac"
21673 330 0 0 0 0
21683 330 0 0 0 0
21693 330 0 0 0 0
21703 330 0 0 0 0
21713 330 0 0 0 0
21723 330 0 0 0 0
21733 330 0 0 0 0

What would you say...?
Apr 24 '08 #5

NeoPa
Expert Mod 15k+
P: 31,613
This looks like you're outputting in delimited format still.
Are you looking for doing this manually or must it be by code?
Apr 25 '08 #6

Expert Mod 2.5K+
P: 2,545
Hi. As NeoPa said, what you show us still looks delimited. If you manually export your data as Text the Export Wizard will guide you about setting the fixed-width fields.

I am not able to replicate the SQL SELECT INTO [Text...].outfilename etc that you have used - my Access 2003 test system does not recognise the qualified export syntax, and the Help info for SELECT..INTO does not list this form at all. I have seen it crop up in a previous thread where My SQL was being used, but not for native Access tables. See Help extract below for the syntax listed by MSHelp for SELECT INTO.

What version of Access are you using?

-Stewart
SELECT...INTO Statement
Creates a make-table query .

Syntax
SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source
Apr 25 '08 #7

P: 97
Hi. As NeoPa said, what you show us still looks delimited. If you manually export your data as Text the Export Wizard will guide you about setting the fixed-width fields.

I am not able to replicate the SQL SELECT INTO [Text...].outfilename etc that you have used - my Access 2003 test system does not recognise the qualified export syntax, and the Help info for SELECT..INTO does not list this form at all. I have seen it crop up in a previous thread where My SQL was being used, but not for native Access tables. See Help extract below for the syntax listed by MSHelp for SELECT INTO.

What version of Access are you using?

-Stewart
Hi NeoPa & Stewart,

Thank you for your replies!
I am using Access 2000. I don't even remember where I got the export syntax. I have searched it Access Help, but I couldn't find anything I need. Probably I must have gotten it from one of googled links. I will use what Stewart showed me and will get back to you. Thanks!
Apr 25 '08 #8

P: 97
Hi NeoPa & Stewart,

Thank you for your replies!
I am using Access 2000. I don't even remember where I got the export syntax. I have searched it Access Help, but I couldn't find anything I need. Probably I must have gotten it from one of googled links. I will use what Stewart showed me and will get back to you. Thanks!
By the way, Stewart, My Access form is linked to MySQL table via ODBC.
Apr 28 '08 #9

P: 97
By the way, Stewart, My Access form is linked to MySQL table via ODBC.
I don't think that I did answer to NeoPa's question above.
Yes, it's gotta be by code.
Apr 29 '08 #10

NeoPa
Expert Mod 15k+
P: 31,613
I don't think that I did answer to NeoPa's question above.
Yes, it's gotta be by code.
Lois,

The way I do it, even in code, is to set up the Import/Export spec manually first anyway using the wizard (and the advanced spec control too).
Then I use :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.TransferText(TransferType:=acExportFixed, _
  2.                 SpecificationName:="Blah blah Spec", _
  3.                 TableName:="qryBlahBlah", _
  4.                 FileName:="C:\Temp\BlahBlah.Txt", _
  5.                 HasFieldNames:=False)
Apr 30 '08 #11

P: 97
Lois,

The way I do it, even in code, is to set up the Import/Export spec manually first anyway using the wizard (and the advanced spec control too).
Then I use :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.TransferText(TransferType:=acExportFixed, _
  2.                 SpecificationName:="Blah blah Spec", _
  3.                 TableName:="qryBlahBlah", _
  4.                 FileName:="C:\Temp\BlahBlah.Txt", _
  5.                 HasFieldNames:=False)
Thanks, NeoPa!

I tried the above by using manual menu, the export of file menu, but it did not serve my need since I need to make the output columns right-aligned. I have looked into carefully if there's any way that I can manipulate the columns' alignment in generating the specification, but it seems there's no way to get it.
This output text file will be used in SPSS later and it's got to be right-aligned.

At this point I think that I need follow the link Stewart provided. I appreciate your help, NeoPa!
Apr 30 '08 #12

Expert Mod 2.5K+
P: 2,545
Hi loisk. I am not sure what field types you are finding are not aligning as expected; if the fields appear to be numeric but are being left-aligned a type conversion to string may have taken place. In a query it is always possible to format a field as necessary using the format function and other in-built techniques to provide custom formats, ensuring that the data is exported correctly.

I mention this because you have already investigated TransferText as NeoPa suggested, and it is less work (by far) to use an output specification with this than it is to code a bespoke solution, even though the bespoke solution allows you the most flexibility. It's a big step if you are not used to coding in VB.

-Stewart
Apr 30 '08 #13

NeoPa
Expert Mod 15k+
P: 31,613
What he said!

By that I mean I'm with Stewart on this. This can be a little fiddly, but try creating a query first which outputs your data in the format you require. This way you can play around and get the results needed before getting into the newer territory of I/O specs.

Format is a very flexible and powerful function :)
May 1 '08 #14

P: 97
What he said!

By that I mean I'm with Stewart on this. This can be a little fiddly, but try creating a query first which outputs your data in the format you require. This way you can play around and get the results needed before getting into the newer territory of I/O specs.

Format is a very flexible and powerful function :)
Hi Stewart and NeoPa!

Ok, here's my code that I did dig out from MS support, but I was errored of the type mismatch.
All of my fields is number, except one, Caseid, but I made all the number datetypes to string in order to specify the width of the field, it creates an error. My dilema here is that how to reconciliate this conflict in terms of keeping my datatype and creating the fixed-width (and right aligned) text file.

What would you say....?

Expand|Select|Wrap|Line Numbers
  1.   Option Compare Database
  2.  
  3. Public Function CreateTextFile()
  4.  
  5. 'This function creates a fixed-width text file using the queryoutput table.  All the fields will be right-aligned.
  6.  
  7. Dim strCaseid As String * 4  'width of 4 char
  8.   Dim strBatch As String * 2
  9.   Dim strOutcome As String * 2
  10.   Dim strSpanish As String * 1
  11.   Dim strSaqlang As String * 1
  12.   Dim strTypecomp As String * 1
  13.   Dim strMailq3 As String * 1
  14.   Dim strIntvTrac As String * 1
  15.  
  16.   Dim mydb As DAO.Database, myset As DAO.Recordset
  17.  
  18.   Set mydb = CurrentDb()
  19.   Set myset = mydb.OpenRecordsetOpenRecordset("queryoutput", dbOpenTable)
  20.  
  21.   myset.Index = "PrimaryKey"
  22.   intFile = FreeFile
  23.  
  24.   Open "D:\\Access_data\\miha2008\\Output.txt" For Output As intFile
  25.  
  26.   myset.MoveFirst
  27.   Do Until myset.EOF
  28.      RSet strCaseid = myset![Caseid]
  29.      RSet strBatch = Format(myset![Batch])
  30.      RSet strOutcome = Format(myset![Outcome])
  31.      RSet strSpanish = Format(myset![Spanish])
  32.      RSet strSaqlang = Format(myset![Saqlang])
  33.      RSet strTypecomp = Format(myset![Typecomp])
  34.      RSet strMailq3 = Format(myset![Mailq3])
  35.      RSet strIntvdate = Format(myset![Intvdate])
  36.      'concatenate all of the variables together
  37.      Print #intFile, Caseid & Batch & Outcome & Spanish & Saqlang & Typecomp & Mailq3 & Intvdate
  38.      myset.MoveNext
  39.   Loop
  40.  
  41.   Close intFile
  42.   myselt.Close
  43.   mydb.Close
  44.  
  45.   MsgBox "The text file has been created!"
  46. End Function
May 1 '08 #15

Expert Mod 2.5K+
P: 2,545
Oh dear, loisk, you have many, many syntax errors and misunderstandings in the code. I don't wish to dishearten you, but this would (a) need rewritten, and (b) really be better done using a query exported by the TransferText method previously discussed.

You've certainly put effort into this, and I do not want to see it go to waste, but I think you may not have as full an understanding of what is needed to output fields in fixed width to do it in code. I am sorry that I cannot be more positive. Within the loop, if you remove the incorrect format statements and so on you are not far off creating strings that when concatenated would give a fixed-width text string output as required, but I can't tell if what you are outputting is anything like what it should be.

I will flag some of the errors below, but there are too many for me to fix (and too much that I can't guess at which is missed out). I also don't see any evidence of right alignment - most of the fixed-width strings are just 1 character long.

-Stewart
Expand|Select|Wrap|Line Numbers
  1. Dim strCaseid As String * 4 'width of 4 char
  2. Dim strBatch As String * 2
  3. Dim strOutcome As String * 2
  4. Dim strSpanish As String * 1 
  5. Dim strSaqlang As String * 1
  6. Dim strTypecomp As String * 1
  7. Dim strMailq3 As String * 1
  8. Dim strIntvTrac As String * 1
  9.  
  10. Dim mydb As DAO.Database, myset As DAO.Recordset
  11.  
  12. Set mydb = CurrentDb()
  13. Set myset = mydb.OpenRecordset("queryoutput", dbOpenTable) '< removed repeated OpenRecordset
  14.  
  15. myset.Index = "PrimaryKey" '< not needed
  16. intFile = FreeFile '< not sure what this is
  17.  
  18. Open "D:\\Access_data\\miha2008\\Output.txt" For Output As intFile
  19.  
  20. myset.MoveFirst
  21. Do Until myset.EOF
  22. strCaseid = myset![Caseid] '< use of RSet is wrong - removed as example but remove all
  23. RSet strBatch = Format(myset![Batch]) '< incorrect use of format statement - not doing anything with it
  24. RSet strOutcome = Format(myset![Outcome])
  25. RSet strSpanish = Format(myset![Spanish])
  26. RSet strSaqlang = Format(myset![Saqlang])
  27. RSet strTypecomp = Format(myset![Typecomp])
  28. RSet strMailq3 = Format(myset![Mailq3])
  29. RSet strIntvdate = Format(myset![Intvdate])
  30. 'concatenate all of the variables together
  31. Print #intFile, Caseid & Batch & Outcome & Spanish & Saqlang & Typecomp & Mailq3 & Intvdate '<incorrect names
  32. myset.MoveNext
  33. Loop
  34.  
  35. Close intFile
  36. myset.Close '<misspelt - corrected
  37. mydb.Close
  38.  
  39. MsgBox "The text file has been created!"
  40. End Function
May 1 '08 #16

NeoPa
Expert Mod 15k+
P: 31,613
My response would be :
  1. Never code without the Option Explicit set (Tools / Options / Editor / Require Variable Declaration).
    In this case the absence has left your code with an undeclared variable myset. This is misspelt on line #42 causing an easily avoidable problem. It also makes reading your code harder.
  2. It seems that Stewart warned particularly against the idea of coding this up in VBA. It is very much less flexible and reusable and is also harder to code and understand.
  3. I suggested specifically to create a query to output the data in the format that you want first before continuing on to build a spec for that to output as you want.
You seem to want to ignore our advice, yet want help to follow the path we both advise against. That's a bit like the tail wagging the dog don't you think.

Be under no illusion - this is perfectly possible using the techniques advised.

PS. Stewart beat me to a response. Clearly he's a kinder man than I. I hope my post proves helpful though - I believe more in "blunt".
May 1 '08 #17

P: 97
My response would be :
  1. Never code without the Option Explicit set (Tools / Options / Editor / Require Variable Declaration).
    In this case the absence has left your code with an undeclared variable myset. This is misspelt on line #42 causing an easily avoidable problem. It also makes reading your code harder.
  2. It seems that Stewart warned particularly against the idea of coding this up in VBA. It is very much less flexible and reusable and is also harder to code and understand.
  3. I suggested specifically to create a query to output the data in the format that you want first before continuing on to build a spec for that to output as you want.
You seem to want to ignore our advice, yet want help to follow the path we both advise against. That's a bit like the tail wagging the dog don't you think.

Be under no illusion - this is perfectly possible using the techniques advised.

PS. Stewart beat me to a response. Clearly he's a kinder man than I. I hope my post proves helpful though - I believe more in "blunt".
Please don't misunderstand me. I wasn't trying to ignore your advice. In fact I was trying to follow the way of the link Stewart gave me in the beginning of this post and there were some lines that I couldn't understand. I found similar method to that of Stewart's suggestion in MS Support website, and I took it and modified it. I couldn't find the way to make my fields right-aligned using TransferText function. Let me try both and I will return to you. I'd like to thank both of you!
May 2 '08 #18

NeoPa
Expert Mod 15k+
P: 31,613
Clearly my "blunt" can be inappropriate at times. I forget that things can be harder to understand than I assume.

I would just reiterate that, in my opinion, you will find it easier, and probably learn a more useful technique, if you follow the "TransferText of a query" path.

Let us know how you get on anyway and I'll try to be helpful whichever path you choose.
May 2 '08 #19

Post your reply

Sign in to post your reply or Sign up for a free account.