Exporting to csv or fixed width

Topic

New Reply

I am trying to export a query to a fixed width file. I also tried csv with the same results.

The problem is the first field is 000015452. When exporting it drops the leading zeros. I have tried Format([myfield],”000000000″), I tried converting the field to a text field, among other things. When viewing the query the zeros are there. But when viewing the text file, the zeros have been dropped.

What am I overlooking?

Thanks in advance for any ideas and help.

Ken

Reply | Quote

Replies

Exporting ignores the format set for a numeric column; you must use the Format function or a text field.

When I export from Access 2002 to a text file, leading zeros created by the Format function or in a text field are preserved.

Which application are you using to view the exported file? If I open such a file in Excel, it tries to interpret a column as numeric if possible, so I lose the leading zeros. But if I use Notepad, I do see them.

Reply | Quote

Exporting ignores the format set for a numeric column; you must use the Format function or a text field.

When I export from Access 2002 to a text file, leading zeros created by the Format function or in a text field are preserved.

Which application are you using to view the exported file? If I open such a file in Excel, it tries to interpret a column as numeric if possible, so I lose the leading zeros. But if I use Notepad, I do see them.

Obviously I am doing something wrong then, as I use Notepad to open. I am using Access 2003, and I tried both the Format function as well as exporting as a text field. I even tried hard-coding the data, instead of using the field in the table. The following SQL Statement is an example of the query.
SELECT Format(23396,”000000000″) AS ID, tmpTEST.ICN, tmpTEST.HICN, tmpTEST.I_txtPersonID, tmpTEST.I_txtLastName, tmpTEST.I_txtFirstName, tmpTEST.Gender, tmpTEST.DOB
FROM tmpTEST;

Yet the results drop the leading zeros in the first field.

Reply | Quote

I’m sorry, I can’t explain that.

Reply | Quote

Try changing your query to this:

Code:

SELECT CStr(Format(23396,"000000000")) AS ID, tmpTEST.ICN, tmpTEST.HICN, tmpTEST.I_txtPersonID, tmpTEST.I_txtLastName, tmpTEST.I_txtFirstName, tmpTEST.Gender, tmpTEST.DOB
FROM tmpTEST;

I have encountered several strange issues with the text export function in Access – Yes/No fields are likely to be problematic, as are date fields – but in many cases changing them to Text with the CStr function seems to resolve them. Note however that if you use it on a Null field value, it causes an error.

Reply | Quote

I am trying to export a query to a fixed width file. I also tried csv with the same results.

The problem is the first field is 000015452. When exporting it drops the leading zeros. I have tried Format([myfield],”000000000″), I tried converting the field to a text field, among other things. When viewing the query the zeros are there. But when viewing the text file, the zeros have been dropped.

What am I overlooking?

Thanks in advance for any ideas and help.

Ken

What command do you use to export, OutputTo or TransferText, or maybe some other way?

Reply | Quote

What command do you use to export, OutputTo or TransferText, or maybe some other way?

Right now I am in test mode, so I am not exporting via VBA, but instead export the query manually (Export/Save As Type/Text Files (*.txtl;*.csv;*.tab;*.asc), give it a name. When I exported delimited I set the field widths.

Reply | Quote

What command do you use to export, OutputTo or TransferText, or maybe some other way?

New discovery. If I move the field from the first position in the query to the second, it DOES leave the zeros in.

Weird.

Reply | Quote

New discovery. If I move the field from the first position in the query to the second, it DOES leave the zeros in.

Weird.

Must remember that one lol

Reply | Quote