Rocket Software Homepage
Forum Home Forum Home > AccuTerm Knowledge Base (read only) > File Transfer
  New Posts New Posts RSS Feed - Access Field Size
  FAQ FAQ  Forum Search   Register Register  Login Login

The AccuTerm forum has moved. Go to community.rocketsoftware.com to register for the new Rocket forum.

Forum LockedAccess Field Size

 Post Reply Post Reply
Author
Message
bneylon View Drop Down
Senior Member
Senior Member


Joined: March 03 2004
Location: United States
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote bneylon Quote  Post ReplyReply Direct Link To This Post Topic: Access Field Size
    Posted: August 13 2004 at 3:11am
We use FTD to load data into an Access database. We do this weekly. For some reason, this week, I am getting the message that I am trying to load more data that allowed into col21. When I look at the design the length of col21 is 4. This causes 90% to error out. If I rerun last weeks file, things work fine, col21 is field size 12. How does Accuterm determine the field size and how can I force a field size?
The file has 164 fields.
my code:
   EXECUTE 'SSELECT ':PICK.FILE:' BY RID'
   DATA 'O','2=Y',''
   DATA 'S'
   DATA 'K'
   DATA PICK.FILE
   DATA ''
   DATA DIR:SUBDIR:FILE
   DATA '*'
   DATA 'N'
   EXECUTE 'FTD'
FILE is '\DCDMH.MDB!Xpatient table'
If you need any more info, please email me.
Any ideas?

Thanks,
Bruce

Back to Top
jgold View Drop Down
Moderator Group
Moderator Group

Moderator

Joined: December 29 2003
Location: United States
Status: Offline
Points: 48
Post Options Post Options   Thanks (0) Thanks(0)   Quote jgold Quote  Post ReplyReply Direct Link To This Post Posted: September 17 2004 at 7:11am
Hi Bruce,

The AccuTerm converter goes through several steps to try and determine the column size and data type. It basically goes through the entire file checking each column and sets the field size to the length of the longest string multiplied by 1.25. So, if the longest string is 40 characters, the column size should be set to 50. It ignores any fields that it thinks are numbers or dates and the length is not allowed to go beyond 255 characters.

It only applies the field types if the converter is creating a new table. If there's an existing table, it assumes that the fields are already set correctly and doesn't change them. If you're downloading into an existing table, you'll just need to increase the field sizes. If the table doesn't already exist, it's hard to say what might be causing the problem. You could send a copy of your data downloaded to a tab delimited file and we can run it through the converter and figure out what's going on.

Hope this helps,

Joe Goldthwaite
Back to Top
bneylon View Drop Down
Senior Member
Senior Member


Joined: March 03 2004
Location: United States
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote bneylon Quote  Post ReplyReply Direct Link To This Post Posted: September 27 2004 at 8:46am
These are new tables.
It would be impossible to send you the data. The transfers are sensitive, patient and claim information. Also, quite large. I haven't been able to duplicate the problem in the test account, possibly due to the small volume there.
The problem occured today on the patient table(4007 patients) I ended up transfering to an excel spreadsheet and then cut and pasting to the access database.

Thanks,
Bruce
Back to Top
jgold View Drop Down
Moderator Group
Moderator Group

Moderator

Joined: December 29 2003
Location: United States
Status: Offline
Points: 48
Post Options Post Options   Thanks (0) Thanks(0)   Quote jgold Quote  Post ReplyReply Direct Link To This Post Posted: September 28 2004 at 3:03pm
I'm not sure how much data we're talking about but if it's not TOO much, you could try this.

1. Create a test file and copy a representative amount of data into it. Call the file "TEST.DATA". Make sure you don't have any existing files called "TEST.DATA".

2. Copy and paste this program below into one of you BP files.

3. Run the program. This will convert all A-Z and a-z characters to a pound sign. It will also convert any number characters 0-9 to 9. That should hide any confidential information.

4. Download TEST.DATA into a tab delimited file, zip it up and send it in. We'll try to duplicate the problem and fix it.

---Program to convert the data below---
FILENAME = "TEST.DATA"
OPEN FILENAME TO FILE ELSE STOP 201,FILENAME
SELECT FILE
EOF = 0
LOOP
   READNEXT ID ELSE EOF = 1
UNTIL EOF DO
   READ ITEM FROM FILE, ID THEN
      NEW = ''
      LN = LEN(ITEM)
      FOR X=1 TO LN
        CHR = SEQ(ITEM[X,1])
        IF CHR >= 65 AND CHR <= 122 THEN
               CHR = '#'
        END ELSE
               IF CHR >= 48 AND CHR <= 57 THEN
                  CHR = '9'
               END ELSE
                  CHR = CHAR(CHR)
               END
        END
        NEW = NEW : CHR
      NEXT
      WRITE NEW ON FILE, ID
   END
REPEAT
PRINT 'DONE'
Back to Top
jgold View Drop Down
Moderator Group
Moderator Group

Moderator

Joined: December 29 2003
Location: United States
Status: Offline
Points: 48
Post Options Post Options   Thanks (0) Thanks(0)   Quote jgold Quote  Post ReplyReply Direct Link To This Post Posted: September 30 2004 at 6:04pm
Hi Bruce,

Thanks for the sample data. I think I traced the problem down to a bug! It was causing the conversion program to choke on column 16. The converter loops through the entire file and checks each column to try and determine the data type. If the field made up of all numeric characters, it tries to determine the data type by converting the numeric string to a long integer. The maximum number for a long is 2147483647 so if any of the nine digit numbers in column 16 exceed the maximum, it generates a run time error. The result is that the data file is not created with the right column numbers or types.

I sent the fix to Pete so he could test it and incorporate it into the next AccuTerm release. He should be sending you an update soon.

Thanks for working with us on this.

Joe Goldthwaite
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.