Export a table to .txt without hyphens & pipes?

[AtLarge]

I am running into a couple problems at the same time and could use some suggestions:

• I need to export a table to .txt
• I need to have the field names as well as the records
• Some records are part numbers with a leading zero like 043095
• Some records are part numbers with dashes and alpha's like 123456E-789
• I don't think I can use .csv because some records have a part number description uses comma's like "some text, more text, even more text"
• I don't need all the hyphens and pipes for the grid work

The problems I have so far are:

1. Exporting to .txt without formatting doesn't bring over the field names (I know they made it this way) Is there a work around other than exporting the data and then adding all the field names in with a macro in Excel? I can do it but, what a pain.
2. I see dropping of leading zeros = 43095 in .txt and .xlsx
3. Exporting to Excel sometimes scrambles the part numbers and they come out like this 5.91E-09 when it should be 123456E-789 (so I'm losing data)
4. Exporting to Excel also puts qoutes in the description field like "A very large part" (extra clean up necessary)

:banghead:

Could someone tell me the easiest way to get rid of the hyphens and pipes and leave it in .txt form? All four encoding types do the same thing.

Any idea why exporting to Excel would scramble some of the part numbers like my examples above?

TIA

 

[Pat Hartman]

Where is the text file being used? What format did the recipiant tell you he wanted? Does it need to be fixed format?

.csv format is a standard interchange format. It separates fields with comas (other delimiters such as tabs or veritcal bars are an option)and puts quotes around text fields. You will occassionally see "poorly formatted" .csv files and those will have quotes only around text values that contain comas. All other text fields will be unquoted. If this is your situation, Excel might interpret those quotes as being part of the text. The first row can include column names or not. Any program that can read .csv files removes the quotes (from a properly formatted file) because it knows they are part of the formatting and not part of the data.

.txt files are completely custom and are usually fixed length fields with no field separators. They also do not include a first row with column headings because the column names may be too wide for the fixed width. For example if the first column is fixed at three characters, that would be all the room you would have for a column name.

In either case, if the standard export is not working excactly the way you want, you can create a custom export. Start by doing the export manually rather then via code. The dialog gives you options. Click the advanced button to get more options. Save the export spec. Then you can automate the export with TransferText by referring to the export spec you created.

When excel or access show the floating point numbers, that just means that the column is too narrow to show the whole field. Widen the column and the value will display correctly.

If the leading zeros are being dropped, it's because Access or Excel thinks the field is numeric rather than text. So, exporting a query rather than a table will usually solve the problem because you can Format() fields so that they will be read as text.

1. As I said the .txt format does not include column headers.
2. Excel thinks it is smarter than you. Try changing the column format from general to text.
3. Widen the column.
4. Excel preceeds all text columns with a quote. I've never seen it embed quotes even when opening a .csv file which probably contains them around each text column

Post more details regarding what you want rather than what doesn't work.

 

[AtLarge]

Thanks Pat. I was able to figure it out. When exporting I was always using the checkbox "Export data with formatting and layout" at the beginning.

1. Leave that checkbox blank, browse for your path and file name, then click Ok
2. Radio button "Delimited" and Next
3. Radio button Tab
4. Checkbox "Include Field Names on the first Row" and Next
5. Then finish

You get the .txt file with the field names and none of the hyphens or pipes. Also, all the leading zeroes and combination part numbers are intact.

Yay!

Your right about Excel of course and I knew that, it just wasn't clear to me why it was happening and I think it's because the person passing me the data wasn't exporting it right because of the aforementioned problems.

Now I can instruct him on the proper way as .txt and import it to my database and know it's right the first time.

 

[Pat Hartman]

You are asking for a non-standard format which may require your source to do extra programming. The .csv is a more standard format as long as he consistantly encloses text strings in double quotes.