Access CSV

[neilhunt65]

Hi, I hope someone can help
I have imported data into Access 2000

In the design view, I formatted my fields to be specific lengths, IE
the import is 123456, the data is now 00123456 in the Access Table. My
problem is I need to export this in a CSV format. When I try this the
export drops my leading zeros. I also set an auto generate number, to
start at 30000000, and the export drops all the leading numbers, except
for the last ones, IE 30000001 becomes 1 on the CSV Export.
I cannot use Excel as I have 4.5 million records.

Any suggestions

Thanks

Neil Hunt

 

[tina]

formatting in table design affects only how data is *displayed*, it does not
affect how the data is *stored*. if a stored value is 123456, that's what
you're going to get when you export the table.

try exporting a query, rather than the table. in the query design, apply
explicit formatting to the fields that are giving you trouble. for example,
the "000123456" field would be formatted as

FieldNameX: Format(FieldName, "00000000")

when you use the Format() function, it changes the data to text (only in the
query dataset - the stored table data is NOT changed), and that text value -
including formatting characters - is what is exported.

hth

 

[neilhunt65]

Tina

Thanks for the help, but tried that and even formatting the fields in
the query has the same result, as does changing the field from number
to text.
Any other suggestions?

Thanks again

Neil

 

[Rick Brandt]

Tina

Thanks for the help, but tried that and even formatting the fields in
the query has the same result, as does changing the field from number
to text.
Any other suggestions?

Tina did not suggest using the format *property*. She suggested the Format()
*Function* which will produce a string output in the desired format that WILL
export (unlike the property).

 

[tina]

thanks Rick - i was about to tell Neil that he had me flummoxed! <g & b>

 

[neilhunt65]

Tina / Rick

Apologies, read the reply too fast.
Thanks for your help

Neil

 

[tina]

you're welcome