The AccuTerm forum has moved. Go to community.rocketsoftware.com to register for the new Rocket forum. |
Access Field Size |
Post Reply |
Author | |
bneylon
Senior Member Joined: March 03 2004 Location: United States Status: Offline Points: 103 |
Post Options
Thanks(0)
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 |
|
jgold
Moderator Group Moderator Joined: December 29 2003 Location: United States Status: Offline Points: 48 |
Post Options
Thanks(0)
|
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 |
|
bneylon
Senior Member Joined: March 03 2004 Location: United States Status: Offline Points: 103 |
Post Options
Thanks(0)
|
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 |
|
jgold
Moderator Group Moderator Joined: December 29 2003 Location: United States Status: Offline Points: 48 |
Post Options
Thanks(0)
|
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' |
|
jgold
Moderator Group Moderator Joined: December 29 2003 Location: United States Status: Offline Points: 48 |
Post Options
Thanks(0)
|
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 |
|
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |