Export Table Pipe Delimited

[Kiwiman]

Howzit

I am having trouble with exporting data from a table to a pipe delimited txt file.

When I use the following code, I get a merging of the 17th \ 18th record. The data in the table looks ok. I have used different datasets when exporting but always the same result.

DoCmd.TransferText acExportDelim, strExport, stDocName, strFileName, True

however when I use the following, that does not have column headers, the data extracted looks ok

DoCmd.TransferText acExportDelim, strExport, stDocName, strFileName, False

Has anyone seen this before and can you offer any solutions to this. I have tried deleting the specification and recreating but I get the same result.

I attach a cut down version of this database, should you wish to look at it, plus the two different files it creates (Hdgs or no Hdgs)

Thanks very much.

 

[vbaInet]

I believe it's following whatever you had in your specification. If you defined it to have column headings in your specification then when you put TRUE in that part of the export paramter it will subtract 2.

 

[Kiwiman]

Howzit

The specification was set up as having no column headings (source file) - however I specified the column headings top be used in the Advanced set up section.

I'm more confused as to why the data is merging when the Column Headings are included in the text file.

 

[vbaInet]

I think the best way to get round this is to create another export spec that includes headings, and use the right export spec for the right option.

 

[Kiwiman]

Howzit

Yes - tried that. I created a new spec that included column headings. When I use this new specification the file that is created has no Column Headings. Regardless if I choose TRUE or FALSE in the Docmd.transfertext syntax.

The only way that I appear to get Column Headings is to use a specification that was set up with no Column Headings, but I get this merging issue.

 

[vbaInet]

I've just downloaded your db. What do I do to replicate this problem? And what should I be looking out for?

 

[Kiwiman]

Howzit

Thanks for your help.

If you open the form and click the "5 Create Oracle Interface..." button, this will create a txt (pipe delimited) file in the db location. Then change the option box to No and click the button again - this will create another file. If you open both files that are created you will see the one with the headings have wrapping issues about record 17 \ 18, but the the other one does not. The only difference is the inclusion of column headings.

 

[vbaInet]

Open your file with headings and look to the far right (line 17) you will find that it does actually import everything but for some reason adds that line there.

 

[Kiwiman]

Howzit

Yes - thats my problem. It's not writing it to a line of its own - and it is also overwriting a portion of the field that it is being attached to.

 

[vbaInet]

Your table isn't at all normalized and has no primary keys. For the export to work effectively you must have primary keys, something that uniquely identifies each record. I just added an autonumber primary, created a new export spec and it works fine.

 

[Kiwiman]

Howzit

Yes I know the table is not normalised - it was intentional. In saying that, I have even tried making the last field a PK - as this will definitely be unique, and recreating the spec. But it does the same thing, except this time the merge encroaches into the 15th field of record 17\18 - not the 18th field.

The final output should only be the 18 columns with headings. If I put an autonmber on the table and tell the spec to ignore the last field, it comes up with err number 3011 - object not found.

 

[vbaInet]

I believe your ID(s) must come first. Have a look at the attached.

 

[Kiwiman]

Howzit

Sorry, was working on another project over the last few days - so I couldn't reply. I have downloaded this db copy and created the text file, and it does come out ok, except it includes the id column. My final file has to only include the 18 columns.

I have tried modifying the spec to exclude the PK column but I get an error - no object found. When I export a query, no matter waht I do no column headings are exorted. Back to square one.

For now I have created a work around, by:

• Creating another table to hold the column headings I want exported
• Creating a Union query with the data table and the heading table
• Exporting the union query

This gives me my desired result and has got the integration files created with no issues. I will continue to research this as there has to be a reason. Thanks for all your help - appreciated.

 

[vbaInet]

I think Access' export function requires a PK or unique field to correctly export your data. If you want greater flexibility as to what fields should be exported (and in a correct format) then consider writing to the file using vba. All you do is open the file, loop through each row, write that row to the file (ignoring the first column or whichever column holds your PK) and close the file. Lots of examples on File I/O.

You're welcome.