leading zeros disappear from numbers when i export csv text file 1

[butcher]

if i export this...
"RecordStart","02-04-06",10,1012,0,"RecordEnd"
"RecordStart","03-04-06",8,0000,0,"RecordEnd"
the 0000 turns to 0 - and it MUST be a number.

i have another Number that reports seven '3digit values' joined together i.e. 001002003004005006007
this number needs leading zeros (or 21 zeros if no values)

the value is stored in table where the field is formatted to Decimal; Precision 21; DecimalPlaces 0;
the 21 digits appear fine in the export query

as soon as i exoprt the number the leading zeros are gone and any large values appear as scientific

i use an Export Specification file but i can't see any option to format an individual field in there.

if i try using format in the export query then i get "" wrapped around the number.

any tips appreciated
butcher

 

[formerTexan]

Butcher,

Your data on arrival is conforming to the data type you have set. Formatting in mid-stream (in the query) has no bearing on this. To maintain a "non-numeric" number with leading zeros, set the destination field's data type to text rather than numeric/decimal.

You didn't indicate why you needed to maintain a numeric data type, but unless you plan doing direct arithmatic calulations on the field contents, I don't think you need worry. Plus you can always do a data type conversion later if necessary.

Cheers,
Bill

 

[butcher]

thanks Bill
i do not know why the fields have to be numeric;
it is part of the transmission protocol to a national collection agency.
no chance of changing that part i'm afraid

butcher

 

[formerTexan]

Butcher,

I'm out of good ideas then, but have you tried sending the data as text/string to see what happens?

On a related note, you might want to try and find out what sort of database/language your data is headed to and find out specifically what the data type specifications are for the "numeric" field. This might give you some clue as to how to handle your data.

Cheers, Bill

 

[CautionMP]

butcher,
I haven't tried the recomendation of [navy]formerTexan[/navy], if it works that's the way to go (keep it simple s...).
Another option might be to create the output file in VBA (instead of using the built in functionality). The following thread has a generic routine for doing this with the benefit of giving you complete control over how the data appears in the file. It uses [tt]TypeName[/tt] to determine how to output the fields but you could use a custom format based on field name. [navy]Microsoft: Access Modules (VBA Coding) Forum:[/navy] Exporting CSV file via VBA

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[butcher]

bill,
thanks again but their idea of a number differs from mine; especially when it is zero. however, i must conform to their protocol - no correspondence entered into etc.


CautionMP,
thanks heaps, i get the idea and i will try that code,it seems to offer more control.
plus - who can resist a little looped concatenation?

 

[CautionMP]

butcher,
P.S. [navy]JerryKlmns[/navy] pointed out that I made a mistake on the original post, [tt]arrTest[/tt] in the concatenation piece should be [tt]MyRecord[/tt].

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[butcher]

CautionMP,
i replaced arrTest with MyRecord
but the error terror stops me here...

While Not MyRecordset.EOF
MyRecord = MyRecordset.GetRows(1)
For intField = 0 To UBound(MyRecord)

' i can't get past this next line
Select Case TypeName(MyRecord(intField))

the error is - Subscript out of range
any suggestions?
cheers, butcher

 

[CautionMP]

butcher,
Oops, [tt]GetRows[/tt] returns a two-dimensional array. Missed that. Here is and updated routine:

Dim MyDatabase As DAO.Database
Dim MyRecordset As DAO.Recordset
Dim MyRecord() As Variant
Dim intFile As Integer, intField As Integer
Dim strRecord As String

Set MyDatabase = CurrentDb
Set MyRecordset = MyDatabase.OpenRecordset("SELECT * FROM [i]SomeTable[/i];")

intFile = FreeFile
Open "C:\Test.csv" For Output As #intFile

While Not MyRecordset.EOF
  MyRecord = MyRecordset.GetRows(1)
  For intField = 0 To UBound(MyRecord)
    Select Case TypeName(MyRecord(intField[b], 0[/b]))
      Case "Date"
        strRecord = strRecord & "#" & Format(MyRecord(intField[b], 0[/b]), "mm/dd/yyyy") & "#"
      Case "String" 'wrap in double quotes, remove double quotes from text
        strRecord = strRecord & Chr(34) & Replace(MyRecord(intField[b], 0[/b]), Chr(34), Chr(39)) & Chr(34)
      Case Else
        strRecord = strRecord & MyRecord(intField[b], 0[/b])
    End Select
    strRecord = strRecord & ","
  Next intField
  Print #intFile, strRecord
  strRecord = ""
Wend

Clean_Up:
Close #intFile
MyRecordset.Close
MyDatabase.Close

Sorry about that,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[butcher]

CautionMP
that truly is a beautiful thing

i get my 21 digit number with leading zeros and without inverts

thanks to you and every one who helped
butcher,
tasmania