Exporting queried results to fixed width text file

[loisk]

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!

      strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\Access_data\\miha2008]." & _
                outfilename & " FROM queryoutput;"
         Debug.Print strSQL
         DoCmd.RunSQL strSQL

[Stewart Ross]

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!

 strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\Access_data\\miha2008]." & _
outfilename & " FROM queryoutput;"
Debug.Print strSQL
DoCmd.RunSQL strSQL

[loisk]

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;H DT=YES;DATABASE =.....]?

[Stewart Ross]

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

[loisk]

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.

strSQL = "SELECT * INTO [Text;FMT=Fixed;HDR=YES;DATABASE=D:\\Access_data\\miha2008]." & _
                outfilename & " FROM queryoutput;"
         
         Debug.Print strSQL
         DoCmd.RunSQL strSQL

The output text file reads like this....

"Caseid","Batch ","Outcome","Sp anish","Saqlang ","Typecomp","m ailq3","intvtra c"
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...?

[NeoPa]

This looks like you're outputting in delimited format still.
Are you looking for doing this manually or must it be by code?

[Stewart Ross]

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 externaldatabas e]
FROM source

[loisk]

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!

[loisk]

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.

[loisk]

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.

[NeoPa]

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 :

Call DoCmd.TransferText(TransferType:=acExportFixed, _
		        SpecificationName:="Blah blah Spec", _
		        TableName:="qryBlahBlah", _
		        FileName:="C:\Temp\BlahBlah.Txt", _
		        HasFieldNames:=False)

[loisk]

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 :

Call DoCmd.TransferText(TransferType:=acExportFixed, _
		        SpecificationName:="Blah blah Spec", _
		        TableName:="qryBlahBlah", _
		        FileName:="C:\Temp\BlahBlah.Txt", _
		        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!

[Stewart Ross]

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

[NeoPa]

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 :)

[loisk]

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....?

  Option Compare Database

Public Function CreateTextFile()

'This function creates a fixed-width text file using the queryoutput table.  All the fields will be right-aligned.

Dim strCaseid As String * 4  'width of 4 char
  Dim strBatch As String * 2
  Dim strOutcome As String * 2
  Dim strSpanish As String * 1
  Dim strSaqlang As String * 1
  Dim strTypecomp As String * 1
  Dim strMailq3 As String * 1
  Dim strIntvTrac As String * 1
    
  Dim mydb As DAO.Database, myset As DAO.Recordset
  
  Set mydb = CurrentDb()
  Set myset = mydb.OpenRecordsetOpenRecordset("queryoutput", dbOpenTable)
  
  myset.Index = "PrimaryKey"
  intFile = FreeFile
  
  Open "D:\\Access_data\\miha2008\\Output.txt" For Output As intFile
  
  myset.MoveFirst
  Do Until myset.EOF
     RSet strCaseid = myset![Caseid]
     RSet strBatch = Format(myset![Batch])
     RSet strOutcome = Format(myset![Outcome])
     RSet strSpanish = Format(myset![Spanish])
     RSet strSaqlang = Format(myset![Saqlang])
     RSet strTypecomp = Format(myset![Typecomp])
     RSet strMailq3 = Format(myset![Mailq3])
     RSet strIntvdate = Format(myset![Intvdate])
     'concatenate all of the variables together
     Print #intFile, Caseid & Batch & Outcome & Spanish & Saqlang & Typecomp & Mailq3 & Intvdate
     myset.MoveNext
  Loop
  
  Close intFile
  myselt.Close
  mydb.Close
  
  MsgBox "The text file has been created!"
End Function