Table Export to .txt or .csv in Access 2002

[Guest]

Hi

I try to export a whole table with 145 fields and approx. 350 records as .txt or .csv file and receive the folowing error message : 'The Field XYZ contains a start position of '32777'. The maximum start position allowed is '32767'
This field XYZ doesn't contain data at all. The table is created via a Make-Table-Query

Export in Excel format is no problem, but then I have to save as .csv again and have probably to re-format some fields/columns. Another thing is, that I would like to have the pipe symbol as field delimiter, which is not supportet in Excel while saving as .csv file. Therefore I have to open the file again in Notepad and have to replace ',' by '|' and some fields could contain a ',' in its data

I have another database a table where I have more columns and records in and it is no problem extracting the data in .txt or .csv format. There are the same columns in there plus some additional ones

Any help and suggestions are very much appreciated

Regards
Bern

(Remove -N-O-S-P-A-M from the e-mail adress, in case you want to e-mail me.)

 

[John Nurick]

Hi Bernd,

I don't know exactly what's happening, but here's a possible cause of
the problem. First, two facts (as far as I can tell):

1) Text fields created by make-table queries have a field size of 255
characters.

2) 32767 characters are sufficient for only 128 fields of 255
characters. (You won't be able to fill all these fields without hitting
the 2000-character limit on the size of an indiviual record, of course.)

So if you try to export your 145 fields, with a total *potential* length
greater than 32767, to a fixed width file it will fail in the way you
describe.

If you're importing to a delimited text file I'm surprised this is
happening; but the above reasoning suggests that you might avoid the
problem by reducing the sizes of your text fields.

 

[Guest]

Hi John

Do you have an idea how to do a bulk change of the text field size or change settings for the make-table query
90 percent of the fields are empty and the make-table query set them up as text field. Changing the settings in Tools -Options - Tables/Queries : Default field sizes : Text = 20 and Default field type = Number doesn't help, still text fields with 255 chars

Due to that I have to add another table via append query to my first table I can't delete some fields or it would be difficult to do. The exported data is loaded into another system and it expects the file in a specific format and number of fields

Thanks
Bernd

 

[John Nurick]

Hi Bernd,

Instead of using a make-table query I'd create the table first with
appropriate field types and sizes, and then run an append query to get
the data into it.

You can do this by converting the make-table query into an append query
(I'll call it qryA). Then use something like this untested air code to
create the table:

Dim dbD As DAO.Database
Dim qdQ As DAO.QueryDef
Dim tdT As DAO.TableDef
Dim fldQ As DAO.Field, fldT As DAO.Field

Set dbD = CurrentDB()
Set qdQ = dbD.QueryDefs("qryA")
Set tdT = New DAO.TableDef

For Each fldQ in qdQ.Fields
Set fldT = New DAO.Field
fldT.Type = fldQ.Type
fldT.Size = fldQ.Size
'or set the size some other way
'if it's a text field
fldT.Name = fldT.Name
...
tdT.Fields.Append fldT
Next

dbD.TableDefs.Append tdT

Set tdT = Nothing
Set qdQ = Nothing
....

 

[Guest]

since this thread is about as close to what i think i want as time permits me to know i hope you don't mind if i join...

i'm exporting some height, weight and dosage data into a csv text file from a query using ms a2k (not 2002!)...

i want my height and weight data to be exported as numbers, i.e. without being surrounded by quotation marks, and expressed in terms of a single digit of precision following the decimal point. dosage, ditto numerical like height and weight, but with three digits of precision following the decimal

i'm using expr1: Round([Dosage],3)
expr2: Round([Height],1
expr3: Round([Weight],1

but in each instance the values i see in the csv export text file have TWO significant digits of precision!??!

can there be no control over the how many significant digits apply to different fields in the export csv file?

 

[Douglas J. Steele]

Rounding a number doesn't actually change its value, it just changes how
it's displayed.

Try using the Format function to actually convert the number to a string
with the correct number of digits.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

since this thread is about as close to what i think i want as time permits

me to know i hope you don't mind if i join....

i'm exporting some height, weight and dosage data into a csv text file

from a query using ms a2k (not 2002!)....

i want my height and weight data to be exported as numbers, i.e. without

being surrounded by quotation marks, and expressed in terms of a single
digit of precision following the decimal point. dosage, ditto numerical like
height and weight, but with three digits of precision following the decimal.

i'm using expr1: Round([Dosage],3)
expr2: Round([Height],1)
expr3: Round([Weight],1)

but in each instance the values i see in the csv export text file have TWO

significant digits of precision!??!!

can there be no control over the how many significant digits apply to

different fields in the export csv file?