Toad Data Point Truncating Fixed Width .fil


When trying to load a .fil fixed width file with Toad Data Point 5.5 and 6.2, TDP only brings in data in ~850 of the columns. There are 2,500 columns total in the file.

Toad for Oracle does not have this problem. It brings in all of the columns to break down into the fixed-width fields. I am not sure if there’s a setting or something I need to change to bring in all of the data.

One additional note - the preview data under the fixed width selection is a bit odd but the actual data in the fixed width selection looks fine. The preview data is taking row 1 and removing a character every line like
0123456 John Smith 5…
123456 John Smith 5…
23456 John Smith 5…
3456 John Smith 5…

Any thoughts?

Hi Matt,

To clarify, are we dealing with 2500 records or 2500 columns in this scenario?

Could you please confirm the exact workflow? Is the data import functioning correctly? Which table (data source) are you importing into, and how many columns does this table have?

Can this entire workflow be seamlessly executed in Toad for Oracle?

Additionally, what is the size of the file you're attempting to load, and is it stored locally?

If it helps, would converting the file to .txt or another supported format in Toad Data Point be a feasible workaround?

I recommend opening a support ticket so our team can thoroughly investigate and address this issue. Including detailed information about your process and any relevant files will assist us in resolving this promptly. Thank you!

2500 represents the number of characters each record has. The import file is fixed width with a lot of instances of blanks. (1-10 may be one field, 11-12 blank and not imported, 13-16 may be one field, then it may be blank for 100 characters and not imported, then characters 115-140 may be a field, etc.) This goes on for about 150 fields total. (There are probably 900k records.)

The data import looks like it is working correctly to start, but when selecting how the fixed width data is spaced, it only brings in 850/2500 characters, so it only shows the left 850 characters to select and I cannot get the data on the remaining 1650 characters to the right side. Additionally, like I mentioned above, there is a preview at the bottom of the screen of how the data will look when it is imported, and it has this bizarre format where it looks like it imports the first record over and over again, but with one fewer character each time.

Yes, Toad for Oracle shows all of the possible characters, and sectioning the data fields seems to work fine. I currently am using Task Scheduler to import this daily with no issues. However, I’m needing to switch this over to Toad Data Point.

As for the table the data is being imported into - it also has the required 150 fields. However, since I only see about 850 of the 2500 characters, if I try to go to the step of actually importing the data, I only do see the fields on the left of the dataset that were being populated in the fixed width selection step (about fifty fields), and the right 100 fields just show as Null since I wasn’t able to section them into data fields in that prior step.

Toad Data Point does not seem to support .txt fields being fixed width. It automatically tries importing them as delimited. Since my data has all these sections of blank records, it gives an error that says something like “Nulls are found, cannot import” and doesn’t even give the option to try to import as fixed width. You only seem to be able to recognize a .fil file as a fixed width import. (A couple google searches seemed to confirm that .fil was the way to go for fixed width uploads.)

Unfortunately, I wouldn’t be able to send the actual data set, as it is confidential information. I could see if I could make a similar file as an example.

Let me know if this was any help or if I should make a ticket.

Thank you for providing insights into the complexity of the file you're working with.

To address this effectively, please open a support ticket and include key details. If possible, sharing a similar example file and a workflow video would greatly assist our investigation and enable us to provide more targeted assistance.

We appreciate your cooperation. Thank you!