Output to MDB does not trim string fields
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have multiple String fields passing through a data cleansing tool before output to MDB. However, all the fields are output with whitespace to take up the defined size of each field, even if no whitespace exists before. Output to CSV does not show the same issue, so I know the data was trimmed properly with the data cleansing tool. What's the best way to make sure my output to MDB is also trimmed?
Solved! Go to Solution.
- Labels:
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jonathanjong,
Use the Select tool and change the fields to the V_String datatype before exporting.
Sam 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Since the issue doesn't exist when you output to CSV, it must be caused by the MDB system that you're writing to(MS Access?). Is there some VBA defined within the database that's padding the fields?
Another point: How are you reading the data back out off the .mdb file. Maybe it's this process that's adding the spaces.
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @danilang,
For an MDB file, it saves the string field as the text content plus whitespace padding to fill in the field. Changing to a variable-length v_string allows the field to be saved in an MDB file without this padding issue.
Sam 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @danilang,
Not sure if it's a JET thing or something that's just inherent of the older MS Access MDB databases.
Sam 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry to revive this again, but in terms of efficiency, does V_String provide an edge over String? My data is too small to notice a difference, but just out of curiousity. Thank you.